+ Reply to Thread
Results 1 to 14 of 14

Don't know how to proceed, can't prorate Gross Amount by different Months in Period given!

  1. #1
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Exclamation Don't know how to proceed, can't prorate Gross Amount by different Months in Period given!

    Hi all,

    Within a report I receive it gives me a Billing period between 2 dates, sometimes these dates are at the end of one month and the beginning of another month. I must prepare a costing per month view. I have used logic in Excel to break up the 10/19/2014 - 11/1/2014 (Final Invoice) string into a start and end date. With these dates I have identified how many days per month this range falls under, it could be all one month, it could be 2 months and I need to break up the Gross Ammount in its respective month based on the proportion.

    Please check this file Attached: Sample Pro Rate.xlsx

    shg can you check the fabulous formula you gave me, for some reason the formula breaks up the dates in the right spot but when you add the total amount it will be higher or lower than the total Gross Amount which it shouldn't be. I applied conditional formatting highlighting these cells in Yellow. Can you correct this problem? If you can you are wonderful
    Last edited by lougs7; 10-26-2015 at 10:20 AM.

  2. #2
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    the only way I can think is to add 24 more columns representing each of the months and putting a formula as such...
    Attachment 425787

    Was seeing if there was a way to do this that only showed the months in questions and not have 22-23 columns with zeros

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    Like this?

    Row\Col
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    1
    Start
    End
    Wkdays
    Amount
    Sep 14
    Oct 14
    Nov 14
    Dec 14
    Jan 15
    Feb 15
    2
    19 Oct 2014
    01 Nov 2014
    10
    $ 2,560
    $ 0
    $ 2,560
    $ 0
    $ 0
    $ 0
    $ 0
    F2: =NETWORKDAYS(D2, E2)
    3
    02 Nov 2014
    15 Nov 2014
    10
    $ 3,200
    $ 0
    $ 0
    $ 3,200
    $ 0
    $ 0
    $ 0
    H2: =MAX(0, NETWORKDAYS(MAX(H$1, $D2), MIN(EDATE(H$1, 1) - 1, $E2))) * $G2 / $F2
    4
    16 Nov 2014
    29 Nov 2014
    10
    $ 2,560
    $ 0
    $ 0
    $ 2,560
    $ 0
    $ 0
    $ 0
    5
    30 Nov 2014
    13 Dec 2014
    10
    $ 850
    $ 0
    $ 0
    $ 0
    $ 850
    $ 0
    $ 0
    6
    14 Dec 2014
    27 Dec 2014
    10
    $ 2,080
    $ 0
    $ 0
    $ 0
    $ 2,080
    $ 0
    $ 0
    7
    28 Dec 2014
    10 Jan 2015
    10
    $ 300,000
    $ 0
    $ 0
    $ 0
    $ 90,000
    $ 210,000
    $ 0
    8
    11 Jan 2015
    24 Jan 2015
    10
    $ 2,424
    $ 0
    $ 0
    $ 0
    $ 0
    $ 2,424
    $ 0


    I removed your holidays from your formulas - it was linked to another workbook.
    Last edited by shg; 10-19-2015 at 03:55 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    Yaaa how do you get to that part directly like that as you did, I can only get there with a lot more steps

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    The formulas are shown.

  6. #6
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    I updated the Workbook to reflect how I get there, was wondering if there is a way to do this in a more efficient manner, not displaying the rows with zeros potentially, or in less steps? I tried the formula you gave me but for some reason it is working but doesn't pro-rate it properly for some rows, this could be a syntax issue I am not sure, you can look at the new Workbook I uploaded to see what I did....

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    See attached.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    You sir are a scholar! Thank you kindly

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    You're welcome.

  10. #10
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    Quote Originally Posted by shg View Post
    You're welcome.
    Hey shg, there were errors in the formula, I explained it in the description at the end and edited my post with the attachment. Can you please take a look?

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    F2: =NETWORKDAYS(D2,E2,Holidays!$A$3:$C$11)

    S2: =MAX(0,NETWORKDAYS(MAX(R$1,$D2),MIN(EDATE(R$1,1)-1,$E2),Holidays!L1399:N1407))*$AH2/$F2

    Does that look right to you?

  12. #12
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    Not sure if this solved it, I am still getting the broken up portion larger then the Gross amount which is not supposed to happen! If this formula is right, the different split up portions based on the formula that gets inputed into the different months should add up exactly to the gross amount each time, not more or less. Are you able to upload the workbook like you did last time? or perhaps investigate this another way. Thank you btw

  13. #13
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    Quote Originally Posted by shg View Post
    F2: =NETWORKDAYS(D2,E2,Holidays!$A$3:$C$11)

    S2: =MAX(0,NETWORKDAYS(MAX(R$1,$D2),MIN(EDATE(R$1,1)-1,$E2),Holidays!L1399:N1407))*$AH2/$F2

    Does that look right to you?
    Not sure if this solved it, I am still getting the broken up portion larger then the Gross amount which is not supposed to happen! If this formula is right, the different split up portions based on the formula that gets inputed into the different months should add up exactly to the gross amount each time, not more or less. Are you able to upload the workbook like you did last time? or perhaps investigate this another way. Thank you btw

  14. #14
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

    No, that won't solve it. I was trying to get you to see the problem.

    What range contains the holidays?

    Why are those ranges different in the two formulas?

+ 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] Formula that will show proceed or not proceed
    By geliedee in forum Excel General
    Replies: 7
    Last Post: 01-29-2015, 07:35 AM
  2. [SOLVED] % of gross changes based on amount produced, what is the commission
    By rob@dmsdealer.com in forum Excel General
    Replies: 1
    Last Post: 06-12-2012, 12:30 PM
  3. Replies: 5
    Last Post: 04-01-2012, 04:20 PM
  4. Working out gross if I know the net amount
    By plumfin in forum Excel General
    Replies: 11
    Last Post: 01-13-2011, 02:08 PM
  5. Loan period in Months
    By Parveez in forum Excel General
    Replies: 1
    Last Post: 10-02-2005, 01:05 PM
  6. Need to subtract vat from gross amount.
    By buachille in forum Excel General
    Replies: 2
    Last Post: 09-22-2005, 01:43 PM
  7. Replies: 3
    Last Post: 08-09-2005, 11:05 PM

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