Looking for IF Statement formula

DarrenF

Board Regular
Joined
Jun 9, 2014
Messages
77
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.
 

Some videos you may like

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
Joined
Sep 18, 2014
Messages
119
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)
 

Steph77

Board Regular
Joined
Sep 18, 2014
Messages
119
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
Joined
Jun 9, 2014
Messages
77

ADVERTISEMENT

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

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Apr 24, 2013
Messages
67
=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
Joined
Jul 10, 2009
Messages
2,757
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top