Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Ned

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 comment
Share on other sites

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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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 comment
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...