[Solved] Leap year problems with sum and averages

Discuss the spreadsheet application

[Solved] Leap year problems with sum and averages

Postby gmccollam » Fri Jan 31, 2020 2:55 am

Sample.ods
The formulas I need to adjust are in 2020, February, March, etc. I left out April thru December as I can do those after I see the others. January will always be correct, but I will change those as well to be consistent.
(23.68 KiB) Downloaded 17 times


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.
Last edited by robleyd on Fri Jan 31, 2020 6:42 am, edited 3 times in total.
Reason: Add green tick
OpenOffice 4.1.1 on Windows 10
gmccollam
 
Posts: 10
Joined: Thu Aug 27, 2015 5:45 pm

Re: Leap year problems with sum and averages

Postby RusselB » Fri Jan 31, 2020 3:29 am

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
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6097
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Leap year problems with sum and averages

Postby FJCC » Fri Jan 31, 2020 3:57 am

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.
Attachments
Sample_fjcc.ods
(26.96 KiB) Downloaded 22 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7737
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Leap year problems with sum and averages

Postby gmccollam » Fri Jan 31, 2020 4:32 am

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.
OpenOffice 4.1.1 on Windows 10
gmccollam
 
Posts: 10
Joined: Thu Aug 27, 2015 5:45 pm

Re: [Solved]Leap year problems with sum and averages

Postby gmccollam » Fri Jan 31, 2020 4:35 am

Thank you FJCC. I think the pivot table is the perfect solution.
OpenOffice 4.1.1 on Windows 10
gmccollam
 
Posts: 10
Joined: Thu Aug 27, 2015 5:45 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests