Please help me smart people of the world - IF (range) contains...

[indyella]

I am going crazy trying to work out this excel problem:

......F

82 1.0
83 0.5
84 0.0
85 0.5
86 0.0
87 #

In order of priority, with only 1 number possible in f87

If range (f82:f86) has a 0.0 in it. cell f87 = 5 (if false go to next line)
If range (f82:f86) has a 0.5 in it. cell f87 = 10 (if false go to next line)
then range (f82:f86) must have 1.0 in it. cell f87 = 20

any help would be apreciated. thanks!

 

[mikerickson]

Perhaps

=IF(COUNTIF(F82:F86,0)>1, 5, COUNTIF(F82:F86,.5)>0,10, 20))

If 0, .5, and 1 are the only possible entries in those cells

=CHOOSE(2*MIN(F82:F86)+1,5, 10,20)

 

[indyella]

=CHOOSE(2*MIN(F82:F86)+1,5, 10,20)[/QUOTE]


thank-you so much for this. worked a treat. i wasted hours trying to work this out and you did it in mintues.

absolute champion!

cannot thank you or this board and its members enough