IF / AND formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have calculations that will give me an average score somewhere between 0
and 4 for a number of items. I'm trying to build a formula that says "if the
result is between 0 and 1", then write the word "poor" in the cell. If
between, 1 & 2 then "fair". If between 2 & 3, then "good". If between 3 &
4, then "excellent". I've tried variations of IF and AND formulas, but to no
avail. My results are either an "error" or only the 1st (poor) and last
(excellent) options, but not "fair" or "good".

Anyone familiar with IF / AND formulas for multiple variables?
 
UNCCNU said:
I have calculations that will give me an average score somewhere between 0
and 4 for a number of items. I'm trying to build a formula that says "if
the
result is between 0 and 1", then write the word "poor" in the cell. If
between, 1 & 2 then "fair". If between 2 & 3, then "good". If between 3
&
4, then "excellent". I've tried variations of IF and AND formulas, but to
no
avail. My results are either an "error" or only the 1st (poor) and last
(excellent) options, but not "fair" or "good".

Anyone familiar with IF / AND formulas for multiple variables?
Click to expand...

Try
=IF(A1>3,"excellent",IF(A1>2,"good",IF(A1>1,"fair","poor")))

You may need to use ">=" rather than ">" depending which word you want on
the boundaries.

Stephen
 
You don't say what is to happen if exactly 1, 2, 3 or 4 but try this.

=LOOKUP(B1,{0,1,2,3,4},{"poor","fair","good","excellent","Over 4"})


Gord Dibben MS Excel MVP
 
Your boundaries are overlapping!

Is "1" Poor or Fair?
Is "2" Fair or Good?
.... etc.

Will decimals be involved?

As a guess:

=IF(A1>3,"Excellent",IF(A1>2,"Good",IF(A1>1,"Fair","Poor")))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have calculations that will give me an average score somewhere between 0
and 4 for a number of items. I'm trying to build a formula that says "if
the
result is between 0 and 1", then write the word "poor" in the cell. If
between, 1 & 2 then "fair". If between 2 & 3, then "good". If between 3 &
4, then "excellent". I've tried variations of IF and AND formulas, but to
no
avail. My results are either an "error" or only the 1st (poor) and last
(excellent) options, but not "fair" or "good".

Anyone familiar with IF / AND formulas for multiple variables?
 
=VLOOKUP(A21{0,"poor";1,"fair";2,"good";3,"excellent";4.00001,"ERROR!"},2)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have calculations that will give me an average score somewhere between 0
| and 4 for a number of items. I'm trying to build a formula that says "if the
| result is between 0 and 1", then write the word "poor" in the cell. If
| between, 1 & 2 then "fair". If between 2 & 3, then "good". If between 3 &
| 4, then "excellent". I've tried variations of IF and AND formulas, but to no
| avail. My results are either an "error" or only the 1st (poor) and last
| (excellent) options, but not "fair" or "good".
|
| Anyone familiar with IF / AND formulas for multiple variables?
 
Back
Top