Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOOONG if statement out of room, need help to shorten
This has had me stumped for a couple of days, and I'm absolutely convinced
it's a simple fix. LOL. anyone have any suggestions on how to shorten this if statement because excel keeps throwing out error windows at me saying there's a problem with my formula =IF($B8="","",IF(AND($G$4="Cost",$E8<"",$F8=""),$B 8*CT$4-FT8,IF(AND($G$4="Cost",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(AND($G$4="Cost",$C8<""),$B8*CT$4-H8,IF(CU9="X","STD",IF(AND($G$4="Sales",$E8<"",$F8 =""),$B8*CT$4-FT8,IF(AND($G$4="Sales",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(and($G$4="Sales",$C8<""),$B8*CT$4-H8,"n/a")))))))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOOONG if statement out of room, need help to shorten
In Excel 2007 I pasted your formula and did not get any error...
I believe Excel 2003 has a limit of & nested IFs and you have 8!! You can evaluate part of the condition to another cell and based on that completed your test... It will easier for us if you tell us what you are trying to achieve... You probably mean =$E8="" instead of =$E8<"" "Btate0121" wrote: This has had me stumped for a couple of days, and I'm absolutely convinced it's a simple fix. LOL. anyone have any suggestions on how to shorten this if statement because excel keeps throwing out error windows at me saying there's a problem with my formula =IF($B8="","",IF(AND($G$4="Cost",$E8<"",$F8=""),$B 8*CT$4-FT8,IF(AND($G$4="Cost",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(AND($G$4="Cost",$C8<""),$B8*CT$4-H8,IF(CU9="X","STD",IF(AND($G$4="Sales",$E8<"",$F8 =""),$B8*CT$4-FT8,IF(AND($G$4="Sales",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(and($G$4="Sales",$C8<""),$B8*CT$4-H8,"n/a")))))))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOOONG if statement out of room, need help to shorten
I didn't do extensive testing on it, but I think this formula should work for
your needs. FYI, you were getting the error due to too many nested IF statements. IF($B8="","",AND(OR($G$4="Sales",$G$4="Cost"),OR(A ND($E8<"",$F8=""),AND($E8="",$F8="x"),$C8<"")),$ B8*CT$4-IF($E8<"",IF($F8="",FT8,GA8),H8),IF(CU9="X","STD" ,"n/a")) The one thing I wasn't certain about is CU9="X" then show "STD". According to your formula, you first check to see if $G$4="Cost", then check CU9 for X. So, by your formula, even if G4="Sales", E8<"", and F8="", your formula would still show the STD vice calculating B8*CT4-FT8. That being said, if that is what you do wish to happen, you could modify my formula 'slightly' as follows: IF($B8="","",AND(OR($G$4="Sales",($G$4="Cost")*(CU 9<"X")),OR(AND($E8<"",$F8=""),AND($E8="",$F8="x" ),$C8<"")),$B8*CT$4-IF($E8<"",IF($F8="",FT8,GA8),H8),IF(CU9="X","STD" ,"n/a")) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Btate0121" wrote: This has had me stumped for a couple of days, and I'm absolutely convinced it's a simple fix. LOL. anyone have any suggestions on how to shorten this if statement because excel keeps throwing out error windows at me saying there's a problem with my formula =IF($B8="","",IF(AND($G$4="Cost",$E8<"",$F8=""),$B 8*CT$4-FT8,IF(AND($G$4="Cost",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(AND($G$4="Cost",$C8<""),$B8*CT$4-H8,IF(CU9="X","STD",IF(AND($G$4="Sales",$E8<"",$F8 =""),$B8*CT$4-FT8,IF(AND($G$4="Sales",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(and($G$4="Sales",$C8<""),$B8*CT$4-H8,"n/a")))))))) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOOONG if statement out of room, need help to shorten
In Excel 2003 and earlier there is a limit to the number of nested
functions you can have (of 8). This applied to all nested functions, not just IF, and you have several ANDs as well as IFs. You might get some joy by joining G4, E8 and F8 together in a helper column, then you might be able to use VLOOKUP for some of the formula. Hope this helps. Pete On Nov 5, 7:44*pm, Btate0121 wrote: This has had me stumped for a couple of days, and I'm absolutely convinced it's a simple fix. LOL. *anyone have any suggestions on how to shorten this if statement because excel keeps throwing out error windows at me saying there's a problem with my formula =IF($B8="","",IF(AND($G$4="Cost",$E8<"",$F8=""),$B 8*CT$4-FT8,IF(AND($G$4="C*ost",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(AND($G$4="Cost",$C8<""),$B8*CT$4-H8,IF*(CU9="X","STD",IF(AND($G$4="Sales",$E8<"",$F 8=""),$B8*CT$4-FT8,IF(AND($G$4=*"Sales",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(and($G$4="Sales",$C8<""),$B8*CT$4-H*8,"n/a")))))))) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOOONG if statement out of room, need help to shorten
See Sheeloo's comment regarding the <"", I thought you were wanting it so
that the criteria was a match so long as the cell wasn't blank, but if the criteria is to be a match if the cell IS blank, then you should change my <"" to ="" -- ** John C ** "John C" wrote: I didn't do extensive testing on it, but I think this formula should work for your needs. FYI, you were getting the error due to too many nested IF statements. IF($B8="","",AND(OR($G$4="Sales",$G$4="Cost"),OR(A ND($E8<"",$F8=""),AND($E8="",$F8="x"),$C8<"")),$ B8*CT$4-IF($E8<"",IF($F8="",FT8,GA8),H8),IF(CU9="X","STD" ,"n/a")) The one thing I wasn't certain about is CU9="X" then show "STD". According to your formula, you first check to see if $G$4="Cost", then check CU9 for X. So, by your formula, even if G4="Sales", E8<"", and F8="", your formula would still show the STD vice calculating B8*CT4-FT8. That being said, if that is what you do wish to happen, you could modify my formula 'slightly' as follows: IF($B8="","",AND(OR($G$4="Sales",($G$4="Cost")*(CU 9<"X")),OR(AND($E8<"",$F8=""),AND($E8="",$F8="x" ),$C8<"")),$B8*CT$4-IF($E8<"",IF($F8="",FT8,GA8),H8),IF(CU9="X","STD" ,"n/a")) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Btate0121" wrote: This has had me stumped for a couple of days, and I'm absolutely convinced it's a simple fix. LOL. anyone have any suggestions on how to shorten this if statement because excel keeps throwing out error windows at me saying there's a problem with my formula =IF($B8="","",IF(AND($G$4="Cost",$E8<"",$F8=""),$B 8*CT$4-FT8,IF(AND($G$4="Cost",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(AND($G$4="Cost",$C8<""),$B8*CT$4-H8,IF(CU9="X","STD",IF(AND($G$4="Sales",$E8<"",$F8 =""),$B8*CT$4-FT8,IF(AND($G$4="Sales",$E8<"",$F8="x"),$B8*CT$4-GA8,IF(and($G$4="Sales",$C8<""),$B8*CT$4-H8,"n/a")))))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not enough room to enter dates | Excel Discussion (Misc queries) | |||
Room Placement Grid | Excel Discussion (Misc queries) | |||
Room Placement Grid | Charts and Charting in Excel | |||
room schedule | Excel Worksheet Functions | |||
Chat Room | New Users to Excel |