+ Reply to Thread
Results 1 to 16 of 16

How to calculate avg rate based on repetitive products?

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    How to calculate avg rate based on repetitive products?

    Sample Data attached.


    I have data as above. A,B,C is available. D is based on B and C. Requirement is defined in "E" column.
    If a product is repeated the average rate is sum of multiplication of rate and value divided by sum of values. Although, the formula is explained in "E" Column but this is small data. I have over a million values in "A" out of which some(not all) are repeated(twice or thrice). Applying formula to each of them is difficult. Kindly suggest a shortcut.
    Attached Files Attached Files
    Last edited by Afsheen; 08-14-2018 at 06:30 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to calculate avg rate based on repetitive products?

    Why is this in the charting section? Shall I move it for you?

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95
    Please move the thread. I don't know how to move. Sample data is attached.
    Last edited by AliGW; 08-14-2018 at 07:06 AM. Reason: Please don't quote unnecessarily.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to calculate avg rate based on repetitive products?

    What does this mean?

    As product is sold once no calculation is req.
    How would I know from your data sample that no calculation is required? You need to have a flag for Excel to use.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to calculate avg rate based on repetitive products?

    I am not sure your calculations are correct, but try this:

    =IF(COUNTIF($A$3:$A$9,A3)=1,C3,AVERAGE(IF($A$3:$A$9=A3,$C$3:$C$9)))

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.

  6. #6
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    Re: How to calculate avg rate based on repetitive products?

    The formula is returning average of "C" column. I need average relevant to B.
    As below:-
    (First Value of Oranges*First Rate of Oranges+Second Value of Oranges*Second Rate of Oranges)/(Sum of first and second value of oranges)

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to calculate avg rate based on repetitive products?

    That's not what your formula said:

    (C7*D7+C8*D8)/(C7+C8)

    So what you really want is this:

    (B7*C7+B8*C8)/(B7+B8)

    Is this correct?

  8. #8
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    Re: How to calculate avg rate based on repetitive products?

    If the product is not repeated than no calculation is required.
    In other words, if the count of the product is 1 then no calculation is required.

  9. #9
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    Re: How to calculate avg rate based on repetitive products?

    yes please.

    (B7*C7+B8*C8)/(B7+B8)
    this is correct.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to calculate avg rate based on repetitive products?

    It really is a waste of everybody's time if you provide sample data that is misleading. Please provide a new sample file - make sure that it properly reflects what you really want.

  11. #11
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    Re: How to calculate avg rate based on repetitive products?

    Attached please.
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to calculate avg rate based on repetitive products?

    Try this in D3:

    =SUMPRODUCT(($A$3:$A$9=A3)*$B$3:$B$9,$C$3:$C$9)/SUMIF($A$3:$A$9,A3,$B$3:$B$9)

    It's an ordinary formula - just use ENTER and then drag copy down.

  13. #13
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    Re: How to calculate avg rate based on repetitive products?

    Solved. Thank you.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to calculate avg rate based on repetitive products?

    Next time, make sure you post in the correct sub-forum, provide the data up front and make sure it's accurate! We could have had this solved within fifteen minutes instead of two hours!!!

  15. #15
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    Re: How to calculate avg rate based on repetitive products?

    Hi! I am having little problem in using the formula.

    Each time I have to calculate the average (Result of this formula), the source have different number of rows. Rows may vary from 1 to few hundred. Do I have to edit the bold part of the formula each time?

    =SUMPRODUCT(($A$3:$A$9=A3)*$B$3:$B$9,$C$3:$C$9)/SUMIF($A$3:$A$9,A3,$B$3:$B$9)

    because it does not work if the source data does not fit exactly into given range in formula.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: How to calculate avg rate based on repetitive products?

    I changed the 9's to 100's (as shown below) and pasted the formula into cell E3 of the file attached to post #11. The formula produced the same values as those in column D (Required Result).
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Calculate Cost of Products Based On Multiple Criteria
    By DomLeRoy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-09-2017, 05:26 AM
  2. [SOLVED] Non-Repetitive formula to SUM the products of numbers
    By Soirah in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-07-2017, 07:46 PM
  3. Calculate reduction in fee charged based on lower rate
    By Aland2929 in forum Excel General
    Replies: 1
    Last Post: 03-09-2017, 08:32 AM
  4. Replies: 8
    Last Post: 07-01-2016, 02:07 AM
  5. Replies: 2
    Last Post: 02-29-2016, 03:50 PM
  6. Replies: 1
    Last Post: 05-16-2014, 10:10 AM
  7. [SOLVED] Calculate total sum based on different rate
    By Roy Kean in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-12-2013, 04:10 AM

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