Help needed - seriously (looping? macros? formulas?)

[jarski]

Help on this one would really make my day - I am a total novice on
macros / programming but I have this monster of a data in my hands that
needs to be clarified.. so please - the simpler the solution the
better.

Ok, here is the scenario:
Column A has a list of numeric values. The list is very long and the
values are on every row ie there is a value on every row way down the
sheet.

On column B I need to select the biggest value from every set of three
values on the column A. In other words, in cell B1 I need the biggest
value of A1:A3, in B4 the biggest value of A4:A6 etc.

Now, this could simply be done by copying the formula MAX in the proper
cells in column B, but the list of values is too long, so a macro is
probably needed.

THANK YOU!

 

[TomHinkle]

Use column C (or any other one contiguous)
paste the following formula in every row in column C

=INT((ROW()-0.1)/3)

This will make groups of 3 rows... 1-3 will be 0, 4-6 will be 1, etc..

Once these groupings are in place, go to the Data menu, select subtotals..
At every change in column C, choose to get the max of column B...

Simple, and best of all, no code!!

 

[K Dales]

No macro, use this formula in B1 and copy down:
=IF(MOD(ROW()-1,3)=0,MAX(A1:A3),"")

 

[jarski]

Thanks Tom,
that seems to work. Still, I'm only half way - the method you provided
leaves the max values where they are, although identifying them. I need
those max values to step out more, preferably to be copied into a
separate column. Is there a neat way to do that?

K Dales,
I didn't get your formula to work, too few arguments it says..

thanks again,
jarski

 

[jarski]

Wait wait
K DAle
NOW it works, my excel 97 wanted semicolons instead of commas... your
formula is perfect. thanks!