Help in < or > mathematical excel equations

[Guest]

basically what i want to do is if Cell number H2 is greater then 0.01 and
less then 0.99 then H2 equals 0.20

but if H2 is greater then 1.00 and less then 9.99 then H2 equals 0.40
can anybody help me with this? i have this so far

=IF(AND(H2<0.99,H2>0.01),"0.20","Invalid")

 

[Guest]

basically what i want to do is if Cell number H2 is greater then 0.01 and
less then 0.99 then H2 equals 0.20

but if H2 is greater then 1.00 and less then 9.99 then H2 equals 0.40
can anybody help me with this? i have this so far

=IF(AND(H2<0.99,H2>0.01),"0.20","Invalid")

I GOT IT here is for some people who might want to know

=IF(AND(H2<=0.99,H2>=0.01),"0.20",IF(AND(H2<=9.99,H2>=1),"0.40","INVALID"))

 

[MyVeryOwnSelf]

basically what i want to do is if Cell number H2 is greater then 0.01

and less then 0.99 then H2 equals 0.20

but if H2 is greater then 1.00 and less then 9.99 then H2 equals 0.40
can anybody help me with this? ...

Taking your problem statement literally, I came up with
=IF(H2>=9.99,"undefined",IF(H2>1,0.4,IF(H2>=0.99,"undefined",
IF(H2>0.01,0.2,"undefined"))))

But notice that you left the value undefined if H2 is exactly .990 or 1.000
or someplace in between (for example). Other “edge effects” seem fuzzy too.
This probably isn’t what you intended, so modify to suit.

 

[Bernd]

In one formula, if your input cell is D1:
=LOOKUP(D1,{-1E+307;0.01;1;10},{"invalid";0.2;0.4;"invalid"})

For better maintenance you can put into A1:B4
-1E307 invalid
0.01 0.2
1 0.4
10 invalid

=LOOKUP(D1,A1:A4,B1:B4)

Please notice that here the implicit comparisons are <1 resp. <10 (and
not <.99 and <9.99).
If this is what you thought it should be then LOOKUP may be your
choice.

Regards,
Bernd

 

[David Biddulph]

Bear in mind that by putting your 0.20 and 0.40 in quotes you are outputting
a text string, not a number.

 

[Rolo]

basically what i want to do is if Cell number H2 is greater then 0.01 and
less then 0.99 then H2 equals 0.20

but if H2 is greater then 1.00 and less then 9.99 then H2 equals 0.40
can anybody help me with this? i have this so far

=IF(AND(H2<0.99,H2>0.01),"0.20","Invalid")

What about this one formula:

=IF(AND(K2>0.01,K2<0.99),0.2,IF(AND(K2>1,K2<10),0.4,"Invalid"))

 

[JE McGimpsey]

Just for another alternative:

=0.2*(ABS(0.5-K2)<0.49) + 0.4*(ABS(5.5-K2)<4.5)

 

[Sandy Mann]

I know that the problem originates in the OP's description because he does
not specify what should happen if K2=1. As written your formula returns 0,
perhaps either:

=0.2*(ABS(0.5-K2)<=0.5) + 0.4*(ABS(5.5-K2)<4.5)
to return 0.2 for K2=1 or
=0.2*(ABS(0.5-K2)<0.49) + 0.4*(ABS(5.5-K2)<=4.5)
to return 0.4 for K2=1


--
Regards,

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk