# Help from Excel gurus required

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

1. bainbridge

# 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

# 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

# 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

# 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

# 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

# Posts: 2,445

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

7. wesimmo

# Posts: 1,540

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

8. bainbridge

# 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.