Results 1 to 4 of 4

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. #1
    Board Regular Davers's Avatar
    Join Date
    Sep 2002
    Location
    Michigan
    Posts
    1,163

    Default

    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. #2
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,747

    Default

    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. #3
    Board Regular Davers's Avatar
    Join Date
    Sep 2002
    Location
    Michigan
    Posts
    1,163

    Default

    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
    AD
    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. #4
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,747

    Default

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com