Need Help

R

Renee100

I am new to excel formulas and I need help to write a formula that will
allow me to use an IF statement to:

Assign a cell a different value based on the range another cells
numbers fall into. The problem also is that the range can be positive
or negative number. For example, if the number that is entered into
cell A1 is equal to or falls between -0.5% and 0.5%, I need the word
touchdown to be assigned to cell D1. BUT, if the number falls between
-0.75% and -0.50% and 0.50% and 0.75% than the word 10yard should be
assigned to cell D1. There are 3 more conditions to follow. It is a
continous range of numbers. The breaks are:
+/- 0.50% than D1= touchdown
+/- 0.75% than D1=10 yard
+/- 1.0% than D1= 20 yard
+/- 1.25% than D1 = 30yard
+/- 1.50% than D1= 40 yard
+/- 1.75% than D1 = 50 yard
Can someone please show me how. Thanks!!!!
 
D

Dave F

=IF(OR(A1=-.5,A1=.5),"touchdown",IF(OR(A1=-.75,A1=.75),"10 yard",IF([etc.])))))

Make sure your parentheses agree.

Dave
 
C

CLR

Maybe something similar to this.........

=LOOKUP(A1,{-100,-1.75,-1.5,-1.25,-1,-0.75,-0.5,0.5,0.75,1,1.25,1.5,1.75,1.76},{"","50
yard","40 yard","30 yard","20 yard","10 yard","Touchdown","Touchdown","10
yard","20 yard","30 yard","40 yard","50 yard",""})

......all on one line, watch out for word-wrap when copying......

hth
Vaya con Dios,
Chuck, CABGx3
 
S

Sandy Mann

Renee,

Would
=CHOOSE(FLOOR(ABS(A1)/0.0025,1),"touchdown","touchdown","10 yards","20
yards","30 yards","40 yards","50 yards","Unknown")

or

=IF(ABS(A1)<0.75%,"touchdown",FLOOR(ABS(A1)/0.0025,1)*10 &" yards")

do what you want?

--
HTH

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

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

Sandy Mann

Sandy Mann said:
Would
=IF(ABS(A1)<0.75%,"touchdown",FLOOR(ABS(A1)/0.0025,1)*10 &" yards")

do what you want?
Click to expand...

No it wouldn't! but

=IF(ABS(A1)<0.75%,"touchdown",FLOOR(ABS(A1)/0.0025,1)*10-20 &" yards")

might do though.

--
HTH

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

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