Getting AVG using Indirect Intersecting Sum Formula

[Jtucker10278]

I'm trying to get an average of what Tyde did divided by the hours he worked

(B2:F2) = 48
(B3:F3) = 40
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jan-1[/TD]
[TD]Jan-2[/TD]
[TD]Jan-3[/TD]
[TD]Jan-4[/TD]
[TD]Jan-5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tyde[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Hours[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

The formula i want to use is (ill explain way)

=sum(B2:F2)/(B3:F3)

this formula gives me an answer of 6

and if i use the formula =sum((B2:F2)/(B3:F3)) I get 1.375 for an answer ??????????

I would love to understand that, but my real problem is the formula

=SUM(INDIRECT(J$21) INDIRECT($I22)/=sum(INDIRECT(J$21) hrswkd))

So if you consider that

columns B:F are the named range "Week1"

Row 2:2 is Named Range "tyde"

Row 3:3 is Named Range "hrswkd"

My results table looks like

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TYDE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Adam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In cell B2 I am trying to get what Tyde built in week1

=sum(week1 tyde) this returns the answer of 48

i can also write the formula using the indirect function

=sum(Indirect(B1) Indirect(A2)) and this returns an answer of 48

the next step would be to get what tyde built in week1 divided by the house tyde work in week1

=sum(Indirect(B1) Indirect(A2)) / sum(indirect(B1) hrswkd))

Any help??

 

[NiMip]

The formula i want to use is (ill explain way)

=sum(B2:F2)/(B3:F3)

this formula gives me an answer of 6

and if i use the formula =sum((B2:F2)/(B3:F3)) I get 1.375 for an answer ??????????

The first one is actually only doing sum(B2:F2)/B3 = 48/8 = 6. The range on the denominator hasn't been summed or had anything done to it, so it will just be treated as the first value in the range.

For the second one, you're getting 1.375 because it's only calculating sum((B2)/(B3)) = sum(11/8) = 1.375. Again, it's treating the ranges as their first values, as the sum() function is around the whole thing. Hitting CONTROL+SHIFT+ENTER will treat each range as an array, and do each division before summing the results, but I'm not sure that's what you want either? Mathematically speaking, that's not an average, it's just adding up fractions.

If you want the average, you should use =SUM(B2:F2)/SUM(B3:F3)

=sum(Indirect(B1) Indirect(A2)) / sum(indirect(B1) hrswkd))

Any help??

This is actually correct and works perfectly!

EDIT: Just thinking about what you're after... maybe you actually want =AVERAGE(B2:F2/B3:F3) ? Hitting CONTROL+SHIFT+ENTER, this would be the average of the individual daily effort/hour. It really depends if you want an average weekly effort/hour, or an average daily effort/hour for each week.

 

[Jtucker10278]

Sorry for the delay in responding.

Thank you very much for explaining the first few formulas

I finally got my last formula to work. It was the last closing parenthesise ) that was gumming it up.

Thanks again for the help