Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Recommended Posts

2 hours ago, Ned said:

 

I just copy/pasted this fine as text.  No idea why you're not able to do it.

You said you've created a file and then imported it... is the file layout the same every time?  If so, you can easily macro the import (I understand how annoying the import wizard is).

Gah, it's just my curse then. I cannot get anything but this when I paste:

1234
1.86E+15
2-Mar

 

Google Sheets: pastes fine, no formatting.  Excel Online: Same crapola as desktop program.

Edited by BroadwayG
Link to post
Share on other sites
  • Replies 921
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

I'm an Excel nerd to the hilt and enjoy helping others when it comes to Excel problems, so I figured I'd start a thread where y'all can ask questions on how to do certain things in Excel. I'm game fo

43 minutes ago, BroadwayG said:
2 hours ago, Ned said:

 

I just copy/pasted this fine as text.  No idea why you're not able to do it.

You said you've created a file and then imported it... is the file layout the same every time?  If so, you can easily macro the import (I understand how annoying the import wizard is).

Gah, it's just my curse then. I cannot get anything but this when I paste:

1234
1.86E+15
2-Mar

Google Sheets: pastes fine, no formatting.  Excel Online: Same crapola as desktop program.

I have the same problem. Just so you aren't alone!!! :shrug:

Link to post
Share on other sites

OK I googled it and got an answer for the Zip. One simple answer is to put the ' in front of the number, but that treats it as text, and Excel gets it's panties in a wad about that and puts the green Sehorn in the corner because you are 'treating a number as text.'

Here is what I found and it works.

You can correct this formatting within Excel using two different methods.

1a. Select the entire Zip Code column from within Excel.
1b. Select the menu option Format -> Cells...
1c. Select the Number tab, and click on the category called "Special".
1d. You can then select the type Zip Code or Zip Code + 4, and then click OK.

Link to post
Share on other sites
9 minutes ago, Ned said:

Any way you guys can post a sample sheet?  This is how it looks when I copied it from Chrome and then did Paste Special - Match Destination Formatting

https://s30.postimg.org/e5jhkjl1t/Broadway_G_Excel.png

Match Destination Formatting must be a newer option, I'm a couple versions behind.  I'm glad they finally figured out a solution for this after 20+ years.

Edited by BroadwayG
Link to post
Share on other sites
  • 2 weeks later...
27 minutes ago, cubd8 said:

I'm getting that division by error symbol when I use this formula below. How do I get rid of it?

=T7/(U7+V7+W7+X7+Y7+Z7)

Thank You!

this error? #DIV/0! if so, that means the formula in red sums to zero. assuming you're using Excel 2013 or newer, use this: =IFERROR(T7/(U7+V7+W7+X7+Y7+Z7),0). it will return 0 (or whatever u put in place of the zero) if the denominator is 0 or if the formula just craps out.

 

 

Link to post
Share on other sites
7 hours ago, Pigskin Fanatic said:

this error? #DIV/0! if so, that means the formula in red sums to zero. assuming you're using Excel 2013 or newer, use this: =IFERROR(T7/(U7+V7+W7+X7+Y7+Z7),0). it will return 0 (or whatever u put in place of the zero) if the denominator is 0 or if the formula just craps out.

Thanks, that took care of it!

 

 

Link to post
Share on other sites

One other question, This formula is working as intended.

=IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0)

However, is there a way to add a filter to this existing logic.

IF Column T is LT 3, then output a 0, else do the above calculation?

 

Link to post
Share on other sites
17 minutes ago, cubd8 said:

One other question, This formula is working as intended.

=IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0)

However, is there a way to add a filter to this existing logic.

IF Column T is LT 3, then output a 0, else do the above calculation?

 

That's another layer that forces you to nest a couple of IFs instead of using IFERROR.

