If Formula for displaying a message

  • Thread starter Thread starter Sherees
  • Start date Start date
S

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
 
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
 
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
Click to expand...

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)
 
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)
 
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 :)
 
Back
Top