# Nested IF based off an AVERAGE (range)

#### VKM

##### New Member
Hi folks.

I don't know if it's because I've just come back to work after the Christmas break, but I just can't seem to work this out. I'm trying to create a nested IF formula that is based off an average of range of five cells. I can calculate the AVERAGE in a separate column and the successfully create my IF statement, but I am trying to combine them into one formula. I have made several attempts at editing this and searched various forums but without success of finding a solution that works.

Here is my nested IF, which does what I need it to:
=IF(AND(B9=5),"ONE",IF(AND(B9>=4.2, B9<5),"TWO", IF(AND(B9>=3.8,B9<4.2),"THREE", IF(AND(B9>=3,B9<3.8),"FOUR",IF(AND(B9<=2.8),"FIVE",)))))

Here is my attempt and calculating the AVERAGE within the nested IF:
'=IF(AND((AVERAGE(B2:F2)=5,"ONE",
IF(AND((AVERAGE(B2:F2)>=4.2,(AVERAGE(B2:F2)< 5),"TWO",
IF(AND((AVERAGE(B2:F2)>=3.8,(AVERAGE(B2:F2)<4.2),"THREE",
IF(AND((AVERAGE(B2:F2)>=3,(AVERAGE(B2:F2)<3.8),"FOUR",
IF(AND((AVERAGE(B2:F2)<=2.8),"FIVE",)))))

Here is a sample of the data:

I want to calculate an average of the five items and assign a new text label based on the range the calculated average falls within:
 IDENTIFIER ITEM_1 ITEM_2 ITEM_3 ITEM_4 ITEM_5 126689​ 5​ 5​ 5​ 5​ 5​ 126690​ 5​ 5​ 5​ 5​ 5​ 126691​ 5​ 4​ 4​ 4​ 5​ 126692​ 2​ 2​ 3​ 2​ 4​ 126693​ 4​ 3​ 4​ 3​ 4​ 126694​ 4​ 4​ 4​ 4​ 4​ 126695​ 5​ 2​ 3​ 2​ 4​ 126696​ 4​ 4​ 4​ 4​ 4​ 126697​ 4​ 3​ 4​ 3​ 4​

Can anyone help?

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=IF(AVERAGE(B2:F2)>=5,"ONE",IF(AVERAGE(B2:F2)>=4.2,"TWO",IF(AVERAGE(B2:F2)>=3.8,"THREE",IF(AVERAGE(B2:F2)>=3,"FOUR",IF(AVERAGE(B2:F2)<=2.8,"FIVE","?")))))``

VKM

#### joeu2004

##### Well-known Member
First, it would simplify things if you reversed the order. For example, in your original:

=IF(B9<=2.8, "FIVE", IF(B9<3.8, "FOUR", IF(B9<4.2, "THREE", IF(B9<5, "TWO", "ONE"))))

Likewise, you might replace B9 with AVERAGE(B2:F2).

Second, you can simplify this by using LOOKUP (but see the caveat below):

=LOOKUP(AVERAGE(B2:F2), {0, 2.8, 3.8, 4.2, 5}, {"FIVE", "FOUR", "THREE", "TWO", "ONE"})

Replace 0 with -1E300 is the average might be negative! Also, you might wrap IFERROR around the LOOKUP expression, in case there no numbers in the range B2:F2 and AVERAGE returns an Excel error.

Caveat: Technically, LOOKUP uses >=0, >=2.8, >=3.8, >=4.2, >=5. So, for example, an average of exactly 2.8 would return "FOUR", not "FIVE" as in your original expression.

If that is critical, there are a number of alternative remedies. The best one depends on details that you have not provided.

Is ROUND(AVERAGE(B2:F2),1) okay? Or do you need to calculate the average with more than 1 decimal place of precision?

VKM

#### VKM

##### New Member
Hi & welcome to MrExcel.
Excel Formula:
``=IF(AVERAGE(B2:F2)>=5,"ONE",IF(AVERAGE(B2:F2)>=4.2,"TWO",IF(AVERAGE(B2:F2)>=3.8,"THREE",IF(AVERAGE(B2:F2)>=3,"FOUR",IF(AVERAGE(B2:F2)<=2.8,"FIVE","?")))))``
Hi @Fluff, that worked a treat. I can't believe how simple it was. I clearly got into a state of confusion and overcomplicated matters!
Thanks so much for your help!

#### VKM

##### New Member
First, it would simplify things if you reversed the order. For example, in your original:

=IF(B9<=2.8, "FIVE", IF(B9<3.8, "FOUR", IF(B9<4.2, "THREE", IF(B9<5, "TWO", "ONE"))))

Likewise, you might replace B9 with AVERAGE(B2:F2).

Second, you can simplify this by using LOOKUP (but see the caveat below):

=LOOKUP(AVERAGE(B2:F2), {0, 2.8, 3.8, 4.2, 5}, {"FIVE", "FOUR", "THREE", "TWO", "ONE"})

Replace 0 with -1E300 is the average might be negative! Also, you might wrap IFERROR around the LOOKUP expression, in case there no numbers in the range B2:F2 and AVERAGE returns an Excel error.

Caveat: Technically, LOOKUP uses >=0, >=2.8, >=3.8, >=4.2, >=5. So, for example, an average of exactly 2.8 would return "FOUR", not "FIVE" as in your original expression.

If that is critical, there are a number of alternative remedies. The best one depends on details that you have not provided.

Is ROUND(AVERAGE(B2:F2),1) okay? Or do you need to calculate the average with more than 1 decimal place of precision?
Thanks @joeu2004 . @Fluff's solution did the job, but I'll have a play around with this too as i'm sure it will come in handy for something else I'm soon to embark on!

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback.
If you have the new LET functions, it can be simplified like
Excel Formula:
``=LET(Avg,AVERAGE(B2:F2),IF(Avg>=5,"ONE",IF(Avg>=4.2,"TWO",IF(Avg>=3.8,"THREE",IF(Avg>=3,"FOUR",IF(Avg<=2.8,"FIVE","?"))))))``

VKM

Replies
5
Views
213
Replies
6
Views
209
Replies
1
Views
306
Replies
2
Views
233
Replies
2
Views
67

1,127,321
Messages
5,623,998
Members
416,004
Latest member
reitz1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back