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

Report
-
 Cindy -
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

Posts
2755
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 3, 2021
462
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

thanks sooo much! you just saved me so much time!
Posts
2755
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 3, 2021
462
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
that's just giving me error #DIV/0.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!