If statement or Alternative

**Rob1970** · 03-20-2024, 06:44 AM

Good Morning,

Hope all is well with everyone. I am stuck and going round in circles using if statements and hope you can help me. i was wondering if they is another alternative? Or a completely different approach?

I have a grading table that has some numbers in, this is based on criteria in columns H and I,

Basically i would be putting in scores in row 9 through columns B/C/D/E/F

A11 is a cell that i would like to automatically populate showing Excellent / Good / Fair / Needs Improvement / Unsatisfactory / Fail based on what scores i put in.

If each column if blank = Blank
If the score in each column is zero the cell populates "Excellent"
Business Critical Column (B9) is 1 or more than 1 the cell in All populates "Fail"
Critical Column (C9) is 1-3 = "Unsatisfactory"
Critical Column (C9) is 4 or more than 4 = "Fail"
Major Column (D9) is 1-3 = "Needs Improvement"
Major Column (D9) is 4 or more than 4 = "Unsatisfactory"
Minor Column (E9) is 1 or more than 1 = "Fair"
Observation Column (F9) is 1 or more than 1 = "Good"

I hope this makes sense. Please find attached a spreadsheet. Thank you for taking your time and effort into looking into this. It is appreciated.

**etaf** · 03-20-2024, 07:46 AM

IGNORE - got it wrong - as i saw fail and fare the same
EDIT coming

what order of importance is placed on the numbers
for example
would you have in C9 a 4 or more = "Fail"
but in D9 4 or more = "Unsatisfactory"
and F9 =1 then "Good"
which cells take preference
NOTE
IF will work left to right
and so if a condition is met - the rest are ignored

i have worked out the Logic for each of those conditions
so you could put in the order you want - LEFT will happen first

fail OR( B9>=1 , c9>=4 )
Unsatisfactory OR(AND(C9>=1,C9<=3),D9>=4)
Needs Improvement AND(D9>=1,D9<=3)
Fair E9>1
good F9>=1
Excellent Sum(B9:F9)=0
BLANK AND(B9="",C9="",D9="",E9="",F9="")

and in excell

**Rob1970** · 03-20-2024, 08:04 AM

Hi etaf,

The order of the numbers is not important, i would be inputting the scores in B9 to F9, these will range from 0 to whatever.
Once the scores have been inputted i just want cell A11 to populate what grading it is based on the criteria from columns H an I
The numbers in the table is a reflection to the criteria in Columns H and I, basically a helper column.
Ultimately i just want A11 to populate the correct grading based on whatever numbers are inputted in B9 to F9.
Does this make sense?

**etaf** · 03-20-2024, 08:11 AM

ok
EDIT I got it wrong sorry - saw fail and fare as the same
EDIT coming

fail OR( B9>=1 , c9>=4)
Unsatisfactory OR(AND(C9>=1,C9<=3),D9>=4)
Needs Improvement AND(D9>=1,D9<=3)
Fair E9>=1
good F9>=1
Excellent Sum(B9:F9)=0
BLANK AND(B9="",C9="",D9="",E9="",F9="")

==IF(AND(B9="",C9="",D9="",E9="",F9=""),"",IF(SUM(B9:F9)=0,"Excellent",IF(F9>=1,"good",IF(E9>=1,"Fair",IF(AND(D9>=1,D9<=3),"Needs Improvement",IF(OR(AND(C9>=1,C9<=3),D9>=4),"Unsatisfactory",IF(OR(B9>=1,C9>=4),"Fail","criteria not correct entered")))))))

The order is left to right - so if there is a 1 or more in F9 it will ignore the other conditions
hence the question

would you have in C9 a 4 or more = "Fail"
but in D9 4 or more = "Unsatisfactory"
and F9 =1 then "Good"

in the IF above F9 is tested first and so the result will be "good"
D9 and C9 will be ignored as condition already met
hence why i mentioned the order of the IF

**Rob1970** · 03-20-2024, 08:33 AM

Hi etaf,

Thank you, what you have done works if you just input in 1 cell only

If i input a score of 1 in each cell in B9 to F9 it populates "good" when in reality it should populate "Fail" (based on the criteria)

**etaf** · 03-20-2024, 08:53 AM

yes, that was sort of what i was getting at the order matters

FAIL is
fail OR( B9>=1 , c9>=4)

=IF(AND(B9="",C9="",D9="",E9="",F9=""),"",IF(SUM(B9:F9)=0,"Excellent",IF(F9>=1,"good",IF(E9>=1,"Fair",IF(AND(D9>=1,D9<=3),"Needs Improvement",IF(OR(AND(C9>=1,C9<=3),D9>=4),"Unsatisfactory",IF(OR(B9>=1,C9>=4),"Fail","criteria not correct entered")))))))

which condition takes precedent
if more that 1 can appy
as you say
FAIL
because B9 is 1

or do we have to consider various groupings or conditions

E9 = 1
and also
C9 =1
what would the result be
Unsatisfactory or fair

or do we have to say Fair is E9>=1 - providing ALL the other cells are blank or 0

