Hi
I'm trying to find a formula that gives me the following tests & values if true.
I have tried IFS and nested IF with AND, but just do not have the skill level to accomplish required result
Need a formula that fist looks at Occupancy % (cell M5) and then:
If below 40% calculates M6*A3
If between 40% & 49% calculates M4*B3
If between 50% & 59% calculates M4*C3
If greater than 60% calculates M4*D3
Thanks
Paul
It all depends if you have office 365 or an older version of excel.
On 2016 or older you would need nested IF(s) with and(s)
=IF(M5<0.4,M6*A3,IF(AND(M5>=0.4,M5<=0.49),M4*B3,IF(AND(M5>=0.4,M5<=0.49),M4*B3,IF(AND(M5>=0.5,M5<=0.59),M4*C3,IF(M5>=0.6,M4*D3)))))
You could also (possibly) use a vlookup to get the number with an approximate match and then do a simpler if
=VLOOKUP(M5,$A$7:$B$10,2,TRUE)
See attached
Purfleet
Hello,
Using IFS function is almost as nested IF’s.
=IFS(M5<0.4,M6*A3,AND(M5>=0.4,M5<=0.49),M4*B3,AND(M5>=0.5,M5<=0.59),M4*C3,M5>=0.6,M4*D3)
Br,
Anders
Hi Paul,
Because the IF statement will only evaluate until it finds a True condition you can use this, leaving out the AND's, by making your first test for the largest value and then working down
=IF($M$5>0.6,$M$4*$D$3,IF($M$5>0.5,$M$4*$C$3,IF($M$5>0.4,$M$4*$B$3,$M$4*$A$3)))
Regards
Phil
Thanks for the tip Phil! I am now just wondering how on earth I have missed such simple fact during the years I have used Excel. There is always something to learn. The same implies also to IFS, so a revised formula would be
=IFS(M5>=0.6,M4*D3,M5>=0.5,M4*C3,M5>=0.4,M4*B3,M5<0.4,M6*A3)
Mind blown
Such a simple, obvious concept yet i completely missed it
Excellent work Phil
Between us we came up with 5 solutions. Pretty good. 🙂
Just a small note that nested IF is more efficient since IFS evaluates everything passed to it, no matter which order you do it in.
Many thanks for the help, I have learnt so much from the replies
A big shout out to Philip, Purfleet, Anders & Velouria
