SOLVED

IF, AND, OR HELP

Occasional Contributor

IF, AND, OR HELP

Hello team!

I'm trying to create a formula for the following situation:

In cell B4 (capture 1) I want to display a mark from 1 to 5, on the following conditions:

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

The percentage would be monthly updated in a different sheet, in Column G (see capture 2 below)

Capture 1

Capture 2

I tried with this formula but is not showing what I need. I'm sure something is wrong.

=IFS(AND('Dashboard Needs'!B2="July",
OR('Dashboard Needs'!G2>=80%,'Dashboard Needs'!G2<100%)),"5",
OR('Dashboard Needs'!G2>=70%,'Dashboard Needs'!G2<80%),"4",
OR('Dashboard Needs'!G2>=60%,'Dashboard Needs'!G2<70%),"3",
OR('Dashboard Needs'!G2>=50%,'Dashboard Needs'!G2<60%),"2",
OR('Dashboard Needs'!G2>=0%,'Dashboard Needs'!G2<50%),"1",
TRUE,"no data")

Could someone please give me a hand?

6 Replies

Re: IF, AND, OR HELP

@gretacristina You can try XLOOKUP() function. See the attached file.

``=IF('Dashboard Needs'!B2="July",XLOOKUP('Dashboard Needs'!G2,{0.8,0.7,0.6,0.5,0.1},{5,4,3,2,1},"no data",-1),FALSE)``

Re: IF, AND, OR HELP

Hi @Harun24HR, thank you so much for your reply. I copied the formula to my Excel file and for some reason I get the #NAME? error. It happens the same when I'm changing the percentage in the file you sent me.

Re: IF, AND, OR HELP

Can you check your excel version? XLOOKUP() only exist on Microsoft-365.
best response confirmed by gretacristina (Occasional Contributor)
Solution

Re: IF, AND, OR HELP

Please modify with simple formula like,
=IF(B2="JULY",IFS(G2>=80,5,G2>=70,4,G2>=60,3,G2>=50,2,G2<50,1,TRUE,"No Data"))
Incase of percentage you can add percentage symbol or 0.XX Decimal in the formula.
Greater than and equal to 80% is 5 and Less than 50 is 1,
Please do remember inverted commas "" is applicable only to text not number.

Re: IF, AND, OR HELP

It worked! Thank you so much!

Re: IF, AND, OR HELP

That's probably why it would not work. Thank you for the help!