+ Reply to Thread
Results 1 to 4 of 4

Seeking formula to find weighted average by identifying all unique rows containing "X"

  1. 08-01-2013, 12:55 PM #1
    HenryS1001
    HenryS1001 is offline
    Registered User
    Join Date
    08-01-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    1

    Seeking formula to find weighted average by identifying all unique rows containing "X"

    Trying to calculate a total average cost for hats (see sample data) using a formula to identify all cells in column A containing the word "Hat" and taking a total weighted average of all these amounts (Total Quantity * Average Cost). All quantities and average costs for each row of hats will be different. Final results should = $17.99

    Note: Real data set has thousands of rows so needs to search column A for text containing "Hat" vs. manually selecting the items and cannot use basic filter…

    I've attached a sample of the raw data.

    Thanks for your help in advance!!

    Sample Data Set.xlsx
    Reply With QuoteRegister To Reply
  2. 08-01-2013, 01:51 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Seeking formula to find weighted average by identifying all unique rows containing "X"

    See the attached file.

    Notice I get an differant result (€ 17,33)
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 08-01-2013, 02:07 PM #3
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Seeking formula to find weighted average by identifying all unique rows containing "X"

    Try this formula to give you 17.99

    =SUMPRODUCT(ISNUMBER(SEARCH("hat",A2:A8))+0,B2:B8,C2:C8)/SUMIF(A2:A8,"*hat*",B2:B8)
    Audere est facere
    Reply With QuoteRegister To Reply
  4. 08-01-2013, 04:33 PM #4
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Seeking formula to find weighted average by identifying all unique rows containing "X"

    With helpcells, I get the result € 17,99.

    See the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Identifying rows with a certain symbol e.g. "@" and grouping them together?
    By sdtopballa in forum Excel General
    Replies: 3
    Last Post: 01-03-2013, 08:38 PM
  2. Calculating "Weighted Average" but excluding certain items
    By jasonwwall in forum Excel General
    Replies: 2
    Last Post: 02-27-2012, 09:40 AM
  3. Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT"
    By tyrese214 in forum Excel General
    Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  4. Can I use "find / replace" to automatically "Insert Rows" in a do.
    By Dr. Picou in forum Excel General
    Replies: 2
    Last Post: 09-01-2005, 05:05 PM
  5. Can you calculate "weighted average cost of capital?
    By Dennis in forum Excel General
    Replies: 1
    Last Post: 06-20-2005, 02:33 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1