Nested IF functions

[FMiller017]

Hi there

I am trying to determine where various actual gross margin percentages would fall within a benchmark range of gross margin percentages (i.e. below minimum, minimum to 25th percentile, 25th percentile to median, median to 75th percentile, 75th percentile to maximum and above maximum).

Example:
My actual gross margin is 16% and my benchmark range of gross margins is as follows:
Minimum - 13%
25th percentile - 37%
Median - 55%
75th percentile - 66%
Maximum - 119%

I want the formula, which compares my actual 16% to the above benchmark values, to return a value of "Between the minimum and 25th percentile".

I am battling to get Excel to accept my below nested IF function formula. I have played around with brackets but nothing seems to be working so feeling pretty stupid and even ChatGPT seems to have run out of suggestions! =( Any help would be much appreciated.

=IF(AH106 < AH82 "Below Minimum", IF(AH106 < AH83, "Between Minimum and 25th Percentile", IF(AH106 < AH84, "Between 25th Percentile and Median", IF(AH106 < AH85, "Between Median and 75th Percentile", IF(AH106 < AH86, "Between 75th Percentile and Maximum", "Above Maximum")))))

 

[Cubist]

-Editing-

 

[Cubist]

Try:

Book1
	A	B	C
1	Minimum	13%
2	Between Minimum and 25th Percentile	14%
3	25th Percentile	37%
4	Between 25th Percentile and Median	38%
5	Median	55%
6	Between 25th Percentile and Median	56%
7	75th Percentile	66%
8	Between Median and 75th Percentile	67%
9	Maximum	119%
10	Above Maximum	120%
11
12		14.0%	Between Minimum and 25th Percentile
Sheet5

Cell Formulas
Range	Formula
C12	C12	=XLOOKUP(B12,B1:B10,A1:A10,,-1)

 

[shift-del]

The formula works if you put a comma after AH82.

 

[FMiller017]

Thank you All!

The formula worked after I replaced all commas with semi colons.

 

[shift-del]

If you don't need a text output you could try and use PERCENTRANK.INC/PERCENTRANK.EXC to get the rank.