Log In
Register
Home
July 11, 2017
Offline
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
Trusted Members
December 20, 2019
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
October 5, 2010
Offline
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
The following users say thank you to Philip Treacy for this useful post:
PurfleetVIP
Trusted Members
December 7, 2016
Offline
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)
Trusted Members
December 20, 2019
Offline
Mind blown
Such a simple, obvious concept yet i completely missed it
Excellent work Phil
October 5, 2010
Offline
Between us we came up with 5 solutions. Pretty good. 🙂
Trusted Members
Moderators
November 1, 2018
Offline
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.
The following users say thank you to Velouria for this useful post:
Purfleet
1 Guest(s)
