1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help from Excel gurus required

Discussion in 'HTML, Graphics & Programming' started by bainbridge, Jun 22, 2018.

  1. bainbridge

    Mobster

    Joined: Dec 9, 2009

    Posts: 2,914

    Location: Bristol

    You know who you are, one of those people who can just whip up a formula in next to no time that works everything out and displays the correct info with next to no effort. Blooming witchcraft if you ask me, but anyway please can you help.

    We're trying to limit our exposure to debt at work and I've come up with the following solution which we're going to trial but I've quickly become out of my depth when setting up a spreadsheet for a few of our guys to use to see if the solution is any good.

    This is the challenge:

    A customer paying by direct debit wants to amend their annual contract mid term, at the moment any additional cost is added to the remaining dd plan, which often works fine but in many cases people who have a large amount added towards the end of their contract end up defaulting and their contract is cancelled as a result, leaving a large debt which we then need to chase.

    Here's the solution I want to trial:

    Contract is less than 60 days old = We take 15% of the AP on a card and remainder goes to dd
    60 -120 days old = 30% on card and remainder to dd
    120 - 180 days old = 40% on card and remainder to dd
    180 days plus = 50% on card and remainder to dd

    Now here's where I need help. I have started a simple spreadsheet in which the user enters the contract expiry date, then the date of the amendment and the remaining number of days is displayed. I then have another cell where the user enters the AP but I need excel to take the days remaining into account, apply the relevant percentage and display the deposit amount which needs to be taken from the customer on a card.

    If any excel sorcerers aren't busy daubing themselves in chicken blood I'd really appreciate some help.

    Thanks
     
  2. fretted

    Hitman

    Joined: Jan 4, 2010

    Posts: 524

    You can just subtract one date from another to get you your number of days and then use a nested IF statement to work out the AP split.
     
  3. sigma

    Suspended

    Joined: Nov 13, 2006

    Posts: 16,137

    An IF statement should be able to handle this.

    I could have a look for you tomorrow evening if you drop me a PM. I'm sure someone will sort it for you by then though.

    You already have the logic that you require. If you want to work out how to do it yourself learn about IF statements and then just define the logic in a lookup table (which you read by using the VLookup formula). Plenty of YouTube videos on Excel basics out there.

    I'd be happy to talk you through it if nobody else does.
     
  4. mattx2

    Wise Guy

    Joined: Jul 21, 2005

    Posts: 1,045

    Location: New York

    change cell A1 to the day count

    =IF(A1<=60,0.15,IF(AND(A1>60,A1<=120),0.3,IF(AND(A1>120,A1<=180),0.4,IF(A1>180,0.5,"Error"))))
     
  5. sigma

    Suspended

    Joined: Nov 13, 2006

    Posts: 16,137

    I'd prefer not to hard code the percentages, but I guess the way shown above is the quickest way to do it. Removes the need for a vlookup.
     
  6. GSVBagpuss

    Wise Guy

    Joined: Jun 11, 2013

    Posts: 2,445

    Put the percentages in some other cells / a spare sheet and reference them
     
  7. wesimmo

    Wise Guy

    Joined: Mar 19, 2012

    Posts: 1,540

    Yep, use a VLOOKUP and a reference table, I hate having hard coded values in formulas.
     
  8. bainbridge

    Mobster

    Joined: Dec 9, 2009

    Posts: 2,914

    Location: Bristol

    Thanks folks all your responses are much appreciated.

    I'll learn about IF statements, make one and nest it.

    There are also a few other tweaks I need to make but I need to learn more about excel without having to keep asking people.

    Thanks again.

    Ps Sigma thanks for your kind offer.