=IF(ISERROR(T30/(U30+V30+W30+X30+Y30+Z30)),0,IF((T30/(U30+V30+W30+X30+Y30+Z30))<3,0,T30/(U30+V30+W30+X30+Y30+Z30)))

Link to post
Share on other sites
22 minutes ago, cubd8 said:

One other question, This formula is working as intended.

=IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0)

However, is there a way to add a filter to this existing logic.

IF Column T is LT 3, then output a 0, else do the above calculation?

=IF(T30<3,0,IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0))

  • Like 2
Link to post
Share on other sites
  • 1 month later...

for the wife

 

got an easy one;

column A has years starting with 2017 and going higher

column B is Y or N

 

formula needed from column A to feed into column B - if column A is 2017 column B should be no.  if column A is > 2017 column B should be Y.

 

thx

Link to post
Share on other sites
5 minutes ago, Chemical X said:

for the wife

 

got an easy one;

column A has years starting with 2017 and going higher

column B is Y or N

 

formula needed from column A to feed into column B - if column A is 2017 column B should be no.  if column A is > 2017 column B should be Y.

 

thx

What should column B be if column A < 2017?

Link to post
Share on other sites
8 minutes ago, Chemical X said:

the years start at 2017 there is nothing earlier.

I think it's pretty simple then. Let's say your column A has the heading "Date", and the data starts in row 2. 

 

In cell B2, type 

 

=if (year (A2)=2017,"n","y")

 

Copy and paste down the entire column. 

 

Profit. 

Edited by Psychopav
Link to post
Share on other sites
5 minutes ago, Psychopav said:

I think it's pretty simple then. Let's say your column A has the heading "Date", and the data starts in row 2. 

 

In cell B2, type 

 

=if (year (A2)=2017,"n","y")

 

Copy and paste down the entire column. 

 

Profit. 

almost, but everything says Y - 2018 2019, Etc all say Y

 

thought?  @Psychopav

Edited by Chemical X
Link to post
Share on other sites
6 minutes ago, Chemical X said:

almost, but everything says Y - 2018 2019, Etc all say Y

 

thought?  @Psychopav

Column A is just the years, not dates, right?  Then just do:

=if(A2=2017,"N","Y")

If that doesn't work, the other thing that comes to mind is that the years are stored as text instead of numbers or something, so you could try:

=if(A2="2017","N","Y")

Link to post
Share on other sites
5 minutes ago, Ignoratio Elenchi said:

Column A is just the years, not dates, right?  Then just do:

=if(A2=2017,"N","Y")

If that doesn't work, the other thing that comes to mind is that the years are stored as text instead of numbers or something, so you could try:

=if(A2="2017","N","Y")

so, this almost worked, but all came up as Y......so we changed the A2=2017 to A2>2017 and now it works.

 

thx for the push

Link to post
Share on other sites
38 minutes ago, Chemical X said:

for the wife

 

got an easy one;

column A has years starting with 2017 and going higher

column B is Y or N

 

formula needed from column A to feed into column B - if column A is 2017 column B should be no.  if column A is > 2017 column B should be Y.

 

thx

sonuva - I wanted the opposite formula.

 

jeebus

  • Like 1
Link to post
Share on other sites
  • 1 month later...

so, wife has a pivot table (she thinks).  trying to accomplish the following;

 

column A has about 4 actions listed that pertain to real estate, like renew, move, extend, etc.  column B has many dates in mm/dd/yyyy form.  lets says each column has 50 rows.

so, renew might have 15 dates, move might have 10 dates, etc.

she would like to filter the dates in column B from newest to oldest, but wants to filter for each action in column A.  so, renew will filter newest to oldest, move will then filter, etc.

 

any thoughts or prayers?

@Ned

Link to post
Share on other sites
31 minutes ago, Chemical X said:

so, wife has a pivot table (she thinks).  trying to accomplish the following;

 

column A has about 4 actions listed that pertain to real estate, like renew, move, extend, etc.  column B has many dates in mm/dd/yyyy form.  lets says each column has 50 rows.

