Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decreasing # to stop decreasing when <=0 | Excel Worksheet Functions | |||
adding decreasing numbers | Excel Discussion (Misc queries) | |||
Solver Decreasing | Excel Discussion (Misc queries) | |||
How do i create a countdown by days? | Excel Discussion (Misc queries) | |||
I need to create pivot table for the month but data in days | Excel Discussion (Misc queries) |