Highs & Lows formula needed

[George B.]

Hello, I would like some advice on what formula to enter into a cell B1 and
fill down in order to get a "TRUE" result when it is able to find the
'highest' value of the previous 5 rows and the 'lowest' value of the previous
5 rows. (I use exel 97).
Per example below, "TRUE" would show up in B5, B9, B14. If this is cannot
be done, what can I do? I really appreciate anyone who can advise!
George B.

A B
1 20 I would like to enter formula in "B1" and then 'fill down'.
2 21
3 22
4 21
5 24 <-- HIGHEST number of previous 5 rows
6 19
7 16
8 14
9 8 <-- LOWEST number of previous 5 rows
10 11
11 12
12 14
13 15
14 16 <-- HIGHEST number of previous 5 rows
15 13

 

[Frank Kabel]

Hi
not sure about your logic. This way TRUE should also show for B13 (as
15 is the highest value in the last 5 values). also what should happen
in B1. It is also the highest (and lowest) value

 

[George B.]

Hello Frank, thank you for responding.
I may have over complicated my example, let me try this: I want to locate
the very highest and very lowest value for example from row 1 through row 20.
(please forget about the "5 previous rows" instruction). I have about 1400
rows of stock data. Each row is one day of stock data. I am trying to find
the peaks and valleys over specified periods of time. I need a "TRUE" result
at these points since I have data later in these rows I need to compute. I
appreciate your help.
George.

 

[Frank Kabel]

Hi
for max an min simply use
=MAX(A1:A100)
and
=MIN(A1:A100)

or you may put the following formula in B1:
=IF(A1=MIN($A$1:$A$100),"Minimum",IF(A1=MAX($A$1:$A$100),"Max",""))
and copy this down

 

[George B.]

Hello Frank, Great advice! I have been using your formula
=IF(A1=MIN($A$1:$A$100),"Minimum",IF(A1=MAX($A$1:$A$100),"Max","")) most of
the day and am finding that in trying to find the highs and lows of every 20
days for example, can be quite cumbersome for 1400 rows of data. Is there
any way I can edit your formula to find the max and min for the first 20 rows
then for the next 20 rows and so on by just changing the formula once and
filling down 1 time?
I thank you very much for your advice!
George.

 

[Frank Kabel]

Hi
try the following formula in C1:
=IF(A1=MIN(OFFSET($A$1,MOD(ROW()-1,20)*20,0,20)),"Minimum",IF(A1=MAX(OF
FSET($A$1,MOD(ROW()-1,20)*20,0,20)),"Max",""))

and copy this formula down

 

[Larry Stiff]

Hi Frank
tried your formula but it didn't work
this one seems to work in C1:
=IF(A1=MIN(OFFSET($A$1,INT((ROW()-1)/20)*20,0,20,1)),"Minimum",IF(A1
=MAX(OFFSET($A$1,INT((ROW()-1)/20)*20,0,20,1)),"Max",""))

and copy this formula down

 

[George B.]

Hi Frank & Larry, You both are awsome in making these formulas work! I
appreciate your help.
Larry, I have a question about using your formula. What formula can I put in
cell D1 then fill down that would be able to do the following:
1) Tell me how many rows are in between the "minimum" result found in
column C and the next proceeding "Max" found in Column C. (also "Max" to
next proceeding "Minimum").
2) Possibly in column E then fill down, compute the % advance (for "min" to
"max" range) or % decline ( for "max" to "min" range) per column A values.
Much appreciated, George.

Hi Frank
tried your formula but it didn't work
this one seems to work in C1:
=IF(A1=MIN(OFFSET($A$1,INT((ROW()-1)/20)*20,0,20,1)),"Minimum",IF(A1
=MAX(OFFSET($A$1,INT((ROW()-1)/20)*20,0,20,1)),"Max",""))

and copy this formula down