please help with my spreadsheet Solved/Closed

Question

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.

venkat1926 · Answer

course starts on 12/12/2012 (these are in A1 and B1)

from A4 down you can have the date of payment as following. dates fictitious

"date of payment of fees" this is in A4
11/1/2012 this is in A5
11/3/2012 this is in A6
11/5/2012 so on
11/7/2012
11/9/2012
11/11/2012
11/13/2012
11/15/2012
11/17/2012
11/19/2012
11/21/2012
11/23/2012
11/25/2012
11/27/2012
11/29/2012
12/1/2012
12/3/2012
12/5/2012
12/7/2012
12/9/2012
12/11/2012

in B5 copy paste this formula
=$B$1-A5 (note the dollar signs)
copy B5 down as long as data is there
format column B as numbers with no decimal places

in C5 copy paste this fomula

=IF(B5<7,0,IF(AND(B5>=7,B5<13),0.05,IF(AND(B5>=13,B5<21),0.08,IF(B5>=21,0.1,""))))

copy C5 down as long as data is there.

format column C as percentage with no decimal places

column B from B4 down will be like this

"no. of days before start day" this will be in B4
41 this will bein B5
39
37
35
33
31
29
27
25
23
21
19
17
15
13
11
9
7
5
3
1

column C form C4 down will be like this

discount
10%
10%
10%
10%
10%
10%
10%
10%
10%
10%
10%
8%
8%
8%
8%
5%
5%
5%
0%
0%
0%

if ou still have problem send an eamil through administrator I shall send the file.

chicky · Answer

thanks a bunch, took some time but i finally have it done, i appreciate your help, your an asset to all that have no clue :)

Please help with my spreadsheet

2 responses

Similar discussions

Subscribe To Our Newsletter!