Nested IF based off an AVERAGE (range)

[VKM]

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?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=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","?")))))

 

[joeu2004]

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]

Hi & welcome to MrExcel.
How about

=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]

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]

Glad to help & thanks for the feedback.
If you have the new LET functions, it can be simplified like

=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","?"))))))