|
AVERAGEIF and group??
Hi,
I have a spreadsheet of products with details like barcode, retail price etc. Many of the barcodes repeat (as I can source them from several different suppliers with different cost prices). What I'd like to do is use a function to group the barcodes and get an average of their retail prices. Some sample data is below (i hope it displays properly!): BARCODE SUPPLIER CODE COST-EX COST-INC RETAIL 20048907 SUPPLIER A 35770 0.7583 0.8342 1.63 20048907 SUPPLIER B 10507404 0.7167 0.7883 1.54 20048907 SUPPLIER C S20008 0.6780 0.7458 1.45 20016296 SUPPLIER A 13178 1.9250 2.1175 4.13 9318120607063 SUPPLIER A 64406 2.5000 2.7500 5.36 9318120607063 SUPPLIER B 60156025 2.8500 3.1350 6.11 9318120607063 SUPPLIER D CM77YL 2.3800 2.6180 5.11 9310025083249 SUPPLIER A 17168 1.0583 1.1642 2.27 9310025083249 SUPPLIER B 10503901 0.9950 1.0945 2.13 9310025083249 SUPPLIER E 8324 0.8565 0.9422 1.84 So, using "9318120607063" as an example, I'd need it to find barcodes that match, and average their retail prices (5.36, 6.11, 5.11). The sheet is sorted on column A, so matching barcodes will all be positioned together. I think I need to use AVERAGEIF for this, but I'm not sure how to do the barcode matching bit. Any help would be awesome. Thanks, Simon. |
AVERAGEIF and group??
http://www.freefilehosting.net/download/3f3di
Check my results... -- RyGuy " wrote: Hi, I have a spreadsheet of products with details like barcode, retail price etc. Many of the barcodes repeat (as I can source them from several different suppliers with different cost prices). What I'd like to do is use a function to group the barcodes and get an average of their retail prices. Some sample data is below (i hope it displays properly!): BARCODE SUPPLIER CODE COST-EX COST-INC RETAIL 20048907 SUPPLIER A 35770 0.7583 0.8342 1.63 20048907 SUPPLIER B 10507404 0.7167 0.7883 1.54 20048907 SUPPLIER C S20008 0.6780 0.7458 1.45 20016296 SUPPLIER A 13178 1.9250 2.1175 4.13 9318120607063 SUPPLIER A 64406 2.5000 2.7500 5.36 9318120607063 SUPPLIER B 60156025 2.8500 3.1350 6.11 9318120607063 SUPPLIER D CM77YL 2.3800 2.6180 5.11 9310025083249 SUPPLIER A 17168 1.0583 1.1642 2.27 9310025083249 SUPPLIER B 10503901 0.9950 1.0945 2.13 9310025083249 SUPPLIER E 8324 0.8565 0.9422 1.84 So, using "9318120607063" as an example, I'd need it to find barcodes that match, and average their retail prices (5.36, 6.11, 5.11). The sheet is sorted on column A, so matching barcodes will all be positioned together. I think I need to use AVERAGEIF for this, but I'm not sure how to do the barcode matching bit. Any help would be awesome. Thanks, Simon. |
AVERAGEIF and group??
On Apr 9, 11:56*pm, ryguy7272
wrote: http://www.freefilehosting.net/download/3f3di Check my results... -- RyGuy " wrote: Hi, I have a spreadsheet of products with details like barcode, retail price etc. Many of the barcodes repeat (as I can source them from several different suppliers with different cost prices). What I'd like to do is use a function to group the barcodes and get an average of their retail prices. Some sample data is below (i hope it displays properly!): BARCODE * * * * * *SUPPLIER * * * *CODE * * * * * *COST-EX * * * * COST-INC * * * * * * * *RETAIL 20048907 * * * * * * * * * SUPPLIER A * * *35770 * * * * * 0.7583 * * * * *0.8342 * * * * *1.63 20048907 * * * * * * * * * SUPPLIER B * * *10507404 * * * * * * * *0.7167 * * * * *0.7883 * * * * *1.54 20048907 * * * * * * * * * SUPPLIER C * * *S20008 * * * * *0.6780 * * * * *0.7458 * * * * *1.45 20016296 * * * * * * * * * SUPPLIER A * * *13178 * * * * * 1.9250 * * * * *2.1175 * * * * *4.13 9318120607063 * * * * * * *SUPPLIER A * * *64406 * * * * * 2.5000 * * * * *2.7500 * * * * *5.36 9318120607063 * * * * * * *SUPPLIER B * * *60156025 * * * * * * * *2.8500 * * * * *3.1350 * * * * *6.11 9318120607063 * * * * * * *SUPPLIER D * * *CM77YL * * * * *2.3800 * * * * *2.6180 * * * * *5.11 9310025083249 * * * * * * *SUPPLIER A * * *17168 * * * * * 1.0583 * * * * *1.1642 * * * * *2.27 9310025083249 * * * * * * *SUPPLIER B * * *10503901 * * * * * * * *0.9950 * * * * *1.0945 * * * * *2.13 9310025083249 * * * * * * *SUPPLIER E * * *8324 * * * * * * * * * *0.8565 * * * * *0.9422 * * * * *1.84 So, using "9318120607063" as an example, I'd need it to find barcodes that match, and average their retail prices (5.36, 6.11, 5.11). The sheet is sorted on column A, so matching barcodes will all be positioned together. I think I need to use AVERAGEIF for this, but I'm not sure how to do the barcode matching bit. Any help would be awesome. Thanks, Simon. Thanks for having a look, but I don't think I explained it properly. I'm only concentrating on barcode and retail price (price 3), and all I need it to do is give me an average of all of the prices with the same barcode. So i need it to look up a barcode, find all of the other barcodes that are the same, and average their retail prices. Is it possible? Thanks. |
AVERAGEIF and group??
Take a look at this:
http://www.freefilehosting.net/download/3f3mg Regards, Ryan--- -- RyGuy " wrote: On Apr 9, 11:56 pm, ryguy7272 wrote: http://www.freefilehosting.net/download/3f3di Check my results... -- RyGuy " wrote: Hi, I have a spreadsheet of products with details like barcode, retail price etc. Many of the barcodes repeat (as I can source them from several different suppliers with different cost prices). What I'd like to do is use a function to group the barcodes and get an average of their retail prices. Some sample data is below (i hope it displays properly!): BARCODE SUPPLIER CODE COST-EX COST-INC RETAIL 20048907 SUPPLIER A 35770 0.7583 0.8342 1.63 20048907 SUPPLIER B 10507404 0.7167 0.7883 1.54 20048907 SUPPLIER C S20008 0.6780 0.7458 1.45 20016296 SUPPLIER A 13178 1.9250 2.1175 4.13 9318120607063 SUPPLIER A 64406 2.5000 2.7500 5.36 9318120607063 SUPPLIER B 60156025 2.8500 3.1350 6.11 9318120607063 SUPPLIER D CM77YL 2.3800 2.6180 5.11 9310025083249 SUPPLIER A 17168 1.0583 1.1642 2.27 9310025083249 SUPPLIER B 10503901 0.9950 1.0945 2.13 9310025083249 SUPPLIER E 8324 0.8565 0.9422 1.84 So, using "9318120607063" as an example, I'd need it to find barcodes that match, and average their retail prices (5.36, 6.11, 5.11). The sheet is sorted on column A, so matching barcodes will all be positioned together. I think I need to use AVERAGEIF for this, but I'm not sure how to do the barcode matching bit. Any help would be awesome. Thanks, Simon. Thanks for having a look, but I don't think I explained it properly. I'm only concentrating on barcode and retail price (price 3), and all I need it to do is give me an average of all of the prices with the same barcode. So i need it to look up a barcode, find all of the other barcodes that are the same, and average their retail prices. Is it possible? Thanks. |
AVERAGEIF and group??
On Apr 10, 5:42*am, ryguy7272
wrote: Take a look at this:http://www.freefilehosting.net/download/3f3mg Regards, Ryan--- -- RyGuy " wrote: On Apr 9, 11:56 pm, ryguy7272 wrote: http://www.freefilehosting.net/download/3f3di Check my results... -- RyGuy " wrote: Hi, I have a spreadsheet of products with details like barcode, retail price etc. Many of the barcodes repeat (as I can source them from several different suppliers with different cost prices). What I'd like to do is use a function to group the barcodes and get an average of their retail prices. Some sample data is below (i hope it displays properly!): BARCODE * * * * * *SUPPLIER * * * *CODE * * * * * *COST-EX * * * * COST-INC * * * * * * * *RETAIL 20048907 * * * * * * * * * SUPPLIER A * * *35770 * * * * * 0.7583 * * * * *0.8342 * * * * *1.63 20048907 * * * * * * * * * SUPPLIER B * * *10507404 * * * * * * * *0.7167 * * * * *0.7883 * * * * *1.54 20048907 * * * * * * * * * SUPPLIER C * * *S20008 * * * * *0.6780 * * * * *0.7458 * * * * *1.45 20016296 * * * * * * * * * SUPPLIER A * * *13178 * * * * * 1.9250 * * * * *2.1175 * * * * *4.13 9318120607063 * * * * * * *SUPPLIER A * * *64406 * * * * * 2.5000 * * * * *2.7500 * * * * *5.36 9318120607063 * * * * * * *SUPPLIER B * * *60156025 * * * * * * * *2.8500 * * * * *3.1350 * * * * *6.11 9318120607063 * * * * * * *SUPPLIER D * * *CM77YL * * * * *2.3800 * * * * *2.6180 * * * * *5.11 9310025083249 * * * * * * *SUPPLIER A * * *17168 * * * * * 1.0583 * * * * *1.1642 * * * * *2.27 9310025083249 * * * * * * *SUPPLIER B * * *10503901 * * * * * * * *0.9950 * * * * *1.0945 * * * * *2.13 9310025083249 * * * * * * *SUPPLIER E * * *8324 * * * * * * * * * *0.8565 * * * * *0.9422 * * * * *1.84 So, using "9318120607063" as an example, I'd need it to find barcodes that match, and average their retail prices (5.36, 6.11, 5.11). The sheet is sorted on column A, so matching barcodes will all be positioned together. I think I need to use AVERAGEIF for this, but I'm not sure how to do the barcode matching bit. Any help would be awesome. Thanks, Simon. Thanks for having a look, but I don't think I explained it properly. I'm only concentrating on barcode and retail price (price 3), and all I need it to do is give me an average of all of the prices with the same barcode. So i need it to look up a barcode, find all of the other barcodes that are the same, and average their retail prices. Is it possible? Thanks. Fantastic, that's what I needed. Sorry for the late reply. Thanks! |
| All times are GMT +1. The time now is 03:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com