Averaging percentages

Post Reply
dmggilbert@gmail.com
Posts: 5
Joined: Sun Jun 28, 2020 4:03 am

Averaging percentages

Post by dmggilbert@gmail.com » Mon Jan 11, 2021 5:08 pm

Sorry if this sounds like a stupid question to most of you but I have searched the internet and have not been able to find the solution to my question not to mention I am new to creating formulas and trying to learn.

I have a column of percentages and each row (a different day) are calculated daily. I want to be able to average the percentages of all the days as they are entered. As of now I have an "average" equation set at the bottom of each column but because the next day does not have a value entered yet the cell with the average equation shows "#DIV/0!". I want the equation to recognize only cells that have a numerical value entered.

I also want to add a bonus based off of these percentages which change based on the percentage. See the bonus section of the workbook for details.

I have attached the file I am working on for anyone interested in offering me some assistance. See Sheet "2021 Daily Flag" of the workbook for all the relevant information I am referring to.

Thank you in advance for your assistance.
Attachments
Tucson Flag Sheet.xlsx
(60.5 KiB) Downloaded 15 times

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1415
Joined: Wed Mar 11, 2020 5:31 pm

Re: Averaging percentages

Post by SuperTech » Tue Jan 19, 2021 4:51 am

The file you shared is password protected.

dmggilbert@gmail.com
Posts: 5
Joined: Sun Jun 28, 2020 4:03 am

Re: Averaging percentages

Post by dmggilbert@gmail.com » Fri Jan 22, 2021 1:58 pm

Oh crap, sorry. Here is the file unprotected.
Attachments
Tucson Flag Sample.xlsx
(18.76 KiB) Downloaded 13 times

miguel-c
SoftMaker Team
SoftMaker Team
Posts: 972
Joined: Wed Jun 05, 2019 12:02 pm

Re: Averaging percentages

Post by miguel-c » Fri Jan 22, 2021 2:34 pm

To avoid DIV/0 you should use IF statements.
You can go to E10 and type: =IF(D10=0;"";SUM(C10/D10))
Then do the same to similar cells on column E
And filnally on E20 you should =IF(SUM(E10:E18)=0;"";AVERAGE(E10:E19))
See attached.
Attachments
Tucson Flag Sample - edited by SoftMaker.xlsx
(18.87 KiB) Downloaded 16 times

dmggilbert@gmail.com
Posts: 5
Joined: Sun Jun 28, 2020 4:03 am

Re: Averaging percentages

Post by dmggilbert@gmail.com » Sat Jan 23, 2021 2:53 am

Thank you miguel-c. I guess I have a lot to learn about formulas.

Post Reply

Return to “PlanMaker 2021 for Windows”