IF, AND, OR HELP

[gretacristina]

Hello team,

I'm reaching out regarding this previous post: https://techcommunity.microsoft.com/t5/excel/if-and-or-help/m-p/3602812/highlight/true

I'm struggling again with formulas. What I'm trying to obtain is the following:

In cell B4 from Capture 1, I need to have a score from 1 to 5 on the following conditions:

5 - over 80%
4 - 70-80% 
3 - 60-70% 
2 - 50-60% 
1 - less 50% 

and I need to gather the AVERAGE of the column G from Capture 2 (a different sheet in the same Excel document) every time in column B from Capture 2 I have the word "JANUARY".

To be more specific, I could have more than 1 row with JANUARY on column B and I want to give the score depending on an average. 

I used the following formula: 

=IF('Dashboard Data'!B2="JANUARY",IFS('Dashboard Data'!G2>=0.8,5,'Dashboard Data'!G2>=0.7,4,'Dashboard Data'!G2>=0.6,3,'Dashboard Data'!G2>0.5,2,'Dashboard Data'!G2<=0.5,1,TRUE,"No Data"))

I tried by using B:B in stead of !B2 but it did not work. I could really use your help.

Capture 1

Capture 2

Thank you,

Cristina

[#2]

@gretacristina 

Re: 1-5 scale

There's no need for lengthy logical formulas in this case. A VLOOKUP with an approximate match can simplify this.

=VLOOKUP(A2,$F$1:$G$5,2)

Please see attached.

[#3 · Accepted solution]

@gretacristina 

=IFERROR(AVERAGE(IF('Dashboard Data'!$B$2:$B$22=$A4,IF('Dashboard Data'!$G$2:$G$22>=0.8,5,IF('Dashboard Data'!$G$2:$G$22>=0.7,4,IF('Dashboard Data'!$G$2:$G$22>=0.6,3,IF('Dashboard Data'!$G$2:$G$22>0.5,2,IF('Dashboard Data'!$G$2:$G$22<=0.5,1,""))))))),"")

For the AVERAGE in column G you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

Sheet "Dashboard Data":

 

[#4]

I believe I have an older version of excel because I'm not able to use the vlookup function

[#5]

Thank you! It worked :)

[#6]

That's odd that you don't have VLOOKUP. The function has been around since 1985 with Excel 1.