# stuck... what to do now?

#### Fredsone1

##### New Member
Hi.
I am creating a table which has 4 data points added on the second day of every month.
I have a cumulative total at the bottom of each data point column (Data Point 1-4) with each months data point row having a daily average column for each data point and cumulative months average columns to the right.
The problem(s) I have are as follows;
1) All data points are returned to zero at the end of each calendar year. The problem I have is that I want to maintain the month by month individual Data Point 1-4 daily averages and each months cumulative total .
2) Because the meters are zeroed annually (or may be replaced) I am not sure how to make the data points total row account for the fact that the numbers may not be sequential.
data points 1-4 on 02.01.2014 should all be 00,00 the entered value of 23 was me trying to make a formula work .
At the bottom of this page, I have included my formula to show how I got the results I currently have.

 Data point Data Point Data Point Data Point 1 2 3 4 Data Point 1 Data Point 2 Data Point 3 Data Point 4 Data point 1-4 02.09.2013 0,01 191,00 154,00 145,00 daily average daily average daily average daily average months total 02.10.2013 0,02 191,00 154,00 146,00 0,00 0,00 0,00 0,03 1,01 02.11.2013 2,00 194,00 156,00 151,00 0,07 0,10 0,07 0,17 11,98 02.12.2013 18,00 194,00 157,00 155,00 0,52 0,00 0,03 0,13 21,00 02.01.2013 23,00 0,16 0,00 0,00 0,00 02.02.2014 0,00 0,00 0,00 0,00 02.03.2014 0,00 0,00 0,00 0,00 02.04.2014 0,00 0,00 0,00 0,00 02.05.2014 0,00 0,00 0,00 0,00 02.06.2014 0,00 0,00 0,00 0,00 02.07.2014 0,00 0,00 0,00 0,00 02.08.2014 0,00 0,00 0,00 0,00 02.09.2014 0,00 0,00 0,00 0,00 02.10.2014 0,00 0,00 0,00 0,00 02.11.2014 0,00 0,00 0,00 0,00 02.12.2014 0,00 0,00 0,00 0,00 02.01.2014 0,00 0,00 0,00 0,00 02.02.2015 0,00 0,00 0,00 0,00 02.03.2015 0,00 0,00 0,00 0,00 Total 22,99 3,00 3,00 10,00 0,19 0,10 0,05 0,11 38,99

