Sep 11 2020 05:21 AM
I am trying to create a formula using IFS to check three possible outcomes:
value<5000 - return RM
5000<value<30000 - return VPO
value<30000 - return IPC
I have managed to get the true value return for the first and the last logical test, while the middle where it checks for value above and below returns N/A
Tried adding the use of AND in the middle but still returns N/A
Anyone?
Sep 11 2020 05:44 AM
Let's say the value is in A2. The formula (in another cell) could be
=IFS(A2<5000,"RM",A2<30000,"VPO",A2>=30000,"IPC")
or
=IF(A2<5000,"RM",IF(A2<30000,"VPO","IPC"))
or
=LOOKUP(A2,{0,5000,30000},{"RM","VPO","IPC"})
Sep 11 2020 05:46 AM - edited Sep 11 2020 05:48 AM
This is a "Nested If" problem. First, let us solve that:
IF (value<5000, "RM", IF (value<30000, "VPO", "IPC"))
Nested IF statements are notoriously hard to maintain and are prone to error.
An easier to maintain and less error prone approach is to leverage Excel's tables like the one below. I called this "tblCodes".
tblCodes is fully dynamic so if we add codes or change values, we can make the changes to the table and leave our formula unchanged. The formula is then:
=VLOOKUP(<value>,tblCodes,2,TRUE)
Where <value> is the cell address or name holding 0, 5000, or 30000. See attached
Sep 11 2020 05:56 AM
Thanks for the tip.
After playing around a little longer I found a solution;
=IFS(S22<=5000,"RM",AND(S22>5000,S22<=30000),"VPO",30000<S22,"IPC")