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?

Thanks,
Rod
LVL 4
S ConnellyTechnical WriterAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:

You can use the following...

=SUM(D29:D43)/COUNT(D29:D43)

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

=SUM(D29:D43)/COUNTif(D29:D43,1)

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

=SUM(D29:D43)/COUNTif(D29:D43,0)

Saurabh...
0
 
S ConnellyTechnical WriterAuthor Commented:
Thank you...

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

:-)
0
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.