+ Reply to Thread
Results 1 to 11 of 11

How to use dates as reference in allocation of a certain amount based on different %

  1. #1
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    How to use dates as reference in allocation of a certain amount based on different %

    Hi,

    Can someone help me on what formula to use in cell L13
    I am forecasting the amount of claim to be allocated on cell L13 based on the percentages from cell B2 to B8. And that amount should show under columns L13 onwards.
    Basically it is a weekly forecasting.
    It goes like, if E13 is within the date range of L13 then multiply C13 by B2, or if its F13 that is within the date range of L13, then multiply C13 by B3 instead and so on and so forth.
    I tried to create a formula, unfortunately it wont work.

    I just want the apportioned claim amount to show under the week the type of claim falls due.

    I hope I made it clear. Please help. Thank you!

    See attached file.
    Attached Files Attached Files
    Last edited by MyStix01; 09-19-2017 at 07:20 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,005

    Re: How to use dates as reference in allocation of a certain amount based on different %

    If you look at your post, you will see that the forum has reformatted it, making it completely unintelligible.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    Re: How to use dates as reference in allocation of a certain amount based on different %

    Hi Glenn,

    Thank you for taking the time to comment on my post. Appreciate it a lot. I have attached the file, hope you can help me on this one. Thanks again!
    Last edited by MyStix01; 09-19-2017 at 11:18 PM.

  4. #4
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    Re: How to use dates as reference in allocation of a certain amount based on different %

    Quote Originally Posted by Glenn Kennedy View Post
    If you look at your post, you will see that the forum has reformatted it, making it completely unintelligible.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Hi Glenn,

    Thank you for taking the time to comment on my post. Appreciate it a lot. I have attached the file, hope you can help me on this one. Thanks again!

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: How to use dates as reference in allocation of a certain amount based on different %

    Hello nickyme02 and Welcome to Excel Forum.
    See if the following formula yields the expected results:
    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.

  6. #6
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    Re: How to use dates as reference in allocation of a certain amount based on different %

    Quote Originally Posted by JeteMc View Post
    Hello nickyme02 and Welcome to Excel Forum.
    See if the following formula yields the expected results:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Hi Jet,
    Thanks for the response. However, can you twitch it a little? I've added some new columns and the expected result to better explain what I need here. Please see attached file.
    If you can see from the columns, on 3 Apr 2017 , there's a $5,365.80 which is 1% of the total claim, it is reported under this column since the due date for the Pre-Construction claim is 28-March-2017. And the 3-Apr-2017 column represents all amounts received from a week's time, that is from 28-March to 3-April. Then this goes on so on and so forth on other claims.
    So the formula should also be able to detect under which time period the claim falls due and reflect the amount that should be received within that time frame. I hope I explained it clearly. Thank you so much.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: How to use dates as reference in allocation of a certain amount based on different %

    This proposed solution employs a helper table (BF12:BL15) which may be moved and/or hidden for aesthetic purposes, and is populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the amounts for the different dates in columns L:BC is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    Re: How to use dates as reference in allocation of a certain amount based on different %

    Wow! Thanks a lot! This actually works.

    I just encountered one problem though, some due dates fall under the same date of the predefined columns and the index formula is picking up the next column date instead of the same column date.
    Like for example I changed in red font color the slab claim due date to 8-May-2017. in the weekly column it should be reported under that same date 8-May-2017, however instead it is being reported under the 15-May-2017 column. I figured that it is because of the +1 in the index formula on columns BF12:BL15. But I figured as well that if we delete that then it would affect those whose due dates is a day after the cut off which shouldn't be the case.

    I would greatly appreciate if you could tweak it a little that of which the formula would be able to detect the correct dates the claims should be reported.

    Thank you very very very much!
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: How to use dates as reference in allocation of a certain amount based on different %

    Modify the formula in cell BF13 to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag across and down.
    This corrects both the date in cell BH13 and the #REF error display is cell BL14.
    Let us know if you have any questions.

  10. #10
    Forum Contributor
    Join Date
    09-19-2017
    Location
    Taguig City, Philippines
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    120

    Re: How to use dates as reference in allocation of a certain amount based on different %

    Hi JeteMC, it worked well. Thank you so much for your time and effort!!! This is amazing

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: How to use dates as reference in allocation of a certain amount based on different %

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Need to find a negative amount and match it to the positve amount based on Acct# and Date
    By sbrandhorst in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-07-2016, 07:49 PM
  2. !! Help with huge amount of data - allocation
    By mariosmk555 in forum Excel General
    Replies: 3
    Last Post: 03-23-2014, 02:32 PM
  3. [SOLVED] Sum amount based on same reference id using VBA
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2013, 07:26 AM
  4. [SOLVED] Sum Amount of Each Year Based on Range of Dates
    By jaclrsen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2012, 08:14 AM
  5. Random allocation of amount
    By expert40 in forum Excel General
    Replies: 0
    Last Post: 02-12-2012, 08:44 AM
  6. Allocation Based on Date
    By Ada01 in forum Excel General
    Replies: 1
    Last Post: 10-16-2010, 02:19 PM
  7. [SOLVED] How can I calculate amount of time left based on amount spent?
    By KLD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2006, 11:25 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