# Calculating an average - but not as you know it....

#### dan78h

##### New Member
Hi folks,

Thanks for taking the time to read this. I think I have a beauty and need some guidance.

So I have a report that has data similar to that below.

Month Serial Number Part Cost Model
Nov-11 12345 \$1.25 TVC1
Nov-11 12345 \$0.89 TVC1
Nov-11 12345 \$5.97 TVC1
Dec-11 35749 \$1.24 TVC2
Dec-11 35749 \$8.14 TVC2
Jan-12 54321 \$7.84 TVC1
Jan-12 54321 \$2.35 TVC1
Jan-12 67890 \$8.97 TVC2
Jan-12 67890 \$4.32 TVC2

What I need to do is calculate the average of each model by month. At the moment as there is possibly more than one part changed under the same serial number this is reducing the average and not showing the true cost.

For example - If I want the average cost for the TVC2 in Jan-12 it should be \$13.29, however as excel takes the serial number (even though the same) as a unique number and it calculates the average as \$6.64.

I have to run this report on about 150K lines of data and there could be an instance of record not having a value in the part cost if there were no parts consumed on a service record.

I hope this can be nutted out.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
is there not a little confusion. 13.29 is NOT average but sum. average 6.64 is correct because there are two such items and 13.29/2=6.64
pleas clarify.

anyhow you can use either
average(if....
function or sumproduct function.

No confusion...

If you can see the serial number is the same and I am working out the cost of a repair performed.

Excel is taking the average over two part numbers on the same TVC2.

Dan
Maybe you could post how the calculation is done....'cause I'm afraid, I agree with Venkat.
the average cost for the TVC2 in Jan-12
using the provided data
Jan-12 67890 \$8.97 TVC2
Jan-12 67890 \$4.32 TVC2
will be \$13.29 / 2 = \$6.65 ??

I am trying to calculate the cost of a repair. The data shows that there a two parts used on a single repair. Why would we use an average when there is only 1 part for 1 repair??

I am trying to calculate the cost of a repair. The data shows that there a two parts used on a single repair. Why would we use an average when there is only 1 part for 1 repair??

What result do you want to see for TVC1 (12345) in Nov-11?

if:
HTML:
month	Serial	Part cost	model
Nov-11	12345	1.25	TVC1
Nov-11	12345	0.89	TVC1
Nov-11	12345	5.97	TVC1
Dec-11	35749	1.24	TVC2
Dec-11	35749	8.14	TVC2
Jan-12	54321	7.84	TVC1
Jan-12	54321	2.35	TVC1
Jan-12	67890	8.97	TVC2
Jan-12	67890	4.32	TVC2

given in A13:
HTML:
model	TVC2
date	Jan-12

13.29
formula being:
=SUMIFS(C2:C10,D2:D10,B13,A2:A10,B14)

that is a SUM not an average as pointed out by forum but maybe you are looking for Global Cost in lieu of Average Cost (in Excel average tends to mean... average)

is this what you need?

Value in B13 and B14 could be changed to fit your needs.

the data is like t his

Excel Workbook
ABCD
1MonthSerial NumberPart CostModel
211-Nov12345\$1.25TVC1
311-Nov12345\$0.89TVC1
411-Nov12345\$5.97TVC1
511-Dec35749\$1.24TVC2
611-Dec35749\$8.14TVC2
712-Jan54321\$7.84TVC1
812-Jan54321\$2.35TVC1
912-Jan67890\$8.97TVC2
1012-Jan67890\$4.32TVC2
Sheet1

try this macro

Code:
Sub test()
Dim model As Range, data As Range, unq As Range, cunq As Range
Dim mmonth As Range, unqmonth As Range, cmonth As Range, ddate As Date

Worksheets("sheet1").Activate
Range(Range("a1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete

Set model = Range(Range("D1"), Range("D1").End(xlDown))
Set mmonth = model.Offset(0, -3)
Set data = Range("A1").CurrentRegion
Set unq = Range("a1").End(xlDown).Offset(5, 0)
mmonth.AdvancedFilter xlFilterCopy, , unq.Offset(0, 1), True

Range(unq.Offset(1, 1), unq.Offset(1, 1).End(xlDown)).Copy
unq.Offset(0, 1).PasteSpecial Transpose:=True

Range(unq.Offset(1, 1), unq.Offset(1, 1).End(xlDown)).Cells.Clear
Set unqmonth = Range(unq.Offset(0, 1), unq.End(xlToRight))

Set unq = Range(unq.Offset(1, 0), unq.End(xlDown))
For Each cunq In unq
For Each cmonth In unqmonth
ddate = cmonth.Value
'MsgBox ddate
data.AutoFilter Field:=4, Criteria1:=cunq.Value
data.AutoFilter Field:=1, Criteria1:=Format(ddate, "d-mmm")

Application.Intersect(Rows(cunq.Row), Columns(cmonth.Column)) = WorksheetFunction.Subtotal(9, Range(Range("c2"), Range("c2").End(xlDown)))
ActiveSheet.AutoFilterMode = False
Next cmonth
Next cunq
End Sub

the result will be five rows below data as follows

Excel Workbook
ABCD
15Model11-Nov11-Dec12-Jan
16TVC18.114125410.19
17TVC2412549.3813.29
Sheet1

Hi venkat1926,

cool macro.
what does 41254 stands for, dec 11 2012?

cyribird-whatever way you enter the dates in excel whatever is shows in formula bar, excel STORES it as a serial number,

you type this number in a cell
and format that cell as dd-mm-yy

you will get the date.

Replies
4
Views
541
Replies
6
Views
361
Replies
2
Views
152
Replies
22
Views
857
Replies
4
Views
405

1,203,120
Messages
6,053,626
Members
444,674
Latest member
Fieldy1999

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back