+ Reply to Thread
Results 1 to 9 of 9

How to get a running total with some blank cells

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Sydney, Australia
    MS-Off Ver
    7
    Posts
    4

    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. #2
    Forum Guru nflsales's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2003; 2007
    Posts
    6,310

    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.
    Click *Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) If you are satisfied and add reputation the for the one who helps you

    Regards

    Samba

  3. #3
    Registered User
    Join Date
    01-03-2018
    Location
    Sydney, Australia
    MS-Off Ver
    7
    Posts
    4

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

    Thanks for your quick reply.

    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
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    5,145

    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: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,388

    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: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Booşathì

  6. #6
    Registered User
    Join Date
    01-03-2018
    Location
    Sydney, Australia
    MS-Off Ver
    7
    Posts
    4

    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.

    Thanks for both your replies.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,655

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

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  8. #8
    Registered User
    Join Date
    01-03-2018
    Location
    Sydney, Australia
    MS-Off Ver
    7
    Posts
    4

    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. #9
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    5,145

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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