How do you get excel to automatically subract if its over a certain value?

[JustOneQuestion]

I am trying to get excel to show what is left over 40. Is it possible? I do not want any negative numbers either. Here is an example:
[TABLE="width: 145"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]45.25
[/TD]
[TD="align: right"]5.25
[/TD]
[/TR]
[TR]
[TD="align: right"]59.25[/TD]
[TD="align: right"]19.25[/TD]
[/TR]
[TR]
[TD="align: right"]43.25[/TD]
[TD="align: right"]3.25[/TD]
[/TR]
[TR]
[TD="align: right"]29.75[/TD]
[TD="align: right"]-10.25[/TD]
[/TR]
[TR]
[TD="align: right"]30.5[/TD]
[TD="align: right"]-9.5[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]-19
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
All I did was subtract 40 from column A. But I do not want the values in B if it is negative. If the value in A isn't over 40, I do not want anything in column B. I tried a countif formula, but that will either take the whole number, or only a 1. Neither of which helps me.

Any thoughts?

Thanks!

 

[Trebor76]

Assuming 45.25 is in cell A2 (change to suit) try this:

=IF(A2>=40,A2-40,0)

Robert

 

[JustOneQuestion]

That works great. Thank you so much

 

[Trebor76]

You're welcome

 

[jtakw]

Hi,

This will also work:

Excel 2010
	A	B
1	45.25	5.25
2	59.25	19.25
3	43.25	3.25
4	29.75	0
5	30.5	0
6	21	0
Sheet1

Cell Formulas
Range	Formula
B1	=MAX(0,A1-40)

 

[Rick Rothstein]

I am trying to get excel to show what is left over 40.

Could you have a number over 80 (like 99 for instance) so that the subtraction was greater than 40; and, if so, what value do you want?