How to get a running total with some blank cells

1. How to get a running total with some blank cells

Hello,

I have a spreadsheet which I developed as a Log Book for my vehicle where I enter all costs incurred including cost of fuel and cost per litre when it was purchased.
I have columns for Date, Odometer, Trip, Total cost, Cost per litre plus fuel usage (calculated) -
I have a column which calculates litre used per 100 kilometers (L/100km) for each separate purchase which works fine.

I would like to add a column which automatically calculates average fuel usage since the log book commenced.
I have used =SUM(a1:a2)/(SUM(b1:b2)/100) which works until it encounters a cell in a column (column a and/or b) which is blank which can happen when I record a cost which was NOT fuel and then it returns "divide by zero" which I feel is because it is attempting to add a value for a blank cell. I think I need to use IF but not sure how to.

Could someone please advise how I can modify my formula to work.

Thanks

2. Re: How to get a running total with some blank cells

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. Re: How to get a running total with some blank cells

It is a new day since I created the thread and I now find that the error I reported doesn't seem to be occurring anymore BUT I still have a problem.

In the past when I created a 'formula' in a cell using the values from other cells when I then 'drag' that cell down the column the formula is copied
to each cell and the cell 'addresses' are changed automatically by incrementing them by 1.
I don't want this to happen for some of the cell addresses.

In each row (a fuel purchase) I calculate the fuel economy for that current trip.
I also want to automatically calculate the AVERAGE fuel economy since the beginning NOT taking an average of each trip fuel economy but using the total
fuel used and total distance travelled since the beginning.

I have attached a shortened version of my spread sheet.
Note column I (formula is =SUM(F4:F5)/(SUM(C4:C5)/100) )
When I drag cell IF down the column the formula is copied to each cell BUT the first 'parameter' of each 'SUM' is changed and I want it to remain as F4 and C4.

I have manually changed the formula in cells I6 and I7 to be correct.

NOTE that in the following cells the first cell in each SUM is increased by 1 eg F5 and C5.

Hope I have explained enough.

It's been many years since I was working in the IT industry and so don't use Excel very much these days.

I have uploaded my spreadsheet using 'manage attachments' but can't work out how to attach it to this post, sorry

4. Re: How to get a running total with some blank cells

Hello jgplarnold and Welcome to Excel Forum.
If I understand correctly, paste the following formula into cell I5 then double click the fill handle (lower right hand corner of the cell) to copy down:
Formula:
`Please Login or Register  to view this content.`

If I have misunderstood and formula doesn't resolve the issue, please tell us the values that you expect to see in column I, and why, so that we can attempt to replicate those values with either a formula or code.
Let us know if you have any questions.

5. Re: How to get a running total with some blank cells

Note column I (formula is =SUM(F4:F5)/(SUM(C4:C5)/100) )
When I drag cell IF down the column the formula is copied to each cell BUT the first 'parameter' of each 'SUM' is changed and I want it to remain as F4 and C4.
Hi, Just lock the cell using \$ sybol to where ever necessary .

Formula:
`Please Login or Register  to view this content.`

6. Re: How to get a running total with some blank cells

I thought I had replied to you Jete - well I had typed in a reply which must have disappeared into the 'ether'.

Yes it works exactly as I wanted.

Thanks for your reply. I suspected that using the \$ symbol would cause that reference to NOT increment as you dragged the cell's formula down the column as boopathiraja has now confirmed.

8. Re: How to get a running total with some blank cells

Thanks Ali for reminding me.
Most forums don't require this so I forgot. Will now close it.

9. Re: How to get a running total with some blank cells

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1