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

[dan78h]

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.

Thanks for your support.

 

[venkat1926]

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.

 

[dan78h]

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.

 

[Michael M]

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 ??

 

[dan78h]

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??

 

[Aladin Akyurek]

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?

 

[cyrilbrd]

if:

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:

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.

 

[venkat1926]

the data is like t his

Excel Workbook
	A	B	C	D
1	Month	Serial Number	Part Cost	Model
2	11-Nov	12345	$1.25	TVC1
3	11-Nov	12345	$0.89	TVC1
4	11-Nov	12345	$5.97	TVC1
5	11-Dec	35749	$1.24	TVC2
6	11-Dec	35749	$8.14	TVC2
7	12-Jan	54321	$7.84	TVC1
8	12-Jan	54321	$2.35	TVC1
9	12-Jan	67890	$8.97	TVC2
10	12-Jan	67890	$4.32	TVC2
Sheet1

try this macro

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)
model.AdvancedFilter xlFilterCopy, , unq, True
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))

'MsgBox unqmonth.Address
Set unq = Range(unq.Offset(1, 0), unq.End(xlDown))
'MsgBox unq.Address
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
	A	B	C	D
15	Model	11-Nov	11-Dec	12-Jan
16	TVC1	8.11	41254	10.19
17	TVC2	41254	9.38	13.29
Sheet1

 

[cyrilbrd]

Hi venkat1926,

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

 

[venkat1926]

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.