Hi all, having an issue with getting the result I want based on several criteria. I'm sure one of your learned selves will be able to solve it quickly however I've been scratching my head for some time to make it work. I'm only using IF statements, but wonder if I need to use AND and/or OR statements instead. Had a try with those as well but can't get it to work properly.
Below is what's in the spreadsheet attached. As per the examples, it works fine when all 3 criteria are entered, however, doesn't provide the result when it's missing either criteria 1 or criteria 2.
Thanks in advance.
| Criteria 1 | Criteria 2 | Criteria 3 | I want to use this figure | Current Formula Result | Formula Text | |
| Outcome 1 | 1 | 2 | 3 | 3 | 3 | =IF(C13<=0,0,(IF(D13<=0,C13,(IF(E13<=0,D13,E13))))) |
| Outcome 2 | 1 | 3 | 3 | 1 | =IF(C14<=0,0,(IF(D14<=0,C14,(IF(E14<=0,D14,E14))))) | |
| Outcome 3 | 2 | 3 | 3 | 0 | =IF(C15<=0,0,(IF(D15<=0,C15,(IF(E15<=0,D15,E15))))) | |
| Outcome 4 | 2 | 2 | 0 | =IF(C16<=0,0,(IF(D16<=0,C16,(IF(E16<=0,D16,E16))))) | ||
| Outcome 5 | 3 | 3 | 0 | =IF(C17<=0,0,(IF(D17<=0,C17,(IF(E17<=0,D17,E17))))) |
Hello Rob,
It seems to me that you want the highest number for each row, so for that you only need the MAX function.
The reason to why you get the results you get is because Excel returns a zero to blank cells when you check what number value it has. You can for example check this with =ISNUMBER(A1), assuming that A1 is blank. The result will be FALSE. But if you write =A1=0 you will get TRUE.
But in this scenario I don’t see why you need to use nested IFS.
Br,
Anders
Thanks Anders. Sorry, the example didn't make it clear. It's not a max result I'm after, it's whichever is the later result I want to display.
For example, criteria 1 could be 100, 2 could be 65 and I'd want 65 to be the result.
Similarly, if criteria 1 is 40, 2 is 50 and 3 is 45 then I'd want 45 to be the result. If, in this 40/50/45 example, that it was now 0/50/45 then the formula would incorrectly provide a 0 result.
I changed the formula to the below using your ISNUMBER example and it now seems to provide me with the correct result, I'm just not sure it's strictly 100% correct. Any suggestions to tidy it up?
=IF(ISNUMBER(C13=0),0,(IF(ISNUMBER(D13=0),C13,(IF(E13<=0,D13,E13)))))
Hello Rob,
Your formula will always evaluate to FALSE in regards to the ISNUMBER checks, simply because the equal sign makes it to fail. You have however made it to work in a cumbersome way, but it does give correct values until you only have a number in column C and leave columns D and E empty.
Below is an alternative nested IF formula which seems to work.
=IF(ISNUMBER(E3)=TRUE,E3,
IF(ISNUMBER(D3)=TRUE,D3,
IF(ISNUMBER(C3)=TRUE,C3,0)))
Added a modified version of your sample file showing the alternative formula and yours, so you can see what works.
Br,
Anders
Thanks Anders, much appreciated.
To get the last number, you could also use:
=LOOKUP(1E+100,C3:E3)
