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

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

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

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

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