Nested IF based off an AVERAGE (range)

VKM

New Member
Joined
Jan 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:
IDENTIFIERITEM_1ITEM_2ITEM_3ITEM_4ITEM_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?
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
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","?")))))
 
  • Like
Reactions: VKM
Solution

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,014
Office Version
  1. 2010
Platform
  1. Windows
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?
 
  • Like
Reactions: VKM

VKM

New Member
Joined
Jan 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
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
Joined
Jan 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
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","?"))))))
 
  • Like
Reactions: VKM

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top