Log In
Register
Home
Active Member
December 11, 2021
Offline
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))))) |
See Answer
VIP
Trusted Members
December 7, 2016
Offline
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
Active Member
December 11, 2021
Offline
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)))))
VIP
Trusted Members
December 7, 2016
Offline
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
Answers Post
Active Member
December 11, 2021
Offline
Thanks Anders, much appreciated.
Trusted Members
Moderators
November 1, 2018
Offline
To get the last number, you could also use:
=LOOKUP(1E+100,C3:E3)
The following users say thank you to Velouria for this useful post:
Anders Sehlstedt
1 Guest(s)
