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:

45.25	5.25
59.25	19.25
43.25	3.25
29.75	-10.25
30.5	-9.5
21	-19

<colgroup><col><col></colgroup><tbody>
</tbody>

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?