+ Reply to Thread
Results 1 to 2 of 2

Need help with lookup / average spanning seperate worksheets

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    Essex
    MS-Off Ver
    Excel 2007
    Posts
    2

    Need help with lookup / average spanning seperate worksheets

    Hi there.

    I will try and be simple. So here goes an example of what i need help with:

    STOCK WORKSHEET (Say im selling single apples, pears & oranges at different prices)
    A B sell price
    ----------------------------------------------
    Apple - £0.52
    Apple - £0.63
    Orange - £0.51
    Pear - £0.75
    Orange - £0.85
    Apple - £1.10


    FINANCE WORKSHEET (Column C is where i need help...)
    A B Sold C average selling price??
    -------------------------------------------
    APPLE - 3 -??
    ORANGE - 2 -??
    PEAR - 1 -??

    So i need a formula on the Finance page, column C , to look up every APPLE, ORANGE and PEAR entry (Separately) in the STOCK WORKSHEET and then return to me the average selling price in column C on the FINANCE WORKSHEET?

    e.g C - look up every APPLE entry on STOCK WORKSHEET (0.52, 0.63 & 1.10) then give me an average of these 3 result in the C column 'APPLE' entry on FINANCE WORKSHEET

    I can use lookup and several intermediate function, for example I can create lookup formula for the amount of items sold on another work sheet as in column B on the finance worksheet, but this other issue is just one step too advanced for me to work out?? so please help me.

    cheers. Cole.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,729

    Re: Need help with lookup / average spanning seperate worksheets

    you could put this into C
    =SUMIF($A$1:$A$6,A1,$B$1:$B$6)/COUNTIF($A$1:$A$6,A1)
    copy down
    it will then give you the average

    OR
    =AVERAGEIF($A$1:$A$6,A1,$B$1:$B$6)

    will give you the average
    however, that does not extract just the unique values in A - we could do that and then work out the average - if required
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Defining an area spanning across multiple worksheets.
    By ari12066257 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2013, 03:40 PM
  2. array formula lookup spanning two columns
    By bennunn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2013, 11:23 AM
  3. [SOLVED] LOOKUP/MATCH for the last 365 days when on seperate worksheets?
    By johnmitch38 in forum Excel General
    Replies: 6
    Last Post: 04-18-2012, 12:08 PM
  4. Sumproduct() challenge: Spanning multiple worksheets
    By DKowalski in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2011, 04:35 AM
  5. Replies: 5
    Last Post: 07-31-2006, 11:08 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1