+ Reply to Thread
Results 1 to 25 of 25

Get the average figures per month

  1. #1
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Get the average figures per month

    Dear all!
    I am very happy to found this site, and all your knowledge have helped my amateur excelmind alot.

    I have a problem with this file.
    It is divided into different periods (that can be changed). It is "programmed" to take out an average of all these periods.

    But I want it also ( in a column beside) to calculate the average per month.
    I have had some "expert" help here in Sweden, but NOBODY could get it to to it, so I believe it is not possible...
    This is my last resort to get some kind of help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get the average figures per month

    As i Say the main dates can be changed so that they are not in such order as they are now , example 1/4-15/4 --15/4-3/5 and so on. That is why it is such a mess to get excel to sort out the dates and make an average out of it...

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,575

    Re: Get an monthly average from different periods

    It is not clear to me what you want to do. Can you re-post a file with some data in it and explain what you want to get out of it.

    Pete

  4. #4
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear Sir!
    PLs see attached.

    As you can see - "the green field" is calculating an average of all periods (the one you see to the left) and these periods can be shifting (right now they are in order monthly, but can also be like: 1 of May to 15 of may and 16 of May to 18 of May etc) but then the "Rates" are shifting. So I want to take out an average of it monthly, even though there can be for example three periods in on month.

    Very glad you look into this, but I think it is not possible
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    See if this is what you want to achieve.

    I changed (or add) the blue cells

    See the attached file.
    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.

  6. #6
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear oeldere!
    This is exact what I want it to do, but I would not like an Pivot tabel, because it is not updating "live". Is there some kind of macro that could do the trick you have done so far.
    Much MUCH appreciated. Kind regards Henrik

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    I don't have a macro for that, but you can refresh the pivot table.

    Or that is not what you mean with (not updating "live")?

  8. #8
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear Oeldere!
    Am I so stupid (apperantly ) How do I refresh Pivot Table?

  9. #9
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    But here is the tricky part. What if the month overdue eachother= if a period is 15/5 to 17/6? How can we solve this!?

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    => data (this is the sixed choise in the excel 2007 version)

    => refresh (this is the fourth choise in the excel 2007 version)

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    for your solution in #8.

    Is it possible to split the input like i did in my example?

    Then the problem would be solved.

    Or it can't be done that way?

  12. #12
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    The periode should stay as they are so we cannot split them up , if there is not an easy way to do so, because I need the periods to stay as they are.
    Once again Oeldere MANY THANKS, but I think this is a real nutcracker that can not be solved...

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    It can be done, but i have 2 questions

    1) It's allowed to use helpcolumns to determine the count of days / per month?

    2) the data is inputted manualy or they are downloaded?

  14. #14
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear Oeldere!
    1, Does this mean you need to split up "the green field" more than it is now? Or you will have help of another column next to the green field somewhere- If yes on last question then it is Ok.
    2, Numbers are inserted manually by me.

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    Still 2 other things:

    1) you found the refresh of the pivot table?

    2) If you inserted the manually (why can't the date be changed)?

    3) the overdue to a month is just 1 month (april and may) or it can be differant monts (e.g. april / may / june / july)?

  16. #16
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear oeldere,
    1,Yes I found it, had to replace to "connection" but now it works
    2, I will use this program to contractperiods so when I start I will put in the periods and then the "rates" in the different periods. This will change for the different contracts I will be writening with the different partners.
    So the dates can be changed everythin can be changed depending on what we come to an agreement of.
    3; It can be different months and there can for example be 3-4 different periods within the same month.

    Therefor this is so hard to solve (if possible) I need the Monthly Averages to compare against for example other contracts and depending on other cirmcumstances.

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    Last request (for now ).

    Please Login or Register  to view this content.
    just show me in a file the possiblities of your answer 3

  18. #18
    Registered User
    Join Date
    06-15-2013
    Location
    uae
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Get an monthly average from different periods

    i have question that how to arrange an excel monthly time sheet dates(Ex. Sunday, Monday, Tuesday ,Wednesday,Thursday ,Friday Saturday)-every month its changing according to the dates,what i need to know every Friday are holyday for us so friday must be in red colour letter .now i arrange my time sheet automatically changing according to the month list but friday colour not staying with that,so my question is howto get friday always red colour? can u solve me the problem?
    Last edited by saiju; 06-29-2013 at 08:43 AM.

  19. #19
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    @saiju

    please start your own question.
    Last edited by oeldere; 06-15-2013 at 09:33 AM. Reason: change text post in please

  20. #20
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear Sir!

    PLS see attached how the different periods can look like.

    Therefor I need to "take out" the Average per month because the formula there is now just give me an average for the total of all periods....
    Kind regards
    EHrnik
    Attached Files Attached Files

  21. #21
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    Probably you add the wrong file.

    The one your just posted, is the same as the first one.

    In this file I don't see any overlap for the months.

  22. #22
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear Oeldere!

    No if you look into the periods there are many differnet dates, exmple 1 periode is just 2 days.
    This is how it can look like

  23. #23
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    @hep.

    See if this it what you want to achieve.

    The result is in the golden cells.

    See the attached file.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    10-07-2012
    Location
    lund
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Get an monthly average from different periods

    Dear Oeldere!
    This is EXACTLY how I wanted it.
    This is so awesome.

    MANY MANY THANKS.
    I appreciate it so much.

    Kind regards
    Henrik

  25. #25
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,498

    Re: Get an monthly average from different periods

    Thanks for the reply.

    Glad i could help.

    You can add points to the (rep)utation by clicking on the star.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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