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?