Help Needed With Current Mileage Calculation

Screenshot.png

Looking for some knowledgeable help to figure out how I may be able to construct a formula, unless a Macro is needed for this instance, to calculate the current mileage for cell G21 without showing a negative or incorrect number. Currently, I have cells D11, D15, H11, and H15 calculating the quarterly mileage with a simple formula of =SUM(D9-D10), showing just Q1 formula for the example. Cells D17, D19, G17, and G19 are just restating the total mileage per quarter. What my goal is, is to have cell G21, which is currently using a formula of =SUM(D17+D19+G17+G19), be able to show the current mileage so far for the year but the issue I run into is that if you have Q1 fully calculated out and submit the beginning mileage for Q2, it throws the total current annual mileage off with an incorrect mileage reading. Is there a helpful way or formula that can be built or used to show a calculated current mileage without other input per quarter throwing it off?

A little background info - I am using some spare time off from work to build a spreadsheet for my boss that helps us keep record keeping for expenses, costs, mileage, ect. for our trucking company. At this point, I may be overlooking something simple to my problem/question as I am about burnt out!

Thank you in advance for any help, response, or time in looking into my proposed question. I really appreciate it!

Sincerely,

BaconFanatic2

Alan,

Please let me apologize as I should've looked for some forum rules and standards prior to posting. However, thank you for informing me as well as briefing me on how to post appropriately.

Attached is a copy of the spreadsheet. Below are the formulas used for specific cells. The outcome I am looking for is to get cell G14 (attached sample workbook) to show a current mileage based on adding each quarter together without showing a negative calculation or a wrong number due to inputting the beginning mileage for the next quarter and not being able to show a full quarter worth of mileage yet.

Please let me know if this is a correct way to post my question in this forum as I would like to make my question easier for anybody who takes the time to read my post. If not, I will edit and repost.

Thank you for the warm welcome!

-BaconFanatic2

Quarterly Mileage:

Cell D4: =SUM(D2-D3)
Cell D8: =SUM(D7-D6)
Cell H4: =SUM(H2-H3)
Cell H8: =SUM(H7-H6)

Total Mileage for Year:

Cell G14: =SUM(D10+D12+G10+G12)

Ford,

Hi and thank you for the warm welcome! It is much appreciated.

I won't lie, it's been some time since college since I used Excel to this degree, but I have enjoyed getting back into the swing of things with it. You are right though and you make a good point! Sometimes the unnecessary things take up time!

Thank you for taking some time to look at my proposed question.

Sincerely,

BaconFanatic2

Ford,

This is exactly what I was searching for! I cannot thank you enough for your time and help. Looks like your IF function along with a more streamlined layout as you said, was the answer to my question. Your layout allows for an easier view and more feasible presentation.

Wonderful!!! Thank you very much!!!

I'm intrigued enough now to go browse around the forum and see what other interesting formulas and posts I can come across.

Sincerely,

BaconFanatic2

That's actually a really great idea that would help to simplify the layout and allow one to view more data with less effort involved.

I enjoy figuring out ways to build spreadsheets like this, something fun to learn and to challenge myself with in my off time.

What would you recommend or suggest for a way to show and present summaries to compare current year to last year Ford?

Much appreciated Ford!

Looked over what you put together and now understand what you are saying/proposing. And I couldn't agree more with you that this presents the needed data in a much simpler, cleaner, well thought out way.

Ford I cannot thank you enough for the help you have provided me in this. This is exactly, and more, what I was seeking!

Hope to keep in touch!

Sincerely,

BaconFanatic2

Oeldere,

Thank you for the added informative pivot table! Goes to show there is always more than one way to get it done.

Thanks again!

My pleasure! And thanks for the heads up on the 'Solved' indicator.

Have a great rest of your weekend!