Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 173
Default How to create a table decreasing over 150 days

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 524
Default How to create a table decreasing over 150 days

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.


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 173
Default How to create a table decreasing over 150 days



"Stan Brown" wrote:

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.


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 524
Default How to create a table decreasing over 150 days

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


"Stan Brown" wrote:

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.


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

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
decreasing # to stop decreasing when <=0 Ken Excel Worksheet Functions 1 March 4th 07 08:02 AM
adding decreasing numbers Tracker Excel Discussion (Misc queries) 4 January 17th 07 02:45 AM
Solver Decreasing Walid M. Bahgat Excel Discussion (Misc queries) 1 December 3rd 06 07:38 PM
How do i create a countdown by days? patti Excel Discussion (Misc queries) 1 June 5th 06 02:22 PM
I need to create pivot table for the month but data in days Mikeyh133 Excel Discussion (Misc queries) 2 December 31st 04 08:53 PM


All times are GMT +1. The time now is 12:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"