Looking for IF Statement formula

[DarrenF]

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.

 

[Steph77]

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]

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

 

[Steph77]

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

 

[DarrenF]

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

 

[taurean]

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]

=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".

=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]

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

=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