If then with wrong return value

  • Thread starter Thread starter Clark
  • Start date Start date
C

Clark

This If-Then will only return "0" or "3.4" depending upon the contents of H8.
How do I modify it to give me the number I want based upon the contents of
H8?

=IF(H8<=10,3.4,IF(H8>=10<=20,2.7,IF(H8>20<=30,2.3,IF(H8>30<=40,2,IF(H8>40<=80,1.8,IF(H8>80,1.5,))))))
 
Try:

=IF(H8>80,1.5,IF(H8>40,1.8,IF(H8>30,2,IF(H8>20,2.3,IF(H8>10,2.7,3.4)))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
If the number in H8 is never negative then:
=IF(H8<=10,3.4,IF(H8<=20,2.7,IF(H8<=30,2.3,IF(H8<=40,2,IF(H8<=80,1.8,1.5)))))
You cannot use H8>=10<=20 This has to be expressed as AND(H8>=10,H8<=20)
There are other formulas to do this too, but I wanted to show you with IF's,
the way you're trying to do it.

Tyro
 
Another one...

Assuming the value of H8 is *always* a positive number:

=LOOKUP(H8,{0,11,21,31,41,81},{3.4,2.7,2.3,2,1.8,1.5})

If H8 is empty the formula will return 3.4.

To prevent that:

=IF(COUNT(H8),LOOKUP(H8,{0,11,21,31,41,81},{3.4,2.7,2.3,2,1.8,1.5}),"")
 
Back
Top