Find Serial Number and input Average Value into Adjacent cell [Solved/Closed]

Ask a question Cindy - Last answered on Feb 15, 2016 at 11:59 AM by Cindy

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.

plus moins
Hi Cindy,

This is what I did:

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

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,
Was this answer helpful?  
Cindy- Feb 15, 2016 at 11:59 AM
thanks sooo much! you just saved me so much time!
plus moins
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,
Cindy- Feb 15, 2016 at 11:35 AM
that's just giving me error #DIV/0.

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!