Excel formula help

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have tried unsucessfully to come up with a formula where cell
A3 = A1 - A2. I would like for the Sum of A3 to not be greater than
15, and not less than -15. My formulas thus far have been,
=if(A1-A2>=15,15*1,A1-A2). This will work for the greater than 15, but
when I add the statement
=if(A1-A2>=15,15*1,A1-A2)=if(A1-A2>=-15,-15*1)I get an error. Any help
and all ideas welcome.
Thank you, Rick
 
Hi Rick

=IF(A3-A2>15,15,IF(A3-A2<-15,-15,A3-A2))


--
XL2002
Regards

William

(e-mail address removed)

| I have tried unsucessfully to come up with a formula where cell
| A3 = A1 - A2. I would like for the Sum of A3 to not be greater than
| 15, and not less than -15. My formulas thus far have been,
| =if(A1-A2>=15,15*1,A1-A2). This will work for the greater than 15, but
| when I add the statement
| =if(A1-A2>=15,15*1,A1-A2)=if(A1-A2>=-15,-15*1)I get an error. Any help
| and all ideas welcome.
| Thank you, Rick
|
 
Rick,

I was testing in the wrong cells - try this
=IF(A2-A1>15,15,IF(A2-A1<-15,-15,A2-A1))

--
XL2002
Regards

William

(e-mail address removed)

| I have tried unsucessfully to come up with a formula where cell
| A3 = A1 - A2. I would like for the Sum of A3 to not be greater than
| 15, and not less than -15. My formulas thus far have been,
| =if(A1-A2>=15,15*1,A1-A2). This will work for the greater than 15, but
| when I add the statement
| =if(A1-A2>=15,15*1,A1-A2)=if(A1-A2>=-15,-15*1)I get an error. Any help
| and all ideas welcome.
| Thank you, Rick
|
 
William said:
Rick,

I was testing in the wrong cells - try this
=IF(A2-A1>15,15,IF(A2-A1<-15,-15,A2-A1))

--
XL2002
Regards

William

(e-mail address removed)

| I have tried unsucessfully to come up with a formula where cell
| A3 = A1 - A2. I would like for the Sum of A3 to not be greater than
| 15, and not less than -15. My formulas thus far have been,
| =if(A1-A2>=15,15*1,A1-A2). This will work for the greater than 15, but
| when I add the statement
| =if(A1-A2>=15,15*1,A1-A2)=if(A1-A2>=-15,-15*1)I get an error. Any help
| and all ideas welcome.
| Thank you, Rick
|
Click to expand...
 
Different approach, plus a little shorter:

=MAX(-15,MIN(A1-A2,15))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thank you very much William. This will work just right, I thank you for
your time.
Rick
 
I have tried unsucessfully to come up with a formula where cell
A3 = A1 - A2. I would like for the Sum of A3 to not be greater than
15, and not less than -15. My formulas thus far have been,
=if(A1-A2>=15,15*1,A1-A2). This will work for the greater than 15, but
when I add the statement
=if(A1-A2>=15,15*1,A1-A2)=if(A1-A2>=-15,-15*1)I get an error. Any help
and all ideas welcome.
Thank you, Rick
Click to expand...


=MIN(MAX(A1-A2,-15),15)


--ron
 
Back
Top