so, renew might have 15 dates, move might have 10 dates, etc.

she would like to filter the dates in column B from newest to oldest, but wants to filter for each action in column A.  so, renew will filter newest to oldest, move will then filter, etc.

 

any thoughts or prayers?

@Ned

Not at a computer but she should be able to right-click in the values area (where dates are listed) and sort there.  It should apply the sort to each of her categories. 

Link to post
Share on other sites
1 minute ago, Ignoramus said:

Not at a computer but she should be able to right-click in the values area (where dates are listed) and sort there.  It should apply the sort to each of her categories. 

how would it know she has different cats to sort?  wouldn't it just sort the entire column without regard?

Edited by Chemical X
Link to post
Share on other sites
1 hour ago, Chemical X said:

so, wife has a pivot table (she thinks).  trying to accomplish the following;

 

column A has about 4 actions listed that pertain to real estate, like renew, move, extend, etc.  column B has many dates in mm/dd/yyyy form.  lets says each column has 50 rows.

so, renew might have 15 dates, move might have 10 dates, etc.

she would like to filter the dates in column B from newest to oldest, but wants to filter for each action in column A.  so, renew will filter newest to oldest, move will then filter, etc.

 

any thoughts or prayers?

@Ned

Sort the dates column first, then sort the action column.

ETA: no, your wife doesn't have a pivot table.

Edited by Ned
  • Like 2
Link to post
Share on other sites

 

5 minutes ago, Ned said:

Sort the dates column first, then sort the action column.

ETA: no, your wife doesn't have a pivot table.

Yeah, good point.  There wouldn't be dates in the values field, would there?

  • Like 1
Link to post
Share on other sites
47 minutes ago, Ned said:

Sort the dates column first, then sort the action column.

ETA: no, your wife doesn't have a pivot table.

what makes a pivot table.....I personally have no idea.  also, sent her the suggestion.

Link to post
Share on other sites

A                                                       B

Close                                            Dates

 

Open

 

Merge

 

so, a has only 3 values, but b has piles of dates.  she would like the dates in b that associate with a to sort by date.  so close would have 10 dates sorted, open may have 20 dates sorted, etc. 

Link to post
Share on other sites
1 minute ago, brun said:

Sort by Column A, then B.

Do you care that the Merge group will appear before the Open group?

I don't think there is a specific order required......getting the dates to sort in their group is most important.

Link to post
Share on other sites
7 minutes ago, Chemical X said:

exactly......however, her sheet or pivot or whatever only has the action name listed once.

I am trying to get a copy to share, but you are on the right path.

OK, now I understand why the sorting by B and then A didn't work.  Having those blanks screws things up (its not a good data practice).

Fill in the blanks with the appropriate actions using the auto fill feature.  Then do the sort I posted earlier.  If she insists on not having the repeated actions, she can delete them after the sorting is done.

Link to post
Share on other sites
2 minutes ago, Ned said:

OK, now I understand why the sorting by B and then A didn't work.  Having those blanks screws things up (its not a good data practice).

Fill in the blanks with the appropriate actions using the auto fill feature.  Then do the sort I posted earlier.  If she insists on not having the repeated actions, she can delete them after the sorting is done.

I sent you a PM for a look.

Link to post
Share on other sites
25 minutes ago, Chemical X said:

here is an easier question.  Excel work book has multiple tabs.  is it possible to email only 1 tab of a multi tabbed work book?

@Ned

right-click the tab and choose send, I believe.

Or right click and say copy to new book and save that one sheet as a new book.

Link to post
Share on other sites
9 minutes ago, Walking Boot said:

Right click the tab,  "move or copy"

in the popup window, select "new workbook" in the drop-down menu at the top.

Then, make sure "Copy" is checked.

Then, click OK

this!

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    No registered users viewing this page.


×
×
  • Create New...