ok. I've amended your workbook slightly.
1. I have made the dates dependent on each other (column A). You didn't take leap year into account and you had months that were too long (no such thing as 31-Apr-2016).
2. The dates that don't exist (31-Apr etc) should behidden by hiding the rows (see eg Feb). Normally I would delete the row entirely, but you need to understand where they are. If you delete them (which you can) most formulas will autocorrect to take into account that a row no longer exists.
3. Cell G41 can either be sum(F10:F40) or max(G10:G40). I have opted not to make it = G40, because of 2. above (hidden sums will screw with you).
4. For the "Rolling 30 Day Sum" I have introduced two extra sheets. The "Helpersheet" takes all the dates along with the flight time of the day and sums up the previous 29 days. However for January that is not possible without the "Last Dec" sheet so I have introduced that as well.
5. Next year you need to change cell A10 in Jan to 1-1-2017. That will automatically fix the Month cell that you have on each sheet and it correct the dates on the "Helpersheet".
6. The "Helpersheet" and the "Last Dec" should be hidden.
Additionally
7. In the Example sheet I have changed the format of column C, D & E to HH:MM. If you use numbers like you did you can enter a value of 09:75 which doesnt make sense. The hours have to be entered as 09:00. Entering 0900 will not work. I have not implemented this in the other sheets.
8. In the Example sheet I have deleted rows 8 and 9 where you had entered text. It is a bad idea to enter text over multiple rows when the text ought to be in a single row. What you should do instead is use text wrapping and centering (horizontal and vertical). If you want to force a line change use alt+enter. I have not implemented this in the other sheets.
However
9. Is column F necessary? Isn't it the same as column E?
Play around with the sheet and see if it takes you in the right direction. There is plenty that can be optimised.
/Møller
|