D
Dave_in_gva
Hello everyone,
My apologies in advance at the length of this post, but I have what is
hopefully an interesting and challenging problem to solve.
I use a web based resource (www.cellartracker.com) to manage my wine cellar.
As an aside, this resource was developed by a Microsoft engineer/wine lover
whilst on sabbatical and is well worth looking into for anyone who enjoys
wine.
Users log into and manage their own cellars, and at the time of adding a new
wine to their cellar (or as a modification one can make at any time for a
given wine), the user can specify a value for Begin and a value for End,
where Begin is the year when a wine should first be opened and drunk and End
the year by which a user should have consumed one's stock of a particular
wine. So, a wine that should best remain untouched in one's cellar until the
year 2015 and consumed over the next 10 years would have a value of 2015
entered for Begin, and 2025 entered for End when managing one's cellar on the
CellarTracker website. The timeframe 2015-2025 would be referred to as this
wine's 'drinking window'.
CellarTracker allows users to download their cellar data to Excel. I have
been working with this file to add a capability and I am close but not quite
there.
The capability I'd like to add is date sensitive calculation of numbers of
bottles in my cellar that are currently in, or will be in, their 'drinking
window' over the time period 2008-2040.
The Excel file generated through the CellarTracker site provides a worksheet
called Wine List, where each row holds data on a particular wine. On this
worksheet row 1 is a header row where 3 variables (quantity, begin and end)
are relevant. Briefly, more detail on these is:
Quantity (Column B. Values are positive, non-zero integers corresponding to
number of bottles of the corresponding wine still in the cellar).
Begin (Column AJ. As above, values are integers corresponding to the year
during and after which a wine should be opened. Valid values could occur in
the past - for example a wine from 1990 might easily have had a value of 2000
entered in the Begin field and still continue being cellared as it may have a
20 year drinking window, meaning its corresponding value for the End column
would be 2020. Conversely, an entry in the Begin column could take place in
the future - a 2005 wine may well have a value for Begin of 2015).
End (Column AK. As above, values are integers corresponding to the year by
which, and certainly during which, any remaining bottles of a given wine
should be consumed. Values in the past would technically be valid, but would
imply a wine was being held in stock past its drinking window as the Wine
List worksheet only provides data on wines where Quantity is greater than
zero).
My approach so far has been to add a calendar year range in the blank area
of the worksheet to the right of the data on row 1. So, beginning on row 1 in
column AP I have 2008, in column AQ I have 2009 etc. up to the year 2040
which occurs in cell BV1.
Immediately underneath the entry of 2008 (e.g. cell location AP2) I have
placed the following formula, and populated this across to column BV and down
to row 5000 (I don't anticipate having more than 5000 unique wines entered
into my wine cellar over my lifetime):
=IF(AND( ( AP$1 >= $AJ2), (AP$1<= $AK2) ),(IF($AP$1>$AJ2,
($B2/($AK2-$AP$1+1)),($B2/($AK2-$AJ2+1)))),"")
This formula returns the number of bottles on hand evenly divided across the
drinking window, or leaves the cell empty if a wine is not yet in its
drinking window or past it.
What I would like to improve on is that the above formula does not take into
account today's date and where one is in the calendar year. For example, if I
had a 6 bottles of a wine where Begin equalled 2008 and End equalled 2010
(i.e. a drinking window of 2008-2010), what I would like the formula to
return if the formula and data were refreshed and the date was Jan. 1, 2009
would be:
2008 Empty cell
2009 3
2010 3
2011 Empty cell
(columns here and below transposed as rows for clarity)
What I would like the formula to return when it was refreshed if I still had
6 bottles of that wine left and the date was Dec. 31, 2009 would be:
2008 Empty cell
2009 .0082
2010 5.9918
2011 Empty cell
Above, the value of .0082 is derived by my using 1/365 (one of 365 days
would be remaining in 2009 on December 31) multiplied by 3 (6 bottles on hand
divided by the 2 calendar years - 2009 and 2010 - still not completely
elapsed in the drinking window at the time the calculation is refreshed).
Instead, what the formula I am using at present is returning irrespective of
when the value is refreshed is:
2008 2
2009 2
2010 2
2011 Empty cell
So, clearly showing 2 bottles as available in the year 2008 makes no sense
as this calendar year is now completely elapsed, and the calculation is
simply evenly dividing the quantity of a wine across the drinking window
without account of today's date.
I know that there are ways of incorporating today's date into worksheet
functions but I expect what would take me another week of trial, error and
reading is something that any number of you could show me quite easily.
What I am working towards is a formula I can set and forget - ideally on a
separate worksheet from the current Wine List worksheet I have been working
on. For info I would like to keep the column headings of 2008 through 2040 as
I am also monitoring consumption (with a formula that does work) and I wish
to automatically chart the time period 2008-2040 and see both my present and
historical consumption as well as the availability of wines I have on hand
and when they will be coming into their drinking window over the future out
to 2040.
Sorry for the length of this post, but I thought the detail would be helpful
(and hopefully interesting). Very grateful to hear any and all input,
Dave M
My apologies in advance at the length of this post, but I have what is
hopefully an interesting and challenging problem to solve.
I use a web based resource (www.cellartracker.com) to manage my wine cellar.
As an aside, this resource was developed by a Microsoft engineer/wine lover
whilst on sabbatical and is well worth looking into for anyone who enjoys
wine.
Users log into and manage their own cellars, and at the time of adding a new
wine to their cellar (or as a modification one can make at any time for a
given wine), the user can specify a value for Begin and a value for End,
where Begin is the year when a wine should first be opened and drunk and End
the year by which a user should have consumed one's stock of a particular
wine. So, a wine that should best remain untouched in one's cellar until the
year 2015 and consumed over the next 10 years would have a value of 2015
entered for Begin, and 2025 entered for End when managing one's cellar on the
CellarTracker website. The timeframe 2015-2025 would be referred to as this
wine's 'drinking window'.
CellarTracker allows users to download their cellar data to Excel. I have
been working with this file to add a capability and I am close but not quite
there.
The capability I'd like to add is date sensitive calculation of numbers of
bottles in my cellar that are currently in, or will be in, their 'drinking
window' over the time period 2008-2040.
The Excel file generated through the CellarTracker site provides a worksheet
called Wine List, where each row holds data on a particular wine. On this
worksheet row 1 is a header row where 3 variables (quantity, begin and end)
are relevant. Briefly, more detail on these is:
Quantity (Column B. Values are positive, non-zero integers corresponding to
number of bottles of the corresponding wine still in the cellar).
Begin (Column AJ. As above, values are integers corresponding to the year
during and after which a wine should be opened. Valid values could occur in
the past - for example a wine from 1990 might easily have had a value of 2000
entered in the Begin field and still continue being cellared as it may have a
20 year drinking window, meaning its corresponding value for the End column
would be 2020. Conversely, an entry in the Begin column could take place in
the future - a 2005 wine may well have a value for Begin of 2015).
End (Column AK. As above, values are integers corresponding to the year by
which, and certainly during which, any remaining bottles of a given wine
should be consumed. Values in the past would technically be valid, but would
imply a wine was being held in stock past its drinking window as the Wine
List worksheet only provides data on wines where Quantity is greater than
zero).
My approach so far has been to add a calendar year range in the blank area
of the worksheet to the right of the data on row 1. So, beginning on row 1 in
column AP I have 2008, in column AQ I have 2009 etc. up to the year 2040
which occurs in cell BV1.
Immediately underneath the entry of 2008 (e.g. cell location AP2) I have
placed the following formula, and populated this across to column BV and down
to row 5000 (I don't anticipate having more than 5000 unique wines entered
into my wine cellar over my lifetime):
=IF(AND( ( AP$1 >= $AJ2), (AP$1<= $AK2) ),(IF($AP$1>$AJ2,
($B2/($AK2-$AP$1+1)),($B2/($AK2-$AJ2+1)))),"")
This formula returns the number of bottles on hand evenly divided across the
drinking window, or leaves the cell empty if a wine is not yet in its
drinking window or past it.
What I would like to improve on is that the above formula does not take into
account today's date and where one is in the calendar year. For example, if I
had a 6 bottles of a wine where Begin equalled 2008 and End equalled 2010
(i.e. a drinking window of 2008-2010), what I would like the formula to
return if the formula and data were refreshed and the date was Jan. 1, 2009
would be:
2008 Empty cell
2009 3
2010 3
2011 Empty cell
(columns here and below transposed as rows for clarity)
What I would like the formula to return when it was refreshed if I still had
6 bottles of that wine left and the date was Dec. 31, 2009 would be:
2008 Empty cell
2009 .0082
2010 5.9918
2011 Empty cell
Above, the value of .0082 is derived by my using 1/365 (one of 365 days
would be remaining in 2009 on December 31) multiplied by 3 (6 bottles on hand
divided by the 2 calendar years - 2009 and 2010 - still not completely
elapsed in the drinking window at the time the calculation is refreshed).
Instead, what the formula I am using at present is returning irrespective of
when the value is refreshed is:
2008 2
2009 2
2010 2
2011 Empty cell
So, clearly showing 2 bottles as available in the year 2008 makes no sense
as this calendar year is now completely elapsed, and the calculation is
simply evenly dividing the quantity of a wine across the drinking window
without account of today's date.
I know that there are ways of incorporating today's date into worksheet
functions but I expect what would take me another week of trial, error and
reading is something that any number of you could show me quite easily.
What I am working towards is a formula I can set and forget - ideally on a
separate worksheet from the current Wine List worksheet I have been working
on. For info I would like to keep the column headings of 2008 through 2040 as
I am also monitoring consumption (with a formula that does work) and I wish
to automatically chart the time period 2008-2040 and see both my present and
historical consumption as well as the availability of wines I have on hand
and when they will be coming into their drinking window over the future out
to 2040.
Sorry for the length of this post, but I thought the detail would be helpful
(and hopefully interesting). Very grateful to hear any and all input,
Dave M