Lists, offsets... help!

[Matt]

Hey guys

I've never ceased to be amazed by the resourcefulness of the folks here so
I'm going to try and get some help with something. My excel knowledge is
fairly good but I can't quite figure out how to solve a particular problem.

Allow me to present some data:

Date (dd/mm/yy) Crude oil Motor oil
01-01-1980 3 0
02-01-1980 2 0
03-01-1980 1 0
04-01-1980 3 2
05-01-1980 4 0
06-01-1980 2 2
07-01-1980 0 0
08-01-1980 1 1
09-01-1980 2 3
10-01-1980 3 3
11-01-1980 3 0
12-01-1980 2 2
13-01-1980 1 1
14-01-1980 2 2

Ok. We'll say that there is a minimum desired level of output from the oil
refinery, for example at least 4 barrels of crude and 1 barrel of motor oil
in the last 7 days. If that has been produced, then everything it going ok.
The spreadsheet is maintained on a daily basis, and let's say that it's 14th
Jan 1980 today.

To check for this is relatively simple. Assuming we name the relevant
ranges as Dates, Crude and Motor:

Last7Days =
OFFSET(Dates,COUNTA(Dates)-COUNTIF(Dates,"<="&DateToday-7),,COUNTIF(Dates,"<
="&DateToday-7),1)
Last7DaysCrude = OFFSET(Last7Days,,1)
Last7DaysMotor = OFFSET(Last7Days,,2)

So SUM(Last7DaysCrude) and SUM(Last7DaysMotor) would therefore give us the
amount produce in the last 7 days. Everything is peachy so far.

QUESTION 1 - are these named ranges ok or is there a more elegant way of
defining them?


Next though is the bit I'm struggling with... we can find out if minimum
production levels have been met over the last 7 days or not, but what I need
to find out is -- if we stopped production now -- how long we'd be 'good'
for until we failed to meet the minimum level (the 4 barrels of crude and 1
barrel of motor oil in the last 7 days)

For example, we can see that we'd be ok up to and including 18-01-1980...
because the crude on the 12,13,14 adds up to 5 and the motor similar. If we
changed the amounts to

08-01-1980 1 1
09-01-1980 2 3
10-01-1980 3 3
11-01-1980 3 0
12-01-1980 2 0
13-01-1980 1 0
14-01-1980 2 0

Then we'd be good up to and including 16-01, as we'd have produced plenty of
crude since but no motor oil since the 10....


QUESTION 2 - how do I work this out? I know I can do each column seperately
and then find out the overall date by running MIN() on the two dates
returned, but I still can't crack it. The answer may be glaringly obvious
but I'm really stuck with this. I'm sure it's probably do-able using
OFFSETS and ROW commands in conjuction but I just don't know how.

Any help gratefully received. My email address if a fake one but post any
advice here or reach me at matt~famous5.com replacing the tilde ~ with the
@.

Thanks
Matt

 

[Domenic]

Hi Matt,

First let me say that I tried solving this when you first posted it last
week. Unfortunately, I couldn't offer a solution at the time. It was
way beyond my capabilities.

But I have since come across a formula that I think just may do the
trick. So here goes...

Assuming that Column A contains your dates, Column B crude oil
production amounts, and Column C motor oil production amounts, first set
up a table which will pull data for the last seven days, starting from
the latest date. So, for example, you could enter the following
formulas...

E1, copied down 6 rows to give you your seven days:

=LARGE(OFFSET(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),0,0,-7,1),ROW(1
:1))

F1, copied over to the next column and down 6 rows giving you the
corresponding data for your seven days

At this point, this will give you your table which will include seven
days worth of data. And you can continue to add your daily data in
Columns A, B, and C, and the table will automatically be updated.

Now for the two formulas I hope will provide you with the information
you need...

I1, entered using CONTROL+SHIFT+ENTER:

=E1+7-MATCH(0,--(SUBTOTAL(9,OFFSET(F1,0,0,ROW(INDIRECT("1:7")),1))<4),0)

J1, entered using CONTROL+SHIFT+ENTER:

=E1+7-MATCH(0,--(SUBTOTAL(9,OFFSET(G1,0,0,ROW(INDIRECT("1:7")),1))<1),0)

Well, I hope this helps. Let me know how this works out!