SUM formula with dynamic divisor

[leopardhawk]

Hi friends, I'm not sure if this is possible or not. Need a formula to SUM a column that could have five entries or it could have 500 entries. I also need it to divide the total by the exact number of entries and it needs to be dynamic, ie. someone enters a new amount in the column and the formula will not only add that amount to the total but will add '1' to the divisor at the same time.

Example:

=IF(G12:G20=0,"",SUM(G12:G20)/8)

So if there is $12.00 in cell G12, $13.00 in G13, $14.00 in G14 and so on up to G19 the formula as it is would return $15.50.

The part I'm not sure about is the dynamic application of an increasing divisor. If the user enters $20.00 in cell G20, the divisor would automatically increase by 1 and now divide the total by 9 and return $16.00.

Any help or advice is much appreciated.

Cheers!

 

[alansidman]

Why not just use the Average Function in Excel.

 

[leopardhawk]

How would that work? I only want the average of the cells in column G that actually have data in them. If there are 50 cells with data, then the formula would divide by 50. I there are 100 cells with data in them then the formula would divide by 100. The formula needs to be dynamic and adjust the divisor as users add additional cells in column G.

 

[shg]

=average(g:g)

 

[leopardhawk]

That does not work either. It is including cells that are empty in the 'average'. I need the formula to average only those cells in column G that actually have data in them.

Assume that the example below is what I am trying to solve. I need the formula to divide by 6 because there are only six cells with data. If I use 'AVERAGE' as suggested, then how do I exclude the cells that do NOT have data in them from the calculation?

Excel 2016 (Windows) 32 bit
	G	H	I
12	$ 12.00		$14.50
13	$ 13.00
14	$ 14.00
15	$ 15.00
16	$ 16.00
17	$ 17.00
18	$ -
19	$ -
20	$ -
payroll

Cell Formulas
Range	Formula
I12	=IF(G12:G20=0,"",SUM(G12:G20)/6)

 

[shg]

18/19/20 are not empty; they contain 0, which is a number.

If you want to exclude zeros,

=AVERAGEIF(G:G, "<>0")

 

[leopardhawk]

Perfect. Thank you so much!

 

[shg]

You're welcome.

 

[leopardhawk]

shg, one follow-up question. Do you know why my 3:30 PM post above is showing all that hexadecimal stuff when in the PREVIEW, it looks perfect. I am using 'Forum Tools' in Excel 2016 (recommended by this forum) to add to my post. Sometimes it work and other times I get all that gibberish. Frustrating at best.

 

[shg]

Dunno, don't use it.