D

#### Dave_in_gva

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