Insert calculations grouped by a key value

**smokebreak** · 10-25-2010, 04:13 PM

I need to insert several calculations into a sheet (whose length will vary each time it is refreshed). For each Part Number (column B) I need to insert (in columns I, J, ....)

Mean USD (excluding zeroes)
Median USD (excluding zeroes)
Minimum USD (excluding zeroes)
and perhaps some weighted values?...

How do I use VBA to identify all the Part Numbers in Column B that are the same and then produce the same result in columns I, J, ... for each part number?

Example:
Part Number 7654 (rows 8-11)
Mean = 289.5
Median = 289.5
Minimum = 123
Weighted by usage (sum(usage/total use)*cost) = 372.75

The data have been anonymized.

**TMShucks** · 10-25-2010, 06:07 PM

Not sure where you want to put this formula. I'm guessing you want it in column I.

So, cell I9, for example:

=(H9/SUMIF(B:B,B9,H:H))*G9

Copy up and down as necessary

Regards

**smokebreak** · 10-25-2010, 06:14 PM

That formula works great if I only had one part number. However my original query was to calculate each of the statistics for each given part number. (The information in I8:I11 was for informational purposes showing how the components of a weighted average should be calculated.)

I know just enough about VBA to know that the successful code would test each of the values in column B against the other values in that column, then for each unique set of values, calculate the statistic.

Am I still not being clear?

**TMShucks** · 10-25-2010, 06:17 PM

Did you copy the formula up and down the column?

Regards

**smokebreak** · 10-25-2010, 06:21 PM

Yes but it only returns the components of the weighted average. The actual weighted average for each line would be the sum of those components. Even if that were what I was looking for, it still doesn't answer the question of how to calculate the median or the other statistics that I need.

The only statistic I've successfully used a formula for is the simple average where I have used

=IFERROR(AVERAGEIFS(G:G,G:G,"<>0",B:B,B2),0)

What I am looking for here is code to find the full range of a given part number, and perform a calculation on values associated with that range to return the required statistics...

**smokebreak** · 10-26-2010, 10:49 AM

bump for vba help please!

**StephenR** · 10-26-2010, 11:11 AM

You don't need VBA - see formulae in attachment. Have added in I-M. M is just a helper for column L and could be hidden. The first three are array formulae so need to be confirmed with Ctrl+Shift+Enter.

All in all probably the not most efficient way as you are duplicating your results - you could generate a list of unique part numbers using Advanced Filter.

**smokebreak** · 10-26-2010, 01:32 PM

OK that looks like it will work. Thank you StephenR

The final piece to this puzzle is: my actual data set can/will be much longer and will have varying lengths when it is refreshed. I am completely unfamiliar with array formulae - Can array formulae be copied down or altered (perhaps using VBA?) to fill down to the last row?

Thanks!

**DonkeyOte** · 10-26-2010, 01:34 PM

If you're using XL2007 why not convert your range (inclusive of formulae columns) to a Table (see Insert Tab -> Table)

edit: and modify formulae accordingly to utilise Table references of course

**DonkeyOte** · 10-26-2010, 01:51 PM

I figured it would be easier to demonstrate the Table by means of sample file:

**StephenR** · 10-26-2010, 03:55 PM

Here is some automation, but if you follow DonkeyOte's approach you may not need this.

Sub x()

Dim rItem As Range, rUSD As Range, rUsage As Range, rHelp As Range

Range("I1:L1").Value = Array("Mean", "Median", "Minimum", "Weighted")

Set rItem = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rUSD = rItem.Offset(, 5)
Set rUsage = rItem.Offset(, 6)
Set rHelp = rItem.Offset(, 11)

rItem.Offset(, 7).Resize(1).FormulaArray = "=AVERAGE(IF(" & rItem.Address & "=B2,IF(" & rUSD.Address & ">0," & rUSD.Address & ")))"
rItem.Offset(, 8).Resize(1).FormulaArray = "=MEDIAN(IF(" & rItem.Address & "=B2,IF(" & rUSD.Address & ">0," & rUSD.Address & ")))"
rItem.Offset(, 9).Resize(1).FormulaArray = "=MIN(IF(" & rItem.Address & "=B2,IF(" & rUSD.Address & ">0," & rUSD.Address & ")))"
rItem.Offset(, 11).Resize(1).FormulaArray = "=G2*(H2/SUMIF(" & rItem.Address & ",B2," & rUsage.Address & "))"
rItem.Offset(, 10).Resize(1).Formula = "=SUMIF(" & rItem.Address & ",B2," & rHelp.Address & ")"

Range("I2:M2").AutoFill Range("I2:M2").Resize(rItem.Count)

'Two lines below convert formulae to values and delete helper column
'Range("I2", Range("I2").End(xlDown)).Resize(, 5).Value = Range("I2", Range("I2").End(xlDown)).Resize(, 5).Value
'Columns(13).Clear

End Sub

Btw, DonkeyOte (or anyone else) when inserting an array formula in VBA is there a way to make it interpret a single cell reference relatively rather than absolutely?