Question

8. In the "Order Quality Nested" column, create a Nested IF function to rank the customer sales by the average sales per order. If the average sales amount per order is less than $20, rank as Low, $20 to less than $40 "Average," $40 to less than $100 as "High," and over $100 as "Prime."

I am having difficulty writing the Nested function. The formula  I used was =IF(B2<20,"LOW",IF(B2<40,"AVERAGE",IF(B2<100,"HIGH","PRIME"))). All I got was Prime for everything. need help with a nested function in excel.

Answer 1

yes you will get the all as Prime .. Because what you are doing is taking base as number of order and not the average sales price per order.

You have to use below formula -

For 1st row =IF(A1/B1<20,"LOW",IF(A1/B1<40,"AVERAGE",IF(A1/B1<100,"HIGH","PRIME")))

for 2nd row =IF(A2/B2<20,"LOW",IF(A2/B2<40,"AVERAGE",IF(A2/B2<100,"HIGH","PRIME")))

Thn you can drag the formula in the excel .