Finding a "Min" value
 

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.

Finding a "Min" value
 

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
wrote in message
oups.com...
| 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.
|

Finding a "Min" value
 

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)
--
Gary''s Student - gsnu200732


" wrote:

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.

Finding a "Min" value
 

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


" wrote:

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.

Finding a "Min" value
 

On Jun 21, 2:57 pm, p45cal wrote:
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



" wrote:
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.- Hide quoted text -

- 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

Finding a "Min" value
 

Array entered as befo

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


" wrote:

On Jun 21, 2:57 pm, p45cal wrote:
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



" wrote:
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.- Hide quoted text -

- 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