-
Estimate End Result
Typically when I post Q's the subject is work related. I have a personal mission to lose weight and I figured I would use Excel to help me chart my progress.
In sheet1 column A is a list of dates, column B is scale reading based on date, and column C is a static "end result" also known my wish.
Example -
4/15/2005 225 190
4/16/2005 225 190
4/17/2005 224 190
4/18/2005 224 190
4/19/2005 224 190
4/20/2005 223 190
4/21/2005 223 190
What I was looking for is Excel to estimate (what day) I would reach my end result with an expanding range by daily input.
I was thinking of using something like an =average formula but haven't come up with anything solid yet.
Any idea's would be greatly appreciated.
Thanks,
Kurt
-
Since a weight-loss curve is non-linear, you might want to use something like a 10-day moving average to use as your loss rate figure. You lose faster in the early days, then it slows down, and if you are persistent, you will probably reach a plateau of no change for a week or so, then it will pick up again. Stick with it!
-
You should create a chart to plot the values for each date. Then add a Trendline which will give you a better idea of where you will be in the future.
-
Here?s what I think will give you the projected number of days it will take to lose the remaining weight as of today. I?m assuming that column ?A? contains the dates, and column ?B? contains your weights on each date, so the bottom row contains today?s date and today?s weight.
Calculate the pounds you still have to lose as of today:
(Formula A)
Code:
= (INDIRECT("B"&ROW()) - 190) (=lbs )
Calculate your average weight for the last 10 days:
(Formula B)
Code:
= AVERAGE(INDIRECT("B"&ROW()-9):INDIRECT("B"&ROW()) (= lbs)
Calculate your average weight loss for the last 10 days:
(Formula C)
Code:
= (Formula B) ? (INDIRECT("B"&ROW())) (= lbs)
Calculate your average weight loss per day during the last 10 days:
Code:
(Formula D) = (Formula C) / 10 (= lbs/day)
Calculate how many days it will take from today to lose the remaining weight:
Code:
= (Formula A) / (Formula D) (=lbs/(lbs/day)=days)
I think I?ve copied this correctly from my worksheet, but be VERY careful to put you parentheses in the correct locations.
If it works, then use column ?D? to put this formula, and each day copy it down to the next row and it should give you a revised projection. :friends:
-
Cyberdude and DRJ -
Thanks for your input. Will be trying it out tomorrow.
Kurt
-
Cyberdude ?
Again, thanks for your post. I need a little hand holding.
Your assumption(s) are correct:
I?m assuming that column ?A? contains the dates, and column ?B? contains your weights on each date, so the bottom row contains today?s date and today?s weight.
Now for the rest of your post. I have some questions:
1. In what cells on your sheet did you put each formula?
2. I copied your Formula A and put it into column c. The formula would not work with the (=lbs). I copied the formula down and it gave me the weight ? 190 for each line.
3. I copied Formula B and put it into column D. It does not like the (=lbs) either. When I take it out I get REF error in cell.
Calculate the pounds you still have to lose as of today:
(Formula A) = (INDIRECT("B"&ROW()) - 190) (=lbs )
Calculate your average weight for the last 10 days:
(Formula B) = AVERAGE(INDIRECT("B"&ROW()-9):INDIRECT("B"&ROW()) (= lbs)
Calculate your average weight loss for the last 10 days:
(Formula C) = (Formula B) ? (INDIRECT("B"&ROW())) (= lbs)
Calculate your average weight loss per day during the last 10 days:
(Formula D) = (Formula C) / 10 (= lbs/day)
Calculate how many days it will take from today to lose the remaining weight:
= (Formula A) / (Formula D) (=lbs/(lbs/day)=days)
I think I?ve copied this correctly from my worksheet, but be VERY careful to put you parentheses in the correct locations.
If it works, then use column ?D? to put this formula, and each day copy it down to the next row and it should give you a revised projection.
I need some help understanding what I am doing wrong. Can you assist?
Thanks,
Kurt
-
Hi, Kurt!
It appears that in my zeal to explain that I have confused you. So begin by erasing every occurrence of something that looks like ( = lbs). That's the engineer in me coming out. I was trying to show you the units of the results of each intermediate formula, something called dimensional analysis. It helps me to know when I should divide or multiply two numbers. But those comments are NOT part of the formula! The eventual number that you want has the units of "days", or more specifically, "days until you reach your weight goal".
You just need the final version of the formula. I was trying to show you how to combine several preliminary formulas together into one final formula, which will be VERY complicated looking. That's why I didn't write out the final version.
But perhaps to start out with it might be a good idea to keep the preliminary parts of the formula into separate columns and see if we can make it work that way.
Put all the following stuff on the bottom row for your data. I'll refer to it as row 10, which should correspond to the last entry you made in Columns "A" and "B".
Start with Formula "B". Put it into column "D", then put Formula "C" into column "E", then Formula "D" into column "F", and the final formula into column "G". Please omit anything that looks like (= lbs...).
Formula "B" in column "D" should be OK without any modification.
Formula "C" in column "E" must be changed. Where it shows "(Formula B)", substitute $D10.
In Formula "D" in column "F", remove the "(Formula C)" and substitute $E10.
In column "G" write the following formula:
=(INDIRECT("B"&ROW()) - 190) / $D10
I think I've done this correctly. You won't have Formula "A" in a separate column, since I've included it as the first part of the final formula. In case you don't understand, the term "Row()" means whatever row this formula is written on, which in this case is row 10.
If I did this all correctly, then I would expect the value you see in column "G" to be between 200 and 300 days.
Now, assuming it works, each day you must copy the formulas on row 10 to the next row down. When you do the copy, the row number should automatically change to the next higher row value.
It will be much easier if you can combine all the formulas into one formula, then you just have to copy one formula down to the next row each day.
You can also write a macro to do the copying for you, or you can rewrite the final formula to to refer to the "CurrRow" (my name for it, not VBA's) instead of a specific row, like 10. While you are trying to accomplish what I outlined abve, I'll try to put together something that will make the change each day easier.
I hope this helps some.
-
Cyberdude -
Thanks for the response back. One can never assume they know it all so I looked at (=lbs) as something I have never seen yet willing to give it a try. Hell, always willing to learn.
2 issues I had problems with:
1. The -9 in Formula B is what was throwing me off. I had dates through 8, hence the #REF! error.
2. Your statement that "If it works, then use column ?D? to put this formula"
I kept beating myself up so much so that I had to take a mental break.
I overcame the situation by Column C part of the formula, Column D part of the formula, etc. thus end result being in column G.
I was proud as a peacock so I was going to post that you could stand down - dummy (Me) figured it out, but you had already posted your novel on Formula Hand Holding.
I appreciate your time and effort.
Thank You,:friends:
Kurt
P.S. current day end result is 192 days:)
-
Here's a procedure that will copy the formulas to the next row down, then remove the formulas from the original row:
Code:
Sub CopyFormula()
Dim CurrRow As Integer
'This macro will copy the formulas in cols "D" thru "G" to the next row
'down, then it will erase the original formula.
'Use the column (if not "G") that has your formula. The starting row
'does not have to be "1", but it must be one that contains a value,
' and all rows in col "G following it must have a value down to the row
' containing the formula.
Range("G1").Select 'Select a row somewhere above the formula row
Selection.End(xlDown).Select 'Move down to the formula row
CurrRow = ActiveCell.Row 'Save the formula row number
'The next command selects cols "D" thru "G" on the formula row
Range("D" & CurrRow & ":G" & CurrRow).Select
'To copy just col "G", uncomment the next command line, and
' remove the command above that copies cols "D" thru "G"
'Range("G" & CurrRow).Select
Selection.Copy 'Copy the contents of the selected row & columns
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste 'Copy formula and value to new cell(s)
'Remove the next two commands if you don't want
'to remove the formula from the previous row.
ActiveCell.Offset(-1, 0).Select 'Move to the original row
Selection.PasteSpecial Paste:=xlValues 'Remove formula from orig cell(s)
End Sub
I tested this procedure and it worked OK.
I forgot to mention that my formula is calculating a 10-day moving average, that's why I used the 9 where you saw it. You can reduce or increase that value. If you want to use, say, a 5-day average, change the 9 to 4, and the 10 in col "G" to 5. I suspect, that once you have enough data, you will want to use a 10 to 20 day average. It's not critical. If it's too small, it's worthless, and too large may be less accurate in forcasting. You can play with it.
-
Cyberdude -
Your help is much appreciate.
Again - Thanks,
Kurt
