Results 1 to 6 of 6

How to return a value based on percentage and date range

Threaded View

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

    How to return a value based on percentage and date range

    Hi,

    Need some help on what formula to use for my forecasting report.
    So I have a spreadsheet with the total amount of claim in column C. Part of this claim (based on percentage B3:B9) should be received on the date it falls due (based on claim due dates E14:K14).
    For example, on 28-Mar-2017 the Pre Construction claim falls due and thus 1% of $536,580 should be received (i.e $5365.80).
    Now, the question is where should the amount of $5365.80 reflect. As you can see, from columns L14:BK14 there are predefined dates. These dates corresponds to every Monday of the week for the whole year and is a week ending date for the purpose of my reporting.
    What I want to happen is for the $5365.80 to be shown under 03-Apr-2017 column because technically this date covers all claims received from 28-Mar-2017 to 03-Apr-2017.
    Then the next type of claim that would fall due would be the Council claim, and this is 4% of the total claim. With 4-April-2017 as due date, the $21,463.2 ($536580*4%) should then reflect under 10-Apr-2017 column since again this column corresponds to all amounts received from 04-Apr-2017 to 10-Apr-2017.
    And this goes on and on and so forth.
    Lastly, the week ending dates with no amounts to be received should reflect zero amount.

    So basically, the formula should be able to detect the percentage and date range for which the type of claim falls due.

    I hope I have explained it clearly.

    Attached is the file with the data and expected results (in red font color).

    Thank you so much!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return percentage based on range of cells from two columns
    By Lillerpool in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-15-2015, 08:27 AM
  2. Check a date range and return value based on the date range
    By KeithCar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2015, 12:55 PM
  3. [SOLVED] Return value based on ID and Date Range
    By just2cruz in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-28-2012, 07:15 PM
  4. Return 1 or 0 based on Date Range
    By kwjoh in forum Excel General
    Replies: 8
    Last Post: 03-04-2011, 10:55 PM
  5. Excel 2007 : Want to return a value based on date range
    By mlittle74 in forum Excel General
    Replies: 2
    Last Post: 04-03-2010, 01:23 PM
  6. Compare Dates To Date Range & Return Date Based On Outcome
    By gusmontoya in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2008, 09:06 PM
  7. [SOLVED] Return Range of Numerical Values in Single Column based on Frequency Percentage
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-28-2005, 06:05 PM

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