# help with formula...weighted average

This is a discussion on help with formula...weighted average within the Excel Questions forums, part of the Question Forums category; Is there anyway to make this formula more efficient? I'm working with weighted averages and this formula is in several ...

1. 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.

2. 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}

3. 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....

******** ******************** ************************************************************************>
 Microsoft Excel - Dave'sQOS.xls ___Running: xl2002 XP : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

Z
AA
AB
AC
81
0*
0*
0
82
**
**
*
83
**
**
*
84
**
**
*
85
0*
0*
0
 2002 QOS Data *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Is it still possible to reduce the formula??

Thanks again for all your help!!

4. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•