+ Reply to Thread
Results 1 to 8 of 8

Calculate weighted average for values between two date ranges.

  1. #1
    Registered User
    Join Date
    10-25-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    15

    Calculate weighted average for values between two date ranges.

    Hi all,


    A B C
    1 Date Tonnes Grade
    2 1/06/12 200 5
    3 5/06/12 300 6
    4 10/06/12 600 4

    I have a data set representative of the above (although thousands of rows) and need to calculate a weighted average grade for those values which have a date >= 1/06/12 and <= 6/06/12. Any thoughts on how I can do this?


    Thanks in advance!

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculate weighted average for values between two date ranges.

    Well, I am not well versed in weighted averages, but I would think we would also need some information on how the weighting is supposed to occur...maybe a sample workbook would help..
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    10-25-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Calculate weighted average for values between two date ranges.

    Sure thing, see the attached which shows the calculation of a weighted average. Now I just need to be able to specify a data range.

    Thanks.

    Chan
    Attached Files Attached Files

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculate weighted average for values between two date ranges.

    And you want this where, compared against what?.....your sample DOES give the weighted average, what it does NOT do, is show what you expect to see against what base date...it can not, as there is no base data....What have you got, what do you expect to see from a few samples...hopefuly a few different dates, so we can make sure the formulas are working right
    (Must be be me, but I have noticed that tonight, very few people actually willing to supply workbooks with relevant info...)

  5. #5
    Registered User
    Join Date
    10-25-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Calculate weighted average for values between two date ranges.

    No problems at all and truly appreciate your assistance on this. See the attached
    Attached Files Attached Files

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculate weighted average for values between two date ranges.

    Okay, try this -
    In H1 : Start Date
    In I1 : Last Date
    then this :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (I've put this in J2 in the attachment, But you could put it in G2)

    Note - the formula in D49 is simply a manual check, delete without worry

    Hope this helps
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-28-2012
    Location
    Montana
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Calculate weighted average for values between two date ranges.

    I really struggled trying to figure how to make my formula work; in the end, I found this thread and it solved my problem. Thank you very much dredwolf.

    Quote Originally Posted by dredwolf View Post

    Hope this helps

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculate weighted average for values between two date ranges.

    You are very welcome !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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