How can I make this spreadsheet work?

[snm6996]

I'm working on a spreadsheet which needs to populate info for an entire year. What I need the Total Meter Worked columns to do is if the end meter is a zero, to populate a zero. If the end meter has a number, then to subtract the end meter from the previous month. However, if the previous month also has an end meter of zero, then it needs to go back to the previous month's previous month, etc.The formula works fine if it only goes back a month or two. However, if it has to go back to the start meter, I'm having issues. The first yellow cell in column AT should be 120 not 2763 (AS17-K17 i.e. 2763-2643) and the second yellow cell should be 83 not 3064 (AS21-K21 i.e. 3064-2981)The attached picture shows the formula I created.I need to be able to fill this in for 12 months and I know that IF formulas can only be used up to 7 times in a formula.I'm open to all suggestions including VBA possibilities.
​

 

[mumps]

Have you considered using a macro? If so, please attach a copy of your file. It is difficult to work with a picture.

 

[hiker95]

snm6996,

Welcome to the MrExcel forum.

You are posting a picture/image of your worksheet.

Anyone trying to assist you would have to manually enter the information, and, that makes no sense.


See the following link for ways to display your actual raw data worksheet, and, what the results should look like:

https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


It is always easier to help and test possible solutions if we could work with your actual file.

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.