Sep 10 2020 01:56 PM
I am needing a formula which essentially says:
If a cell value is >=A and <=B then "p%", if not and cell value is >=C and <=D then "q%", if not and cell value is >=E and <=F the "r%", etc...
Any help would be appreciated.
Sep 10 2020 02:43 PM
How are the ranges related. Do they overlap; are there gaps?
Microsoft 365 only, but
= FILTER( percent, (value>=lower)*(value<=upper), "Not in range" )
would take care of most eventualities.
Sep 10 2020 02:49 PM
@Peter Bartholomew Thanks for reaching out. The ranges butt up against each other. For example:
If cell is greater than or equal to 10.01% but less than or equal 20.00% then "25%, if not and cell is greater than or equal to 20.01% but less than or equal to 30.00% then "35%, and so on and so forth.
I look forward to your response!
Sep 10 2020 03:56 PM
Since there are no overlaps you could use a lookup. Options with XLOOKUP are
= XLOOKUP(value, lower, percent, ,-1)
= XLOOKUP(value, upper, percent, ,1)
but the older LOOKUP requires the lower bound
= LOOKUP( value, lower, percent )