Getting percentages (in Excel 2010) while having dynamic number of values?

Hello folks,

I might kick myself when I see a proper solution but here goes....

I need to determine percentage in rows where the total number of cells being added will change.

Normally, I would use the old formula: =SUM(D29:D43)/15 - where I add up the values (in this case either 1 or 0 value) and divide by the sum of all the cells (15). However, this is inconvenient when the value of 15 may change.

If I use the formula =AVERAGE(D4:D13) the problem with the changing number of cells is solved, however, the value is slightly different (re: only the average value).

Can someone please provide a solution to this problem?

S ConnellyTechnical WriterAsked:
Who is Participating?
Saurabh Singh TeotiaCommented:

You can use the following...


Lets say you want to get a average of all the 1 values only then use this...


and lets say sum all the values but get average only keeping in mind 0 values then..


S ConnellyTechnical WriterAuthor Commented:
Thank you...

I could not make the bottom formula work but the rest did..

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.