calculating a weighted average from referencing formulas in other cells

  • Is there a way to calculate a weighted average referencing formulas in other cells? For example, in Sheet 1, cells A1:A10 all have the number 1. Column B is empty. Cell C1 has a formula stating 100% of the number 1's are in column A. Sheet 2 has the number 1 in cells A1 & B2 so the value of C1 is 50%. In Sheet 3, how can I compute the averages of the data of cells C1 from Sheets 1 & 2 without averaging the answer? Averaging the data would say 92% of the 1's are in the A columns, but averaging the values of C1 would say 75%.


    Note that more data in columns A & B is routinely added & the formula references in C1 are changed as different ranges of data is to be reviewed. I'd like to be able to change my formula references in C1 on Sheets 1 & 2 & have the changes automatically applied in Sheet 3 if possible.

  • Re: calculating a weighted average from referencing formulas in other cells


    This works. You can modify the ranges as needed.


    =SUM(Sheet1!C1*COUNTIF(Sheet1!A1:B10,1)/SUM(COUNTIF(Sheet1!A1:B10,1),COUNTIF(Sheet2!A1:B10,1)),Sheet2!C1*COUNTIF(Sheet2!A1:B10,1)/SUM(COUNTIF(Sheet1!A1:B10,1),COUNTIF(Sheet2!A1:B10,1)))

    Regards,


    WidgetWonka
    Puuuureeee Imagination

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!