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.


Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
thanks sooo much! you just saved me so much time!
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
that's just giving me error #DIV/0.