Min & Max help

[Guest]

Hello,
The best way to describe what I need help on is below. Column's A & B I
have now.
I need your help creating 2 formulas, 1 formula entered into cell C1 then
filled down, 1 formula entered into cell D1 and filled down also.
Cell C1 formula would count the number of rows from first "minimum" to the
next "max". Then from that "max" to next "minimum".
Cell D1 formula 'filled down' in example below would compute the % increase
or % decrease from minimum to max and max to minimum.


have have need need
A B C D
1 40 minimum
2 45
3 52
4 56 max 3 A1 to A4 % increase <----what I
need
5 49
6 44 minimum 2 A4 to A6 % decrease <----what I need
7 45
8 47
9 48
10 51 max 4 A6 to A10 % increase <---what I
need

Thanks to anyone who can help!
George.

 

[R.VENKATARAMAN]

I hope I have understood your requirements.
we dont know wether A1 is local minimum or local maximum as we dont know the
previous figure. so leave it
insert two columns after columnA which will be new B and new C
type in B2 repeat B2 type the following
=IF(AND(A2<=A1,A2<=A3),A2,0)
type in C2
=IF(AND(A2>=A1,A2>=A3),A2,0)

now copy B2 and C2 down thedata
you will get
40
45 0 0
52 0 0
56 0 56
49 0 0
44 44 0
45 0 0
47 0 0
48 0 48
51 0 0


44 local minimum and 56 and 48 local maxima.

if you want enter 40 in B1and 0 in C1, then 40 also may be considered local
minimum. similarly enter 0 in B10 and 51 in C10 , then 51 can be considred
local maximum
welcome to contact by email. remove $s,@s&s from email address
==================================

 

[Frank Kabel]

Hi
in C2 enter:
=IF(B2<>"",ROW()-LOOKUP(2,1/($C$1:$C1<>""),ROW($C$1:$C1)),"")
and copy this down

in D2 enter
=IF(B2<>"",A2/OFFSET(A2,-C2,0)-1,"")
and format this cell as percentage. Copy down