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:
Can anyone help?
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?