bobthedino83
New Member
- Joined
- Oct 16, 2021
- Messages
- 5
- Office Version
- 2019
- Platform
- Windows
Hi, everyone. Newbie here.
I've been using excel for 25+years since high school but I'm by no means a pro and this particular problem has got me stumped.
Context: (not important, so you can skip to problem below)
This is for a wine cellar. There's an app/website called Cellartracker(.com) that I use for cellar management. It's very powerful, if you like excel you'd like this platform.
It has a feature (which is why I started using it) called "ready to drink" whereby it outputs a "score" for each wine in your cellar which ranks that wine relative to all the other wines in your cellar in terms of what you should drink first.
The exact working of and rationale for this formula is contentious, but it's super useful when one has 1,000 bottles in the cellar and wants to know what to drink next. Most importantly it helps to avoid a disaster where bottles end up going off in one's cellar because they weren't consumed within their drinking window.
What it can't avoid though is what might be called a "wine tsunami". Which is when a lot of the bottles in a cellar hit their final year(s) all at the same time.
This is a real thing as people buy wines with the intention of drinking them in 5 or 10 or 15 years' time. Or one can buy a case of wine with the intention of drinking the 6 bottles over 10 years, starting in 5 years' time, for instance.
Problem: (note that wine and bottles are two different things)
Each wine has a drinking window, eg. 2019 - 2025
Each wine has a quantity of bottles present in your cellar, eg. 1, or 6, or 24, whatever
I want to spread the quantity of bottles for a wine across the remaining years left in the drinking window for that wine. This spread needs to be more or less even...
Simply dividing the number of bottles left into the remaining years will give me a fraction in some cases, which isn't going to work because I want to graph the bottles that are "scheduled" for drinking for each year for the next decade and you can't drink a fraction of a bottle (with a coravin, maybe, but I don't wanna).
If I were doing this by hand the columns would look like this:
Note one of the things I'd like to do is avoid drinking a bottle during the last year of it's drinking window, I've applied this rule to the example above.
With the wine in row 2 the solution could be some IF functions, but the problem for me comes in when there's fewer bottles than years, then I don't know how to get it to skip a year or two as in row 3....
Any advice on which formulas to use or if this requires macros (which I'm not really familiar with) would be greatly appreciated.
Thanks in advance!
I've been using excel for 25+years since high school but I'm by no means a pro and this particular problem has got me stumped.
Context: (not important, so you can skip to problem below)
This is for a wine cellar. There's an app/website called Cellartracker(.com) that I use for cellar management. It's very powerful, if you like excel you'd like this platform.
It has a feature (which is why I started using it) called "ready to drink" whereby it outputs a "score" for each wine in your cellar which ranks that wine relative to all the other wines in your cellar in terms of what you should drink first.
The exact working of and rationale for this formula is contentious, but it's super useful when one has 1,000 bottles in the cellar and wants to know what to drink next. Most importantly it helps to avoid a disaster where bottles end up going off in one's cellar because they weren't consumed within their drinking window.
What it can't avoid though is what might be called a "wine tsunami". Which is when a lot of the bottles in a cellar hit their final year(s) all at the same time.
This is a real thing as people buy wines with the intention of drinking them in 5 or 10 or 15 years' time. Or one can buy a case of wine with the intention of drinking the 6 bottles over 10 years, starting in 5 years' time, for instance.
Problem: (note that wine and bottles are two different things)
Each wine has a drinking window, eg. 2019 - 2025
Each wine has a quantity of bottles present in your cellar, eg. 1, or 6, or 24, whatever
I want to spread the quantity of bottles for a wine across the remaining years left in the drinking window for that wine. This spread needs to be more or less even...
Simply dividing the number of bottles left into the remaining years will give me a fraction in some cases, which isn't going to work because I want to graph the bottles that are "scheduled" for drinking for each year for the next decade and you can't drink a fraction of a bottle (with a coravin, maybe, but I don't wanna).
If I were doing this by hand the columns would look like this:
Note one of the things I'd like to do is avoid drinking a bottle during the last year of it's drinking window, I've applied this rule to the example above.
With the wine in row 2 the solution could be some IF functions, but the problem for me comes in when there's fewer bottles than years, then I don't know how to get it to skip a year or two as in row 3....
Any advice on which formulas to use or if this requires macros (which I'm not really familiar with) would be greatly appreciated.
Thanks in advance!
