Apache OpenOffice Community Forum

I have a spreadsheet that sums & averages values for my electric production and usage by month. However since this is a leap year all the formulas after January and the yearly totals are off by a day. I would like to change the monthly formulas so that they sum all the months correctly, whether February has 28 or 29 days. I am not sure how to change these formulas so they add up all the days regardless of whether it is a leap year. I could just fix the date each year by changing the formula in the date column leaving a blank where February 29th would be, but I would like the min, max, avg, and sum formulas to adjust automatically for leap years. I tried to attached the spreadsheet with the formulas, but it is too large. So I copied just the page in question. The formatting is off, but that is not important. ANY help you can provide would be greatly appreciated. Thank You in advance.

I didn't actually see SUM or AVERAGE in the sheet you uploaded, but since you are working with a specific date range, you could use the SUMIF or SUMIFS functions, likewise with AVERAGEIF and AVERAGEIFS

I suggest you do not use formulas at all but rather Pivot Tables. That function is found under Data -> Pivot Table. In the attached file I added a Month column, modified your column headers, and put pivot tables for the max, min and annual results starting in column S. You can right click on a table and select Edit layout to see how I set it up. Be sure to look in the More section for some of the options. You can double click on, for example, the button that says Result - Daily Prod to see that I selected Sum, Average and Max for the Daily Production.

You will probably want to modify the layout some but I think in the long term it is a more convenient way to display the results. The software handles the calculations and you can easily filter by month.

RusselB wrote:I didn't actually see SUM or AVERAGE in the sheet you uploaded, but since you are working with a specific date range, you could use the SUMIF or SUMIFS functions, likewise with AVERAGEIF and AVERAGEIFS

They were in column P. SUMIF and AVERAGEIF will work, but that won't solve my MAX & MIN range problem. However, the Pivot Table solution i received works, I just have to learn how to use it. Thanks again.

Thank you FJCC. I think the pivot table is the perfect solution.