| | | Square Peg |  |
|  Posted: Wed Aug 20, 2008 6:13 pm Post subject: Suggestions for conditional calculations |  |
| |  | |
I have a spreadsheet to keep track of hard disk defrags and help me
estimate when the next one might be needed.
The table looks like this (some rows and columns are not shown):
B D E G H I
3
4 Defrag threshhold: 15% (Name: DefragThresh)
5
6 Date #Days Frag% #D2T Ave Next
7 5/30/08 45 40% 23 38 7/06/08
8 4/15/08 30 10% 60 45 5/30/08
9 3/16/08 30 20% 30 30 4/15/08
10 2/15/08 -- 14% n/a n/a n/a
The table is in reverse chronological order so I don't have to scroll
down once it gets more than a page long.
I am using the OFFSET function for all day-to-day computations. This
allows me to insert rows pretty much anywhere, even at the top, and
not mess up the calculations. I named cell D4 (NumDays1) to establish
the last cell for the AVERAGE function. In case anyone is interested,
the formulas are:
D7: =B7-OFFSET(B7,1,0)
G7: =D7*(DefragThresh/E7)
H7: =AVERAGE(G7:NumDays1)
I7: =B7+H7
This all works.
The reason for this post if that I have run into a problem. Sometimes
the analyze function shows a low frag % (less than 15%) and does not
advise a defrag. I would like to log these events and have the
spreadsheet update the estimate for when the defrag should be done s I
can reset the reminder. I am not sure how to do this.
If I add a new row, I need to adjust the formulas, because they
currently assume that the previous row was a defrag and the
fragmentation % was zero.
Does anyone have an elegant suggestion for how to add this
functionality? |
|
