Looking for IF Statement formula

DarrenF

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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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
90

ADVERTISEMENT

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

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,189
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top