Trying to get a yearly total using monthly subtracted values.

Hello All,

I am taking printer readings each month and would like to display a YEAR END total that displays a 'running total' in a cell. IE: Jun = 1 page printed, Jul = 3 pages printed --> Year End Total = 2 Pages.

I am unable to get a Year End Total to display in the proper cell unless Jan - Dec are filled with numeric values other than zero.

I am using the following formula: =(F5-E5)+(G5-F5)+(H5-G5)+(I5-H5)+(J5-I5)+(K5-J5)+(L5-K5)+(M5-L5)+(N5-M5)+(O5-N5)+(P5-O5)+('2014'!E5-'2013'!P5)

(To get a value for my December 2013 reading, I am subtracting the Dec 2013 value from the corresponding Jan cell on page 2014.)

My problem is that when I put a value of '1' in cell J5 and a value of 3 in cell K5, my YEARLY TOTAL cell, Q5, displays the value of zero instead of '2'.

I hope I have explained this problem in terms that can be understood.

Any help is appreciated as I am a novice when it comes to using Excel for advanced functions.

Thank you,

RJeffDay

Here is my workbook.

To solve this, you should break down your formula to look at all the parts. When you look each pairing (ie: F5-E5 for each of the months, you get:

0+0+0+0+1+2-3+0+0+0+0 which equals 0. So, this is problem one.

A couple of questions. Why is the total for the year 2? Did the machine not start at 0?

If you are looking for the yearly total, wouldn't it be the largest number subtracted from the smallest number across the whole year? So, in the current example for model DP6020G, the smallest number is 0 and the largest is 3: 3 - 0 = 3. This can be written as this: MAX(E55) - MIN(E55). Also, Removeing the zeros from the cells makes the formula calculate as 3 - 1 = 2.

Also, why are you bringing in 2014's numbers to a 2013 report? I am unsure the value or reasoning.

So, if you can answers those, we can move forward.

Sorry, I am stumbling though your spreadsheet a bit. For the Pages Printed Each Month section, you need a little logic. IE: if this month's meter reading is 0 then do not calculate. So, cell K21 would be: =IF(K5=0, 0, K5-J5). Which would equal 2.