Find Serial Number and input Average Value into Adjacent cell

Solved/Closed
Cindy - Feb 15, 2016 at 08:29 AM
 Cindy - Feb 15, 2016 at 11:59 AM
Hello,

I'm trying to find out if there is a way to do this. i have a list of 15k serial numbers. several of them repeat because the value vary. i need to be able to type the serial number into a different sheet and it insert the average value for it in the adjacent cell. the tricky part is i need it to search 'exact stings' ex if i type in da1 it only returns value for that number not x09da123. not sure if this is possible but i have to do this manually over 100 times a day and its very time consuming. and please try to explain simply i'm far from a newbie when it comes to excel but i learn thing as i need them and i'm definitely not an expert lol.


2 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Feb 15, 2016 at 11:49 AM
Hi Cindy,

This is what I did:

Sheet1
H1  	        H2
x09da123 10
da1 10
x09da123 1
da1 1
da3 1


Sheet2
A2: da1
B2: =AVERAGEIF(Sheet1!A2:A6,A2,Sheet1!B2:B6)
B2 shows the value "5.5".


Can you make it work now?

Best regards,
Trowa
1
thanks sooo much! you just saved me so much time!
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Feb 15, 2016 at 11:23 AM
Hi Cindy,

You could try using the AVERAGEIF formula:
=AVERAGEIF(Column with s/n, the s/n you want to look up, column with values)

Best regards,
Trowa
0
that's just giving me error #DIV/0.
0