Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Challenge: Detection of first and Last +ve number in a range
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Challenge: Detection of first and Last +ve number in a range
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:IV30,0) and the index of the last one would be given by =MATCH(2,1/(B3:IV30)) So the dynamic range from the first to the last could be given by INDEX(B3:IV3,MATCH(TRUE,B3:IV30,0)):INDEX(B3:IV3, MATCH(2,1/(B3:IV30))) which would need array entry in formulas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|