anything over 10,000 ?

[coltheplumb]

Hi ho you lot , i am trying to sort out a calc that refers to my milage claim form, At the moment i have an ongoing monthly total for milage claimed which shows in C1, so what i am after is when that running total reaches 10,000 miles in C1, i would like anything i enter after it reaches 10,000 to show in D1 , so just to clarify if i am showing 9,800 miles in C1 then the following month i put a total of 350 miles, it will show 10,000 in cell C1 and 150 miles in D1.

Thanks for your help on this
Colin x

 

[Jonmo1]

The MIN and MAX functions are usefull here..


C1: =MIN(10000,TotalMiles)
D1: =MAX(0, TotalMiles-10000)

Hope that helps.

 

[coltheplumb]

ermmm just tried that and it dont seem to work ? perhaps i am entering it wrong?
i dont need to put "total miles" in with the calc do i ?
sorry if i am comming across daft

 

[Jonmo1]

Say your total miles is in B1

C1: =MIN(10000,B1)
D1: =MAX(0, B1-10000)

 

[coltheplumb]

Well done johnmo1.
thanks ever so much with your help, very much appriciated

Colinx

 

[Jonmo1]

Glad it sorted you out!