# Looking for IF Statement formula

#### DarrenF

##### Board Regular
I have a complex question here:

I have a drop down that will produce 2 different types of information.

Legacy Scorecard
X-1 Scorecard

I have the following IF statement

=IF(\$G\$1="Legacy Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE",IF(E5<=Metrics!C4,"E"))))))),IF(\$G\$1,"X-1 Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE",IF(E5<=Metrics!J4,"E")))))))))

I know its very lengthy and probably quite messy. I'll try to explain what I'm trying to do.

I have 2 different target goals depending on if your Legacy or X-1 scoring. I'm also trying to decide weither your falling in the catagory of U, NI, AE, HE or E.

Let me know if I need to provide further information.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Steph77

##### Board Regular
You have a problem with the last IF statement within each section:

IF(E5<=Metrics!C4,"E") and IF(E5<=Metrics!J4,"E")

The IF needs a value if true and a value if false, these two bits of the formula are only giving a value if true.

Try replacing the last 2 IFs that lead to them with something like:

IF(E5>Metrics!C4,"HE","E") and IF(E5>Metrics!J4,"HE","E")

(Remember to take one of the close brackets out at the end of the section too or the number of open and close brackets won't match)

#### DarrenF

##### Board Regular
Thank you. When I go to X-1 Scorecard info I end up getting #VALUE!

#### Steph77

##### Board Regular
Normally a #VALUE error means there's something that looks like a number to you but it's actually being treated by Excel as a text string. Check any of the cells that have numbers in them are set to a number format as the first way to try and fix that.

I also think the same problem as I mentioned before is affecting the second half of your formula

IF(\$G\$1,"X-1 Scorecard",**STUFF**)

If you can only pick between two values then take out this bit of the IF chain and skip straight to the other stuff, as long as if it isn't Legacy it will ALWAYS be X-1 then that might solve it too...

Edit so:

=IF(\$G\$1="Legacy Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE","E")))))),IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE","E")))))))

Last edited:

#### DarrenF

##### Board Regular

I appologize, I'm not understanding the 2nd solution.

#### taurean

##### Well-known Member
If G1 is always going to be either of the two then you can try like below:
=IF(\$G\$1="Legacy Scorecard",IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!C4:F4)+1,{"HE","AE","NI","U"}),"E")),IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!J4:M4)+1,{"HE","AE","NI","U"}),"E")))

#### xrichardx

##### Board Regular
=IF(\$G\$1="Legacy Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE",IF(E5<=Metrics!C4,"E"))))))),IF(\$G\$1,"X-1 Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE",IF(E5<=Metrics!J4,"E")))))))))

First thing, you've got a typing mistake in the condition "\$G\$1,"X-1 Scorecard"", it shall be an "=" equal sign, "\$G\$1="X-1 Scorecard"".

I also re-arranged your code with the logic of "IF, ELSEIF, ELSEIF, ELSE, ENDIF", against your logic of "IF, ELSE, ELSEIF, ELSEIF, ENDIF".

Code:
``=IF(\$G\$1="Legacy Scorecard",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE",IF(E5<=Metrics!C4,"E","")))))),IF(\$G\$1="X-1 Scorecard",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE",IF(E5<=Metrics!J4,"E","")))))),""))``

But, the above code will still have a conflict depending on your values in "IF(E5=0,"E" and IF(E5<=Metrics!C4,"E"". Try to minimize it.

Cheers
Rich

#### ukmikeb

##### Well-known Member
If G1 is always going to be either of the two then you can try like below:
=IF(\$G\$1="Legacy Scorecard",IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!C4:F4)+1,{"HE","AE","NI","U"}),"E")),IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!J4:M4)+1,{"HE","AE","NI","U"}),"E")))

Or a slimmed down version :-
Code:
``=IF(E5="","",IFERROR(LOOKUP(E5,INDEX((Metrics!C4:F4,Metrics!J4:M4),,,IF(\$G\$1="Legacy Scorecard",1,2))+1,{"HE","AE","NI","U"}),"E"))``

hth

Replies
9
Views
55
Replies
1
Views
54
Replies
2
Views
41
Replies
0
Views
282
Replies
0
Views
33