Forum Discussion
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?
Thank you in advance!
- 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.
6 Replies
- 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.- It worked! Thank you so much!
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)- 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.
- Can you check your excel version? XLOOKUP() only exist on Microsoft-365.
