If Formula for displaying a message

[Sherees]

Hi

If A1 is equal to or greater than 80 but less than 89,b1 =abc. If A1 is
equal to or greater than 90 but less than 99, b1 =def. If A1 is equal to or
greater than 100 but less than 119,b1 =ghi--
Please help

Thanks
Sherees
Life isa journey not a destination

 

[מיכ×ל (מיקי) ×בידן]

Try
=IF(AND(A1>80,A1<89),"abc",IF(AND(A1>=90,A1<99),"def",IF(AND(A1>100,A1<119),"ghi","")))
Micky

 

[albertmb]

Hi Sherees,
I am sure an expert can find a better formula, but until then you can try
this:

IF(A1<=79,"",IF(A1=80,"abc",IF(A1<=(90-1),"abc",IF(A1=90,"def",IF(A1<=(100-1),"def",IF(A1=100,"ghi",IF(A1<=(120-1),"ghi","")))))))

Regards
Albert

 

[מיכ×ל (מיקי) ×בידן]

Albert,
What is the reason for using (90-1) instead of 89 !?
Micky

 

[David Biddulph]

I think that the 80 and 100 should be >=, rather than > ?

 

[David Biddulph]

Couldn't
IF(A1<=79,"",IF(A1=80,"abc",IF(A1<=(90-1),"abc",IF(A1=90,"def",IF(A1<=(100-1),"def",IF(A1=100,"ghi",IF(A1<=(120-1),"ghi","")))))))
be simplified to
IF(A1<=79,"",IF(A1<=89,"abc",IF(A1<=99,"def",IF(A1<=119,"ghi","")))) ?

The OP wasn't clear what was wanted if A1 was >=89 and <90, or >=99 and
<100, and you've left the extra question about >79 and <80 [which latter
question could have been avoided by changing your A1<=79 to A1<80].

It is possible that the OP wanted
IF(A1<80,"",IF(A1<90,"abc",IF(A1<100,"def",IF(A1<120,"ghi",""))))
though of course that wasn't what was asked for.

 

[Sherees]

Thanks a lot Micky!
Thank you too Albert but Mickys answer solved my question

 

[Mike H]

Hi,

Another approach possibly a bit shorter

=LOOKUP(A1,{0,80,90,100,119},{"","abc","def","ghi",""})

but note your thresholds are very confusing. For example

I.f A1 is equal to or greater than 80 but less than 89
If A1 is equal to or greater than 90 but less than 99

This means you want nothing in the cell if A1=89 bit I don't really believe
you mean that so I have eliminated these 'gaps'

--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)

 

[Mike H]

Seems like I was wrong you do want gaps at 89,99 & 100 but I still prefer the
shorter approach

=LOOKUP(A1,{0,80,89,90,100,119},{"","abc","","def","ghi",""})


--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)

 

[albertmb]

Good Question Mike, but it was my mistake, I used copy and paste and was
getting the same answer, at first I thought it was because I used the exact
number but then I realised it was because I did not change the answer. I
told you I am no expert No