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