A Challenge: Detection of first and Last +ve number in a range

[Chris Gorham]

Hi,

I hate to admit defeat, but this has got me stumped...

I'm using the "forecast" function to predict from a row of figures. This row
may or may not contain some initial zero's where there is no information, so
the exact start of the data sequence is not the same for each row. In
addition data will be added each quarter("actuals"), so the end of the data
range will advance and the prediction from "forecast" must change.

As you might know, "forecast" will be sqewed if the range you select
includes zero's where there is no data or does not include the new data
entered as the actuals advance - therefore I need to write a formula that
detects the beginning and end of the number sequence.

I have an "input" row above my "forecast" row where the ("actuals") data
will be entered. If there is information in a cell in this row, then the
forecast function is not used - the "actuals" are used instead via a simple
IF statement. However at the end of the "actuals" there are blank cells in
the "input" row and the "forecast" function must take over based ONLY on the
full range of "actual" data.

I can get part of the "forecast" function to work using "indirect" to
reference the beginning and end of the data sequence - I can therefore
control the range that "forecast" covers. It is now simply a matter of
detecting the beginning and end of the sequence.

I could do this using a couple of rows with flags in them - but I have +/-
50 rows I want to predict - I want a neat way that ensures I only have two
rows - just an input row and a row below that takes these actuals and
forecasts them (although I do have a time period counter row at the top of
the table)...

I've used combinations of HLookup, Min, Max, Rank and so on, but its got me
beat...

Thanks for your assistance....Chris

 

[Harlan Grove]

Chris Gorham wrote...
....

I can get part of the "forecast" function to work using "indirect" to
reference the beginning and end of the data sequence - I can therefore
control the range that "forecast" covers. It is now simply a matter of
detecting the beginning and end of the sequence.

....

If you have a variable number of positive values in, say, B3:IV3, the
index of the first one would be given by the array formula

=MATCH(TRUE,B3:IV3>0,0)

and the index of the last one would be given by

=MATCH(2,1/(B3:IV3>0))

So the dynamic range from the first to the last could be given by

INDEX(B3:IV3,MATCH(TRUE,B3:IV3>0,0)):INDEX(B3:IV3,MATCH(2,1/(B3:IV3>0)))

which would need array entry in formulas.