Thread: calculating discounts in different cells

Hello,
I am having a hard time trying to figure out how to format my spreadsheet so that i can make it a template that i can use in future, this is what i have to work with, a course being offered at the local tafe is due to start on 12/12/2012 (hypothetically), if the students pay within 7 days of the course starting that get no discount, 7-13 days they get a 5% discount, 14 - 20 days the get a 8% discount and 21 days or more they get a 10% discount.
i need to create and enter formulas that will calculate the number of days paid in advance, the discount they are entitled to and the course fee paid. i hope some one understands what i have just written like i said i am desperate i feel like my brain is going to explode, i am studying business studies and i have answered every other question but i just cant for the life of me figure this out and its a stand alone task meaning there is no text available for this, i should add i have recently given birth and i missed a few weeks of class so im guessing this is one of the things i missed. thankyou for your time you will never know how very greatfull i am.

i think i worded this wrong, I know the formula for example 18/10/2009-15/01/2009= 3 and i know how to work out the total cost ie if its supposed to be course cost $100 - 10% = $90 but what i dont know how to do is work out how to make the number of days that someone has paid in advance so in my example the number 3, find the cell that states 3 days in advance would be 0%. im not asking for someone to do my homework lol im asking someone to guide me in the right direction so i can do my "homework" i dont have to return this for marking but im sure when my exams come around there will be a question along these lines

As a general rule we don't like to be seen to be doing homework but I will offer some pointers

First you need to store 2 dates the first being the course start date (as implied already) and the second being the payment date.
Dates in XL are integers so you can subtract one from the other to establish the days difference.
Based on the days difference you can then use a LOOKUP (or VLOOKUP if preferred with range_lookup set to TRUE or 1 or omitted altogether) to calculate the discount % based on the days difference.
You can then multiply this 1-% by the cost to get your final value.

Well, I think I did give you some pointers on all fronts...

Check out VLOOKUP and/or LOOKUP in XL Help.

You can build a table with your day boundaries and associated % and "lookup" the day difference in this table to retrieve the appropriate %.

If you use a range_lookup of TRUE/1 (or omit altogether given it is default) in the VLOOKUP you need only store the low boundaries in Asc order, ie 0, 7, 14, 21 and for each of those you assign the appropriate discount in adjacent column (0%, 5%, 8% etc...)

Once you have that table you can retrieve the rate using the days difference

VLOOKUP(days difference value, your table, 2, TRUE)

the days difference calc you know how to do... once you have the above discount % the rest is pretty much just the basic math which as you said you know how to do.

Thankyou so much thats exactly what i was trying to figure out, if we were in person i probably could have worded that so you didnt have to waste your time with the first jumble i wrote lol. cheers for the help. if i may ask just one more question, i went to put the numbers in for the days and i have no idea what i have done but i type in 7 and press enter and it displays 0 any idea what thats about (i think this is a frustration brain freeze aghh) never mind figured it out lol