help with formula...weighted average

[Davers]

Is there anyway to make this formula more efficient? I'm working with weighted averages and this formula is in several hundred cells.....

(((SUM(F80:F81)*F82)+(SUM(H80:H81)*H82)+(SUM(J80:J81)*J82)+(SUM(L80:L81)*L82)+(SUM(N80:N81)*N82)+(SUM(P80:P81)*P82)+SUM(R80:R81)*R82)+(SUM(T80:T81)*T82)+(SUM(V80:V81)*V82)+(SUM(X80:X81)*X82)+(SUM(Z80:Z81)+Z82)+(SUM(AB80:AB81)*AB82))/SUM(F80:AB81)

Thanks,

Dave M.

 

[tusharm]

Since you are using SUM(F80:AB81) in the denominator, I assume that the alternate columns that are missing from the numerator are actually empty.

You might want to add an additional row, say 79 that contains the sum of 80:81. In F79, enter =SUM(F80:F81). Copy this over to AB79. Now, use =SUMPRODUCT(F79:AB79,F82:AB82)/SUM(F79:AB79).

In addition, if the 'hundreds' of cells all contain the same formula, leave that formula in only one cell. In all the others, use ={cell-with-real-formula}

 

[Davers]

Thanks for responding, I will try referencing the cell, however, the skipped columns have formula's in them....here is a small section of my spreadsheet....

Dave'sQOS.xls
	Z	AA	AB	AC	AD
81	0		0		0
82
83
84
85	0		0		0
2002 QOS Data

Is it still possible to reduce the formula??

Thanks again for all your help!!

 

[tusharm]

Repeating myself, the original formula contains SUM(F80:AB81) in the denominator.

If the alternative columns contain formulas that don't always yield zero, the original formula doesn't compute the weighted average.

The other suggestion about replacing each of the hundreds of cells with a simple formula pointing to the one cell that contains the real formula will simplify the worksheet dramatically. This remains true irrespective of the real formula that is ultimately used.