Need weighted average, maybe ? 1

[renigar]

I'm using Excel 2007. I have several budget index spreadsheets, 1 for each department (I have attached a small 30 kb sample workbook in xls format). Each column is a budget program in the department and each row represents an account number. On the original sheet all values are linked to individual account sheets in other workbooks. What I want to do is come up with a percent remaining in the totals row and column without having to reference the beginning balances from the individual account sheets. I was told I should use a weighted average formula, which I believe I have in cells C16 and G14 of my sample. I would use the same formula in G15 and G16 although I have not put it in yet. The formula I have seems to work except when any account goes to zero. Then the divide by zero error kicks in and throws it off. If any account goes to zero the percent remaining on the total row will show zero. Any tips on how to acheive my goal would be greatly appreciated.

The following formula is currently in cell C16.
=IFERROR(SUM(B16/((B2/C2)+(B3/C3)+(B4/C4)+(B6/C6)+(B7/C7)+(B8/C8)+(B9/C9)+(B10/C10)+(B11/C11)+(B12/C12)+(B13/C13)+(B14/C14))),0)

 

[zelgar]

Try changing the formula to this:

=IF(MIN(C2:C14)>0, SUM(B16/((B2/C2)+(B3/C3)+(B4/C4)+(B6/C6)+(B7/C7)+(B8/C8)+(B9/C9)+(B10/C10)+(B11/C11)+(B12/C12)+(B13/C13)+(B14/C14))),0)

 

[renigar]

Thanks but, that formula still produces the divide by zero issue.

 

[SkipVought]

EACH of your b/c calculations need an IFERROR()!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renigar]

Thanks Skip, it looks like that did it.