<tbody>
</tbody>

 Worksheet Formulas Cell Formula F7 =IF(B7="",0,(B7-B6))/31 G7 =IF(C7="",0,(C7-C6))/31 H7 =IF(D7="",0,(D7-D6))/31 I7 =IF(E7="",0,(E7-E6))/31 J7 =IF(SUM(B7:E7)=0,"",(SUM(B7:E7)-SUM(B6:E6))) F8 =IF(B8="",0,(B8-B7))/30 G8 =IF(C8="",0,(C8-C7))/30 H8 =IF(D8="",0,(D8-D7))/30 I8 =IF(E8="",0,(E8-E7))/30 J8 =IF(SUM(B8:E8)=0,"",(SUM(B8:E8)-SUM(B7:E7))) F9 =IF(B9="",0,(B9-B8))/31 G9 =IF(C9="",0,(C9-C8))/31 H9 =IF(D9="",0,(D9-D8))/31 I9 =IF(E9="",0,(E9-E8))/31 J9 =IF(SUM(B9:E9)=0,"",IF(SUM(B9:E9) F10 =IF(B10="",0,(B10-B9))/31 G10 =IF(C10="",0,(C10-C9))/31 H10 =IF(D10="",0,(D10-D9))/31 I10 =IF(E10="",0,(E10-E9))/31 J10 =IF(SUM(B10:E10)=0,"",IF(SUM(B10:E10) F11 =IF(B11="",0,(B11-B10))/28 G11 =IF(C11="",0,(C11-C10))/28 H11 =IF(D11="",0,(D11-D10))/28 I11 =IF(E11="",0,(E11-E10))/28 J11 =IF(SUM(B11:E11)=0,"",IF(SUM(B11:E11) F12 =IF(B12="",0,(B12-B11))/31 G12 =IF(C12="",0,(C12-C11))/31 H12 =IF(D12="",0,(D12-D11))/31 I12 =IF(E12="",0,(E12-E11))/31 J12 =IF(SUM(B12:E12)=0,"",(SUM(B12:E12)-SUM(B11:E11))) F13 =IF(B13="",0,(B13-B12))/30 G13 =IF(C13="",0,(C13-C12))/30 H13 =IF(D13="",0,(D13-D12))/30 I13 =IF(E13="",0,(E13-E12))/30 J13 =IF(SUM(B13:E13)=0,"",(SUM(B13:E13)-SUM(B12:E12))) F14 =IF(B14="",0,(B14-B13))/31 G14 =IF(C14="",0,(C14-C13))/31 H14 =IF(D14="",0,(D14-D13))/31 I14 =IF(E14="",0,(E14-E13))/31 J14 =IF(SUM(B14:E14)=0,"",(SUM(B14:E14)-SUM(B13:E13))) F15 =IF(B15="",0,(B15-B14))/30 G15 =IF(C15="",0,(C15-C14))/30 H15 =IF(D15="",0,(D15-D14))/30 I15 =IF(E15="",0,(E15-E14))/30 J15 =IF(SUM(B15:E15)=0,"",(SUM(B15:E15)-SUM(B14:E14))) F16 =IF(B16="",0,(B16-B15))/31 G16 =IF(C16="",0,(C16-C15))/31 H16 =IF(D16="",0,(D16-D15))/31 I16 =IF(E16="",0,(E16-E15))/31 J16 =IF(SUM(B16:E16)=0,"",(SUM(B16:E16)-SUM(B15:E15))) F17 =IF(B17="",0,(B17-B16))/31 G17 =IF(C17="",0,(C17-C16))/31 H17 =IF(D17="",0,(D17-D16))/31 I17 =IF(E17="",0,(E17-E16))/31 J17 =IF(SUM(B17:E17)=0,"",(SUM(B17:E17)-SUM(B16:E16))) F18 =IF(B18="",0,(B18-B17))/30 G18 =IF(C18="",0,(C18-C17))/30 H18 =IF(D18="",0,(D18-D17))/30 I18 =IF(E18="",0,(E18-E17))/30 J18 =IF(SUM(B18:E18)=0,"",(SUM(B18:E18)-SUM(B17:E17))) F19 =IF(B19="",0,(B19-B18))/31 G19 =IF(C19="",0,(C19-C18))/31 H19 =IF(D19="",0,(D19-D18))/31 I19 =IF(E19="",0,(E19-E18))/31 J19 =IF(SUM(B19:E19)=0,"",(SUM(B19:E19)-SUM(B18:E18))) F20 =IF(B20="",0,(B20-B19))/30 G20 =IF(C20="",0,(C20-C19))/30 H20 =IF(D20="",0,(D20-D19))/30 I20 =IF(E20="",0,(E20-E19))/30 J20 =IF(SUM(B20:E20)=0,"",(SUM(B20:E20)-SUM(B19:E19))) F21 =IF(B21="",0,(B21-B20))/31 G21 =IF(C21="",0,(C21-C20))/31 H21 =IF(D21="",0,(D21-D20))/31 I21 =IF(E21="",0,(E21-E20))/31 J21 =IF(SUM(B21:E21)=0,"",(SUM(B21:E21)-SUM(B20:E20))) F22 =IF(B22="",0,(B22-B21))/31 G22 =IF(C22="",0,(C22-C21))/31 H22 =IF(D22="",0,(D22-D21))/31 I22 =IF(E22="",0,(E22-E21))/31 J22 =IF(SUM(B22:E22)=0,"",(SUM(B22:E22)-SUM(B21:E21))) F23 =IF(B23="",0,(B23-B22))/28 G23 =IF(C23="",0,(C23-C22))/28 H23 =IF(D23="",0,(D23-D22))/28 I23 =IF(E23="",0,(E23-E22))/28 J23 =IF(SUM(B23:E23)=0,"",(SUM(B23:E23)-SUM(B22:E22))) F24 =IF(B24="",0,(B24-B23))/31 G24 =IF(C24="",0,(C24-C23))/31 H24 =IF(D24="",0,(D24-D23))/31 I24 =IF(E24="",0,(E24-E23))/31 J24 =IF(SUM(B24:E24)=0,"",(SUM(B24:E24)-SUM(B23:E23))) B25 =SUMPRODUCT(--(B7:B24<>""),B7:B24-B6:B23) C25 =SUMPRODUCT(--(C7:C24<>""),C7:C24-C6:C23) D25 =SUMPRODUCT(--(D7:D24<>""),D7:D24-D6:D23) E25 =SUMPRODUCT(--(E7:E24<>""),E7:E24-E6:E23) F25 0,1 G25 0,04 H25 0,12 I25 29,995 J25 0

<tbody>
</tbody>

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Replies
1
Views
143
Replies
0
Views
259
Replies
2
Views
199
Replies
5
Views
105
Replies
3
Views
69

1,196,355
Messages
6,014,756
Members
441,848
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back