IF / AND formula

[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?

 

[Stephen]

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?

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

 

[Gord Dibben]

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

 

[RagDyeR]

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?

 

[Niek Otten]

=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?

 

[Guest]

Thank you both for your help!