1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rebates paid early or late - how many days?

Discussion in 'Ask an Excel Question' started by colans, Jan 11, 2018.

  1. colans

    colans New Member

    Messages:
    24
    I've attached a workbook containing data and pivots. My organization is trying to identify how much we could have saved (if anything) by paying customer rebates according to the contract rather than paying earlier (or later) than contract terms. I show in the pivots that we would have paid close to $15M less in 2017. The next question being asked is to show how much was paid 1 day early, 5 days early, 20 days late etc.
    The catch is, even if it was paid 1 day early but falls within the same month, it shouldn't be counted.
    Hope this makes sense. Any help is greatly appreciated!

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    718
    Hi colans,
    I have some trouble understanding your data. Is this a correct reading:
    - Act Days Paid seems to be the difference between Receive Date and Paid Date?
    - The Pay Day is the term you need to respect?
    - If Act Days Paid < Pay day, you paid too early and this is the data you are after?
    - If this difference is 1 day and falls in same month then should not be in scope.
    - Else you want to see this data grouped in kind of buckets (1 d; 5d; 20d)
    - I do not need to consider all columns with ACT and New in the labels.

    Attached Files:

    Last edited: Jan 11, 2018
    Thomas Kuriakose likes this.
  3. colans

    colans New Member

    Messages:
    24
    yes, that is correct
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    718
    Then I believe the file I attached contains a possible solution.
    I now added a pivot. You can adapt the range "buckets" as you need them to be.

    Attached Files:

  5. colans

    colans New Member

    Messages:
    24
    Your solution is not exact but has given me some ideas to work with, thank you!
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    718
    Still mission accomplished then ;-). Always difficult to handle and understand requirements... I'm glad that I helped somehow.

Share This Page