Calculation problem

[grayram]

Hi folks,

Been trying several things to solve this problem!

In the attached Summary tab of the worksheet, cells H14 and H16 should reflect that once 10,000 miles has accumulated then with the example figures given those boxes should each read 1000 miles, not 2000 miles in H16. The calculation in H14 and H16 works for case where 10000 is not yet reached and for after 10000 has been reached, but not for the case where 10000 limit is reached for the first time. Any suggestions would be welcomed.

Kind regards
Grayram

[Sam Capricci]

I'm confused, your formula in H14 says that if H9 is less than or equal to 10,000 return H7, if not return 0.
your formula in H16 says that if H9 is greater than 10,000 return the value in H7, otherwise return 0.
H7 is simply pulling the mileage from the Mileage detail tab cell O12 which has 2000.
How is it supposed to return 1000, under what circumstances?

[Pete_UK]

Change the formula in H14 to this:

=IF(H5<=10000,10000-H5,0)

and in H16 to this:

=IF(H9>10000,H7-H14,0)

Hope this helps.

Pete

[grayram]

Genius! Thank you Pete!

[grayram]

I know it was confused! That was my problem. Pete has solved it though. Thank you for your reply!

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[grayram]

All done! Thank you again.