Finding a "Min" value

[helliott04]

To anyone who can help,
I have a Excel file that I need to extract out the Min value and the
Max Value out of a specific column looking only at every ninth record
starting with row one (a1). It will look like this:

a1) 90
a2) 1
a3) 2
a4) 3
a5) 4
a6) 5
a7) 6
a8) 7
a9) 8
a10) 100
a11) 1
a12) 2
a13) 3
a14) 4
a15) 5
a16) 6
a17) 7
a18) 8
a19) 110

I only want to key on the "90","100,"110" and find the min value out
of these which is "90". Hope this makes sense. Can anyone tell me who
to use the "Min" command to do this keying on every ninth record only
in column "A"? Then I need to do the same principle but instead of min
I need "Max" which would be "110". Thank You in Advance.

 

[Jim Rech]

Forgetting about the 'every ninth' part and just assuming that only numbers
that are followed by a smaller number matter then:

=MIN(IF(A2:A20<A1:A19,A1:A19,""))

Array-enter this (Press Ctrl-Shift-Enter rather than just Enter).

By the same reasoning the max would be the MAX(A1:A19)

--
Jim
| To anyone who can help,
| I have a Excel file that I need to extract out the Min value and the
| Max Value out of a specific column looking only at every ninth record
| starting with row one (a1). It will look like this:
|
| a1) 90
| a2) 1
| a3) 2
| a4) 3
| a5) 4
| a6) 5
| a7) 6
| a8) 7
| a9) 8
| a10) 100
| a11) 1
| a12) 2
| a13) 3
| a14) 4
| a15) 5
| a16) 6
| a17) 7
| a18) 8
| a19) 110
|
| I only want to key on the "90","100,"110" and find the min value out
| of these which is "90". Hope this makes sense. Can anyone tell me who
| to use the "Min" command to do this keying on every ninth record only
| in column "A"? Then I need to do the same principle but instead of min
| I need "Max" which would be "110". Thank You in Advance.
|

 

[Gary''s Student]

In a helper column, say column B enter:

=IF(MOD(ROW(),9)=1,A1,"") and copy down

Only every 9th will appear, so:

=MIN(B:B)
=MAX(B:B)

 

[p45cal]

Array-enter the following (CTRL+SHFT+ENTER, rather than just Enter):

=MIN(IF(MOD(ROW(A1:A19),9)=1,A1:A19))
and
=MAX(IF(MOD(ROW(A1:A19),9)=1,A1:A19))

which if properly entered will appear with curly braces around them in the
formula bar thus:
{=MIN(IF(MOD(ROW(A1:A19),9)=1,A1:A19))}
{=MAX(IF(MOD(ROW(A1:A19),9)=1,A1:A19))}

 

[helliott04]

Array-enter the following (CTRL+SHFT+ENTER, rather than just Enter):

=MIN(IF(MOD(ROW(A1:A19),9)=1,A1:A19))
and
=MAX(IF(MOD(ROW(A1:A19),9)=1,A1:A19))

which if properly entered will appear with curly braces around them in the
formula bar thus:
{=MIN(IF(MOD(ROW(A1:A19),9)=1,A1:A19))}
{=MAX(IF(MOD(ROW(A1:A19),9)=1,A1:A19))}

--
p45cal







- Show quoted text -

p45cal,
Your =MIN(IF(MOD(ROW(A1:A19),9)=1,A1:A19)) worked great. My only
issue is some of my values every ninth record contain a zero. So when
using =MIN I am pulling zero as my min qty. Is there a way to use your
command array line and bypass zero's.

Thanks

 

[p45cal]

Array entered as before:

=MIN(IF((A1:A19<>0)*(MOD(ROW(A1:A19),9)=1),A1:A19))