=AND( B9<1, C9<1,D9<1, F9<1 , E9>=1) - type of thing

**Rob1970** · 03-20-2024, 09:40 AM

Hi etaf,

Once all cells B9 to F9 have been inputted.

1. Fail
2. Unsatisfactory
3. Needs Improvement
4. Fair
5. Good
6. Excellent

**etaf** · 03-20-2024, 10:01 AM

SO rearranging to that order

=IF(AND(B9="",C9="",D9="",E9="",F9=""),"",
IF(OR(B9>=1,C9>=4),"Fail",
IF(OR(AND(C9>=1,C9<=3),D9>=4),"Unsatisfactory",
IF(AND(D9>=1,D9<=3),"Needs Improvement",
IF(E9>=1,"Fair",
IF(F9>=1,"good",
IF(SUM(B9:F9)=0,"Excellent",
"criteria not correct entered")))))))

so now thats the order you gave 1-6

1. Fail
2. Unsatisfactory
3. Needs Improvement
4. Fair
5. Good
6. Excellent

Once all cells B9 to F9 have been inputted.

BUT it will do it as you enter - its not waiting for each cell to be complete - which would probably need VBA to do that

**AliGW** · 03-20-2024, 10:09 AM

Maybe it can be tweaked so as not to need VBA:

=IF(AND(B9="",C9="",D9="",E9="",F9=""),"",
IF(OR(B9="",C9=""),"",
IF(OR(B9>=1,C9>=4),"Fail",
IF(OR(AND(C9>=1,C9<=3),D9>=4),"Unsatisfactory",
IF(AND(D9>=1,D9<=3),"Needs Improvement",
IF(E9>=1,"Fair",
IF(F9>=1,"good",
IF(SUM(B9:F9)=0,"Excellent",
"criteria not correct entered"))))))))

**Rob1970** · 03-20-2024, 10:25 AM

Hi etaf,

Brilliant, it works.

I have changed it a little bit similar to AliGW help below.

=IF(AND(B9="",C9="",D9="",E9="",F9=""),"",
IF(OR(B9="",C9="",D9="",E9="",F9=""),"",
IF(OR(B9>=1,C9>=4),"Fail",
IF(OR(AND(C9>=1,C9<=3),D9>=4),"Unsatisfactory",
IF(AND(D9>=1,D9<=3),"Needs Improvement",
IF(E9>=1,"Fair",
IF(F9>=1,"good",
IF(SUM(B9:F9)=0,"Excellent",
"criteria not correct entered"))))))))

You are a start, thank you so much for your help it is appreciated.
Hope you have a great day.

**Rob1970** · 03-20-2024, 10:28 AM

Hi AliGW,

Brilliant, it works.

I have changed it a little bit, see below.

=IF(AND(B9="",C9="",D9="",E9="",F9=""),"",
IF(OR(B9="",C9="",D9="",E9="",F9=""),"",
IF(OR(B9>=1,C9>=4),"Fail",
IF(OR(AND(C9>=1,C9<=3),D9>=4),"Unsatisfactory",
IF(AND(D9>=1,D9<=3),"Needs Improvement",
IF(E9>=1,"Fair",
IF(F9>=1,"good",
IF(SUM(B9:F9)=0,"Excellent",
"criteria not correct entered"))))))))

You are a start, thank you so much for your help it is appreciated.
Hope you have a great day

**etaf** · 03-20-2024, 10:29 AM

cool tweak from AliGW
never would have thought of that ... power of the forums

anyway
Glad to have helped

**Rob1970** · 03-20-2024, 10:36 AM

Hi etaf,

You have helped in a big way, understand more about about the ordering because of you, for that Kudos to you.

Have a great evening etaf

**etaf** · 03-20-2024, 10:37 AM

will do and yourself

**DJunqueira** · 03-20-2024, 10:57 AM

Please, not IF(AND(B9="",C9="",D9="",E9="",F9=""),"", use IF(CONCAT(B9:F9)="",""

And this could look more well organized.

Formula:

Please Login or Register  to view this content.

**AliGW** · 03-20-2024, 12:30 PM

Originally Posted by etaf

cool tweak from AliGW
never would have thought of that ... power of the forums

LOL! Teamwork ...

**DJunqueira** · 03-20-2024, 02:08 PM

Agree!

**Rob1970** · 03-21-2024, 05:02 AM

Hi DJunqueira,

Brilliant, thank you so much.

Hope you have a great day.

**DJunqueira** · 03-21-2024, 06:44 AM

Tks for the feedback, glad to have helped.

If statement or Alternative

LinkBack

Thread Tools

Rate This Thread

Display

If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Re: If statement or Alternative

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Alternative to IF statement

[SOLVED] Nested If/And Statement or an Alternative?

[SOLVED] IF Statement Alternative

[SOLVED] Alternative to IF statement?

Excel 2007 : Alternative to if statement ?

Alternative to a if statement

[SOLVED] alternative to if statement

Bookmarks

Bookmarks

Posting Permissions