Stuck! and need a formula Please!

[Blueridge227]

I have a Column A "Part Number" and a Column B "Hours".

Example
A.........................B
12345..................0.5
54321..................1.0
12345..................1.5
54321..................2.0
98765..................5.0

Notice same part number can be used more than once and have different hours.
So I'm looking for a formula that will calculate the average hours for each part number.

Note Column A contains over 5000 part numbers with tons of duplicates so the idea is on another worksheet I have removed all the duplicates and would like to show the average hours next to them.

Thanks in advance!

 

[number kruncher]

If you have Excel 2007 or above, you could use..

=AVERAGEIFS(B1:B5,A1:A5,A1,B1:B5,"<>0")

 

[Jonmo1]

Sheet1 has data as you posted
Sheet2 has your unique list of part numbers (in column A)

In Sheet2, B2 and filled down..

=SUMIF(Sheet1!A:A, A2, Sheet1!B:B)/COUNTIF(Sheet1!A:A, A2)


Hope that helps.

 

[jasonb75]

Or use a PivotTable.

Row label = Part number
Value label = Average of Hours

 

[repairman615]

Hello,

Welcome to the board!

The AVERAGEIF() may help. Using 2007 or later.


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>PN</TD><TD>HRS</TD><TD>AVG HRS</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1.5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">54321</TD><TD style="TEXT-ALIGN: right">0.5</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1.5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">54321</TD><TD style="TEXT-ALIGN: right">1.5</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">33333</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=AVERAGEIF($A$2:$A$6,A2,$B$2:$B$6)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">=AVERAGEIF($A$2:$A$6,A3,$B$2:$B$6)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left">=AVERAGEIF($A$2:$A$6,A4,$B$2:$B$6)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=AVERAGEIF($A$2:$A$6,A5,$B$2:$B$6)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=AVERAGEIF($A$2:$A$6,A6,$B$2:$B$6)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Blueridge227]

Thank you! This helps a lot!

Is there a way to tweak the formula so that I don't get the divide by zero error message(#DIV/0!) on blank cells when hours were not recorded?

 

[repairman615]

Hello,

2007 or later. The IFERROR() could do on any of the formulas here. Basically like:

<TABLE style="WIDTH: 278pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=370><COLGROUP><COL style="WIDTH: 278pt; mso-width-source: userset; mso-width-alt: 6765" width=370><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 278pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=370>=IFERROR(AVERAGEIF($A$2:$A$6,A2,$B$2:$B$6),"-")

</TD></TR></TBODY></TABLE>

 

[jrabi]

You don't need to make a and b absolute, the below will be eaier to manage:
<TABLE style="WIDTH: 278pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=370><COLGROUP><COL style="WIDTH: 278pt; mso-width-source: userset; mso-width-alt: 6765" width=370><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 278pt; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl63 height=20 width=370>=IFERROR(AVERAGEIF($A$2:$A$6,A2,$B$2:$B$6),"-")</TD></TR></TBODY></TABLE>

=IFERROR(AVERAGEIF(A:A,A2,B:B),"-")

 

[snoopyhr]

Not as neat as others but it is bullet proof you can throw anything you want at it it handles text, number, crazy Chartres (*)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Sheet 2
2	Count of unique parts	14
3		Parts	Count	Average hour	Readings						Proof of Average
4		12345	2	1	0.5	1.5					1
5		54321	4	1.75	1	2	2	2			1.75
6		98765	6	2.166666667	3	3	1	1	3	2	2.166666667
7		83540	4	2.25	3	3	2	1			2.25
8		34072	3	2	2	2	2				2
9		13606	3	1.666666667	2	1	2				1.666666667
10		81363	1	3	3						3
11		24415	3	2.666666667	2	3	3				2.666666667
12		86807	2	3	3	3					3
13		65825	3	1.666666667	2	2	1				1.666666667
14		26172	3	1.666666667	1	3	1				1.666666667
15		83067	1	2	2						2
16		69610	1	2	2						2
17		69458	1	2	2
18
19
20		PARTS refer to range in sheet 1 A3:A34 if you create a name range PARTS and then convert your data into table (excel 2007 and higher) or List (excel 2003) than you can have a dynmic list to lookup, the same goes with HOURS
Sheet2

 

[Blueridge227]

Thank You for your replies. I am up and running with a solid formula thanks to you guys.

Appreciate it!