July 11, 2017
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
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
October 5, 2010
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
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
1 Guest(s)