Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default Using AVERAGE with conditions in different worksheets?

I have a workbook where in sheet 1 there is a list of items (such as apple,
pear, orange). In sheet 2 these items are listed with various prices. How
can I set up a formula that will take each item in the list in sheet 1 and
find all the matches in sheet 2 and then determine the average price
associated with it? I am looking for a forumla I can put in sheet 1 and then
copy for the entire list so I won't have to retype the forumla each time.
Your help is greatly appreciated! Thanks.

Sheet 1
apple
orange
pear

Sheet 2
apple $1
apple $2
pear $3
pear $4
pear $3
orange $6
orange $7
orange $3
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Using AVERAGE with conditions in different worksheets?


If your sheet1 criteria (apple, orange, pear) are in A1 down and list of
fruits is in sheet2 column A with dollar amounts in sheet2 column B then
use this formula in sheet1 B1 copied down

=SUMIF(Sheet2!A:A,A1,Sheet2!B:B)/MAX(1,COUNTIF(Sheet2!A:A,A1))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=561876

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Average across worksheets madduck Excel Worksheet Functions 3 July 13th 06 07:03 AM
Average Function (include Blank Cells and Zeros) [email protected] Excel Discussion (Misc queries) 17 June 27th 06 01:33 PM
rolling average William Okumu Excel Worksheet Functions 5 May 25th 06 03:49 PM
Modified Average Function PA Excel Worksheet Functions 3 May 15th 06 12:20 PM
Changing a Link Mid-way Across Worksheets Frosty Excel Worksheet Functions 0 August 25th 05 12:03 AM


All times are GMT +1. The time now is 11:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"