How to create a table decreasing over 150 days

[Guest]

Hi,
My wife has decided to stop smoking before her first grand child is born -
this gives us approximately 150 days. We thought a useful technique might be
to gradually reduce the number of cigaretts per day over that period.
Currently she smokes 20 per day and we would want to reduce this to 0 over
the 150 day period (whether this will be successful or not we don't know). I
thought it would be useful to have a table that we could hang on the note
board scheduling the number of cigaretts permitted each day - perhaps even a
chart showing the decrease.

I use Excel in its basic form quite a bit but this is beyond me. Could some
kind person explaine how I would go about creating a sheet that would
autopmatically calculate the daily quantities. Also it might be good if the
amount of days could be changed and also the starting number of cigaretts per
day.

Many thanks,
Neil

 

[Stan Brown]

Sun, 22 Apr 2007 03:44:03 -0700 from Neil

My wife has decided to stop smoking before her first grand child is born -
this gives us approximately 150 days. We thought a useful technique might be
to gradually reduce the number of cigaretts per day over that period.
Currently she smokes 20 per day and we would want to reduce this to 0 over
the 150 day period (whether this will be successful or not we don't know). I
thought it would be useful to have a table that we could hang on the note
board scheduling the number of cigaretts permitted each day - perhaps even a
chart showing the decrease.

I use Excel in its basic form quite a bit but this is beyond me. Could some
kind person explaine how I would go about creating a sheet that would
autopmatically calculate the daily quantities. Also it might be good if the
amount of days could be changed and also the starting number of cigaretts per
day.

Put today's date in A1. In A2, put
=A1+1
Click A2 (only) and drag to fill through A150.

Assuming you want to decrease the same amount every day (rather than,
say, faster at the start and slower at the end), put this into cell
B2:
=floor(20-20*(A2-A$1)/149, 1)
Click in B2 and drag down to fill through B150, which should show 0.

"20" is the initial number of cigarettes, and "149" is the number of
days after the first day.

"A2-A$1" is the number of days since staring: note that $ sign. When
you drag the formula you'll see it change to A3-A$1, A4-A$1, etc.

"20-20*(A2-A$1)/150" is the number of cigarettes to smoke, on a
straight line basis. "floor ... ,1" eliminates the fractional part
and converts to a whole number. If you wanted to try to monitor
fractional cigarettes, omit the floor business and just put
=20-20*(A2-A$1)/150
in B2 before dragging to B150.

 

[Guest]

Sun, 22 Apr 2007 03:44:03 -0700 from Neil


Put today's date in A1. In A2, put
=A1+1
Click A2 (only) and drag to fill through A150.

Assuming you want to decrease the same amount every day (rather than,
say, faster at the start and slower at the end), put this into cell
B2:
=floor(20-20*(A2-A$1)/149, 1)
Click in B2 and drag down to fill through B150, which should show 0.

"20" is the initial number of cigarettes, and "149" is the number of
days after the first day.

"A2-A$1" is the number of days since staring: note that $ sign. When
you drag the formula you'll see it change to A3-A$1, A4-A$1, etc.

"20-20*(A2-A$1)/150" is the number of cigarettes to smoke, on a
straight line basis. "floor ... ,1" eliminates the fractional part
and converts to a whole number. If you wanted to try to monitor
fractional cigarettes, omit the floor business and just put
=20-20*(A2-A$1)/150
in B2 before dragging to B150.

Stan,
Thank_you_very_much! That was just what I needed and worked a treat. I
even managed to generate a bar chart to illustrate the decrease.

Again many thanks,
Neil

 

[Stan Brown]

Sun, 22 Apr 2007 11:20:02 -0700 from Neil

[solution, snipped]

Stan,
Thank_you_very_much! That was just what I needed and worked a treat. I
even managed to generate a bar chart to illustrate the decrease.

Happy to help! Thanks for letting us know.