how to automate max function in a column

[luuckyoctopus]

column A consists of 10000 cells , all data are numbers , i want to find maximum value for every 10 cells automatically & put results in next column .... manually it goes like that : select B1.. =max(A1:A10) then select B2..=max(A11:A20) then select B3.. =max(A21:A30) ................... when i tried to double click B1 it gave me different results from the one i wish to be shown .... any ideas .......

[MarvinP]

Hi luuckyoctopus

Find the attached for the answer.

[Cutter]

I would use an INDEX() approach

In B1 your normal MAX() formula
In B2 and down:
=MAX(INDEX(A:A,(ROW()-1)*10+1):INDEX(A:A,ROW()*10))
or
=MAX(INDEX(A:A,ROW(B1)*10+1):INDEX(A:A,ROW()*10))

OR

In B1 and copied down:

=MAX(INDEX(A:A,ROW()*10-9):INDEX(A:A,ROW()*10))

[MarvinP]

@ Cutter - we call that a 1 off error. Also - look at my formula and they are different...
Mine is in B1 and is
=MAX(INDIRECT("A" & 10*(ROW()-1)+1 & ":A"& ROW()*10))
yours is in B2 and
=MAX(INDEX(A:A,(ROW()-1)*10-1):INDEX(A:A,ROW()*10))

Which one works and is easier?

[Cutter]

Hi Marvin

I edited my post to show a "one formula fits all" to use in B1 copied down (as an alternative).

I would use the INDEX() approach to avoid the 1000 volatile INDIRECT()s your approach would give.

[luuckyoctopus]

thx MARVIN thx CUTTER both r working .. now i need to copy the 1st number of every 10 cells it goes like this.. select c1 ..=a1 then select c2 .. =a11 then select c3 .. =a21 and so on .. how can i make it automaticaly ?? any ideas..

[Cutter]

You can use the first half of the formula I gave you:

In C1: =INDEX(A:A,ROW()*10-9)

and copied down

[luuckyoctopus]

thx CUTTER it did the job ... now i have the 1st row for labels so data will start from 2nd row so all the formulas dont work now they need edition to work how can i do that ??

[Cutter]

You can replace the ROW() reference, like this:

In C2: =INDEX(A:A,ROW(A1)*10-9)

In B2: =MAX(INDEX(A:A,ROW(A1)*10-9):INDEX(A:A,ROW(A1)*10))

[luuckyoctopus]

thx CUTTER but maybe i explained what i need in wrong way
all columns with labels now i need to start counting from 2nd row

label1 label2 label3
12323 max 1st value------> ( after edition result = label1 not the 1st value 12323)
xxxxx max --------------------------> ( after edition it starts counting from label1 not 1st value )
xxxxx
xxxxx
xxxxx
xxxxx
xxxxx
xxxxx
xxxxx
xxxxx

[Cutter]

Ah, right - another cloudy brain day.

In C2: =INDEX(A:A,ROW(A1)*10-8)

In B2: =MAX(INDEX(A:A,ROW(A1)*10-8):INDEX(A:A,ROW(A1)*10+1))

[luuckyoctopus]

thx CUTTER it did the job

[luuckyoctopus]

another problem appeared now .. i want o use values in column B & C in other file so i copy & paste in the other file but the cells keep the functions & values r missing how can i show values ??

[Ace_XL]

Perhaps..

Copy--Paste special --Values

[luuckyoctopus]

tx ACE that was usefull