Weighted Average Formula Multiple colums Ignore 0

[wherdzik]

Attached is a copy of survey data I am putting together and it requires the use of weighted averages. Weighted average is based off the count of participants and the type of data. Type of data in here is what we refer to as TCC, wRVU per TCC, wRVU and Base Pay.

The formula is required in columns CG:CV. There is a formula in there already but it fails simply because some of the criteria have the count of participats but they have no related values. So follwing an example, please go to cell CR4. In CR4 the formula does not calculate the weighted average properly because there is not data in column BK but there is data in BG. In this case I want the formula not to perform the weighted average on sections that do not have data. Currently CR4 is displaying $64.2 and it is reducing that value because of thsoe 0 values in BK. What I want the result to be is actually $80.70.

Can someone help me create the logic around this. I know there are some really good formula people in here.

Thank for looking and let me know if you have additonal questions.

[Bernie Deitrick]

in CR4, change your formula to

=IFERROR((($S4*W4)+($AM4*AQ4)+($BG4*BK4))/(IF(W4=0,0,$S4)+IF(AQ4=0,0,$AM4)+IF(BK4=0,0,$BG4)),0)

If you had a different data structure, you could use SUMPRODUCT(...)/SUMIFS(...) but that would require a lot of changes.