+ Reply to Thread
Results 1 to 16 of 16

Help calculating monthly orders depending on cumulative orders to date

  1. #1
    Registered User
    Join Date
    01-30-2015
    Location
    Hampshire
    MS-Off Ver
    2013
    Posts
    4

    Lightbulb Help calculating monthly orders depending on cumulative orders to date

    Hi, I need to create two excel formulas to show the number of orders in each month, but they are dependant on how many orders have been cumulatively placed in the year to date.

    Basically if the cumulative number of orders are less than or equal to 100, then I need to know how many orders were placed in the month.
    Secondly if the cumulative number of orders is more than 100, I need to know how many of those orders were placed in the month.

    The reason for this is that for up to 100 orders we have one price, but for >101 we have another price.

    E.G. If we had 50 orders in Jan, 30 orders in Feb and 40 orders in Mar and 60 in Apr, that is a total of 180 orders. Cumulatively that's in 50 Jan, 80 in Feb, 120 in Mar and 180 in Apr. The In Jan they would all be at the lower price, same for Feb, but in Mar we'd have 20 at the lower price and 20 at the higher price, then in Apr it would be 0 at the lower price and 60 at the higher price.

    I'm tearing my hair out trying to come up with a formula which is consistent every month without having to manually tweak it.

    Can anyone help me? I'd be so grateful if anyone can help.
    Attached Files Attached Files
    Last edited by nats2412; 02-02-2015 at 06:25 AM.

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Help calculating monthly orders depending on cumulative orders to date

    If you are still interested in a solution for this you should attach a worksheet with a dataset and the desired results.

  3. #3
    Registered User
    Join Date
    01-30-2015
    Location
    Hampshire
    MS-Off Ver
    2013
    Posts
    4

    Re: Help calculating monthly orders depending on cumulative orders to date

    Thanks for your response. I have uploaded a file with the table I am trying to populate and the data I am using.

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Help calculating monthly orders depending on cumulative orders to date

    See how this works for you. I filled in the formulas, so you can see what I did.
    Note that in cells A8, A9, A11 and A12 I did a custom number format. If you look at the cell it looks as it originally did but if you highlight one of these cells and look at the formula bar you will just see a number. I did that so I can use that number directly.

    Good luck!

  5. #5
    Registered User
    Join Date
    01-30-2015
    Location
    Hampshire
    MS-Off Ver
    2013
    Posts
    4

    Re: Help calculating monthly orders depending on cumulative orders to date

    Hi there, thank you for your response. It's monthly requestss I need to see rather than cumulative. Also where the cells say 506700, I need it to say zero, as the monthly requests would be at the next level on the row beneath. It's this part of the formula that I am struggling with. I can work out the monthly requests but when I get to the threshold, I can't get the subsequent months on the lower volume level to say zero. The formulas neeed to be automated as the request volumees may change. I bet it's quite simple but it's dribing me crazy.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Help calculating monthly orders depending on cumulative orders to date

    Your required it Financial Year wise Cumulative or total cumulative
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Help calculating monthly orders depending on cumulative orders to date

    Your required it Financial Year wise Cumulative or total cumulative

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Help calculating monthly orders depending on cumulative orders to date

    Quote Originally Posted by nats2412 View Post
    Hi there, thank you for your response. It's monthly requestss I need to see rather than cumulative. Also where the cells say 506700, I need it to say zero, as the monthly requests would be at the next level on the row beneath. It's this part of the formula that I am struggling with. I can work out the monthly requests but when I get to the threshold, I can't get the subsequent months on the lower volume level to say zero. The formulas neeed to be automated as the request volumees may change. I bet it's quite simple but it's dribing me crazy.

    You know what I misread your comments in the cell, but I think I have what you want.

    Just notice that in cell B8 and B11 the formula is different than the one next to it. Then the one next to it is copied to the right. The only issue I have is where do I start over again. So wherever your financial years starts make sure the first formula is similar to those in the B column and the one to the right similar to those in the c column and then copy again. Just look at the formulas and you'll see what I mean. If this is not what you want then fill in some of the cells for me and tell me what I'm looking at so I can rework the formula.

  9. #9
    Registered User
    Join Date
    01-30-2015
    Location
    Hampshire
    MS-Off Ver
    2013
    Posts
    4

    Re: Help calculating monthly orders depending on cumulative orders to date

    Thank you very much, this is nearly what I am after. The new year in terms of calculating volumes starts every July. The cumulative volumes are shown on rows 7 and 10. However the cells in rows 8, 9, 11 & 12 are still cumulative, e.g. in cell C8, the cumulative total is 179, but I need to see a monthly total of 114 for the month of May. Another example would be in cell M11, the monthly total should be 59,544. In M12 it is 522, in N11 it should be 0, in N12 it should be 53,391, etc. I hope this makes sense?

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Help calculating monthly orders depending on cumulative orders to date

    see attached file
    Attached Files Attached Files

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Help calculating monthly orders depending on cumulative orders to date

    Ignore my post there is mistake

  12. #12
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Help calculating monthly orders depending on cumulative orders to date

    I believe this version to be error free. Please test it to make sure.
    I learned a few things from nflsales, thanks for that. I wondered why you were using sum on single cells and I figured out why.

    Good Luck!
    Attached Files Attached Files

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Help calculating monthly orders depending on cumulative orders to date

    There is a text in column A
    If you used B1-A1 then it gives an error
    To avoid that i used sum

  14. #14
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Help calculating monthly orders depending on cumulative orders to date

    Quote Originally Posted by nflsales View Post
    There is a text in column A
    If you used B1-A1 then it gives an error
    To avoid that i used sum
    Yeah, thanks I figured that out. I took all the sums out and got an error and noticed when I put back the one referencing the text the error went away.

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Help calculating monthly orders depending on cumulative orders to date

    see attached file
    Attached Files Attached Files

  16. #16
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Help calculating monthly orders depending on cumulative orders to date

    Quote Originally Posted by nflsales View Post
    see attached file
    You might want to look that over again.

+ 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] Help needed urgently, excel cells that function like ms project
    By devvie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2013, 12:08 PM
  2. Excel 2007 macro help needed urgently
    By nemo12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2012, 01:20 PM
  3. [SOLVED] Excel Form (VBA) validation help needed urgently
    By neo5000 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-27-2012, 09:16 AM
  4. help needed urgently
    By richard354 in forum Excel General
    Replies: 3
    Last Post: 07-31-2006, 09:28 AM
  5. [SOLVED] Help Needed Urgently
    By John in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2005, 11:05 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