Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

15 minutes ago, Donkey Derp said:

=IF(MONTH(A1)<10,YEAR(A1),YEAR(A1)+1)

 

?

 @Donkey Derp , thanks, but seems like it may be missing something, no?

let's say column A has 8/31/2016, 8/31/2018, and 8/31/2021....................need to have each of these separated into a separate pot for its' respective FYE.

Share this post


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

 @Donkey Derp , thanks, but seems like it may be missing something, no?

let's say column A has 8/31/2016, 8/31/2018, and 8/31/2021....................need to have each of these separated into a separate pot for its' respective FYE.

What do you think is missing?

9/1/15 will return 2015

10/1/15 will return 2016

9/1/16 will return 2016

10/1/16 will return 2017

  • Like 1

Share this post


Link to post
Share on other sites
18 minutes ago, Donkey Derp said:

What do you think is missing?

9/1/15 will return 2015

10/1/15 will return 2016

9/1/16 will return 2016

10/1/16 will return 2017

wife had column formatted as date, not numerical............

 

worked perfectly, I owe you a coke.  (drinkable version)

  • Like 2

Share this post


Link to post
Share on other sites

Are you trying to add something from column A for each fiscal year?  You can use =SUMIF based on those formulas in column B.  If you don't want an additional column of formulas in column B, a  =SUMIFS (extra S) formula would most likely work.  The latter would take me a second to think about and write.

Clarify exactly what you need if the formula I typed isn't working as you expect.

Edited by Donkey Derp
can ignore
  • Like 2

Share this post


Link to post
Share on other sites

I like how Chem X keeps blaming his wife. 

Edited by Ned
  • Like 2

Share this post


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

I like how Chem X keeps blaming his wife. 

"ask your friends" she says.

they aren't my friends, I say.

 

  • Like 2

Share this post


Link to post
Share on other sites

asking for wife......

 

she has a vlookup that ties back to a column in a spreadsheet.  if she moves the column, the vlookup goes blank.

anyway to not have the vlookup go blank regardless of where the column lies?

 

thx

Share this post


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

asking for wife......

 

she has a vlookup that ties back to a column in a spreadsheet.  if she moves the column, the vlookup goes blank.

anyway to not have the vlookup go blank regardless of where the column lies?

 

thx

I'm guessing your VLOOKUP hs the cell references locked with $. Take the $ out and it will adjust as you move your lookup column around. 

  • Like 1

Share this post


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

I'm guessing your VLOOKUP hs the cell references locked with $. Take the $ out and it will adjust as you move your lookup column around. 

gb you.....answer is correct!

Share this post


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

asking for wife......

 

she has a vlookup that ties back to a column in a spreadsheet.  if she moves the column, the vlookup goes blank.

anyway to not have the vlookup go blank regardless of where the column lies?

 

thx

any chance your wife could just learn to use excel?

  • Like 1

Share this post


Link to post
Share on other sites
Just now, Peyton Marino said:

any chance your wife could just learn to use excel?

gf knows how to.......

Share this post


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

gf knows how to.......

Pics as payment, por favor.

Share this post


Link to post
Share on other sites

Excel 2010 automatically formats new sheets as tables (at least when expanded from pivot tables).

You can manually change it from a "table" to "a range", but anyone know of a way to make that the default setting?

Edited by Long Ball Larry

Share this post


Link to post
Share on other sites
On 12/11/2016 at 11:55 AM, Long Ball Larry said:

Excel 2010 automatically formats new sheets as tables (at least when expanded from pivot tables).

You can manually change it from a "table" to "a range", but anyone know of a way to make that the default setting?

I don't know of a setting that controls this.

Share this post


Link to post
Share on other sites

Anyone know how to make excel not auto format long numbers into scientific notation during a paste?  Or not format "3-2" as a date.  Or keep leading zeroes on a zip code?

Saving it into a text file then importing, having to manually change every column to text is incredibly tedious.

Edited by BroadwayG

Share this post


Link to post
Share on other sites

 

6 minutes ago, Walking Boot said:

I prefer index-match to vlookup

:goodposting: 

i never use VLOOKUP. It sucks. I think there's a post in the first couple of pages explaining the difference for folks interested. 

Share this post


Link to post
Share on other sites
3 minutes ago, BroadwayG said:

Anyone know how to make excel not auto format long numbers into scientific notation during a paste?  Or not format "3-2" as a date.  Or keep leading zeroes on a zip code?

Saving it into a text file then importing, having to manually change every column to text is incredibly tedious.

For the zip code issue - either format the column to text before importing or make a custom format of '00000'. 

I haven't had to deal with numbers that large, so not sure about the long numbers issue. 

Share this post


Link to post
Share on other sites

I'm copying/pasting from elsewhere, so preformatting isn't an option.  Thankfully they put the scientific notation limit at 11 so that unformatted phone numbers can be pasted ok.  However, a 12-digit UPC will get scientific notationed on pasting, unless of course it comes with a leading zero, then your data is up the creek.

Edited by BroadwayG

Share this post


Link to post
Share on other sites
4 minutes ago, BroadwayG said:

I'm copying/pasting from elsewhere, so preformatting isn't an option.  Thankfully they put the scientific notation limit at 11 so that unformatted phone numbers can be pasted ok.  However, a 12-digit UPC will get scientific notationed on pasting, unless of course it comes with a leading zero, then your data is up the creek.

Paste Special - Values 

Share this post


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

Paste Special - Values 

I'm copy/pasting from outside of Excel so I get the option of pasting as text or Unicode Text when I try paste special.  Neither one works to actually paste as text, it formats everything as a number.

Share this post


Link to post
Share on other sites

Here are some values I'd like to copy and paste into Excel as text.  You'd think it wouldn't be a big deal, but this has terrorized me since the 1990s.

 

01234
1856324585855521
3-2

Edited by BroadwayG

Share this post


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

Here are some values I'd like to copy and paste into Excel as text.  You'd think it wouldn't be a big deal, but this has terrorized me since the 1990s.

 

01234
1856324585855521
3-2

Why can't you just format the cells as Text and then paste them?

Share this post


Link to post
Share on other sites

 

12 hours ago, BroadwayG said:

Here are some values I'd like to copy and paste into Excel as text.  You'd think it wouldn't be a big deal, but this has terrorized me since the 1990s.

 

01234
1856324585855521
3-2

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
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:

Share this post


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.

Share this post


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

Share this post


Link to post
Share on other sites

For the Long Numbers, I went back and switched the format to 'General' and the numbers switched from Scientific to plain old ordinary.

For the numbers with dashes, before importing change the column format to 'text' and you'll be set.

 

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
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.

 

 

Share this post


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!

 

 

Share this post


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?

 

Share this post


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)))

Share this post


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

Share this post


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

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

:doh: I misread his post.  

Share this post


Link to post
Share on other sites

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

Share this post


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?

Share this post


Link to post
Share on other sites
4 minutes ago, Psychopav said:

What should column B be if column A < 2017?

the years start at 2017 there is nothing earlier.  @Psychopav

Edited by Chemical X

Share this post


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

Share this post


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

Share this post


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")

Share this post


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

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

 

thought?  @Psychopav

I'm confused... Isn't that what you want?  Everything which isn't 2017 should say "y"?

Share this post


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

Share this post


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

I'm confused... Isn't that what you want?  Everything which isn't 2017 should say "y"?

actually, we wanted 2017 to say Y and all others to be no.

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


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.