Help in < or > mathematical excel equations

P

polardude1983

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")
 
P

polardude1983

polardude1983 said:
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")
Click to expand...

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"))
 
M

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

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.
 
B

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
 
D

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.
 
R

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

What about this one formula:

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

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

[email protected]
[email protected] with @tiscali.co.uk
 
Top