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

Thanks for an excellently written post, with plenty of detail!

In AP2:
=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK$-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))

Note that this formula does reference AO, but that is only to avoid a
circular reference. You should be able to copy this formula across and down
as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

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

Dear Luke,

Thanks and thank you for your kind words.

I don't actually have time to try your formula just now but at a quick look
I am sure it will be just what I need.

Thanks again!

Dave M

"Luke M" wrote:

Thanks for an excellently written post, with plenty of detail!

In AP2:
=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK$-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))

Note that this formula does reference AO, but that is only to avoid a
circular reference. You should be able to copy this formula across and down
as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

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

Acually Luke, I did just try testing this very quickly just now but Excel
reports an error.....would you mind taking a quick moment to double check if
there is an obvious error you see? Lots of parentheses in there....I really
don't have time just now to give it a closer look.

Best wishes,

Dave M

"Luke M" wrote:

Thanks for an excellently written post, with plenty of detail!

In AP2:
=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK$-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))

Note that this formula does reference AO, but that is only to avoid a
circular reference. You should be able to copy this formula across and down
as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

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

Argh, I change $AK2 to $AK$. This should work now, sorry about that.

=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK2-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

Acually Luke, I did just try testing this very quickly just now but Excel
reports an error.....would you mind taking a quick moment to double check if
there is an obvious error you see? Lots of parentheses in there....I really
don't have time just now to give it a closer look.

Best wishes,

Dave M

"Luke M" wrote:

Thanks for an excellently written post, with plenty of detail!

In AP2:
=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK$-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))

Note that this formula does reference AO, but that is only to avoid a
circular reference. You should be able to copy this formula across and down
as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

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

Just noticed that you did say if it was last day of year, to still count it
as 1 day, thus need to subtract today from 366. Do note that this will be
'slightly' off if the current year is a leap year.

=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK2-AP$1+1))*((366-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

Argh, I change $AK2 to $AK$. This should work now, sorry about that.

=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK2-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

Acually Luke, I did just try testing this very quickly just now but Excel
reports an error.....would you mind taking a quick moment to double check if
there is an obvious error you see? Lots of parentheses in there....I really
don't have time just now to give it a closer look.

Best wishes,

Dave M

"Luke M" wrote:

Thanks for an excellently written post, with plenty of detail!

In AP2:
=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK$-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))

Note that this formula does reference AO, but that is only to avoid a
circular reference. You should be able to copy this formula across and down
as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

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

When Excel reported the error, didn't it highlight the place which Luke
referred to in his subsequent message?
Usually Excel does its best to highlight where it thinks the problem is.
Sometimes, of course, it isn't possible for Excel to tell what was intended.
--
David Biddulph

Dave_in_gva wrote:
Acually Luke, I did just try testing this very quickly just now but
Excel reports an error.....would you mind taking a quick moment to
double check if there is an obvious error you see? Lots of
parentheses in there....I really don't have time just now to give it
a closer look.

Best wishes,

Dave M

"Luke M" wrote:

Thanks for an excellently written post, with plenty of detail!

In AP2:
=IF(OR(AP$1<$AJ2,AP$1$AK2),"",IF(YEAR(TODAY())AP $1,0,IF(YEAR(TODAY())=AP1,($B2/($AK$-AP$1+1))*((365-DAY(TODAY()))/365),($B2-SUM($AO2:AO2))/($AK2-AP$1+1))))

Note that this formula does reference AO, but that is only to avoid a
circular reference. You should be able to copy this formula across
and down as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dave_in_gva" wrote:

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

David, yes I was aware of this in Excel. Very helpful for people like me :~)
but I simply didn't have the time yesterday to look into it closely.

Luke, thanks again. I did have some abberant behaviour due to automatic
refreshing but I've simply set up your formula in a separate workbook linked
to my original data and it seems to work perfectly.

Best,

David