MAX €œevery 4th cell€� in a row
 

In Sheet2, cell N2, I want to calculate the maximum value:

MAX(sheet1!B8,sheet1!F8,sheet1!J8,etc,

Ive already been shown (by some very learned and helpful fellows on this
site - thanks) that in cell E2 of Sheet2 the SUM of these cells can be
obtained using

=SUMPRODUCT(--(MOD(COLUMN(sheet1!8:8)+2,4)=2), sheet1!8:8)

but dont know how to perform the MAX function in a similar manner. Or even
if this is the best way.

Furthermore, I dont follow the relationship between the cell Im in and the
cells Im trying to SUM, MAX, etc, and the figures +2 and =2 (in the
example). The concept of COLUMN(sheet1!8:8) -

Can anyone help? Thanks for help already received in this area. It is
highly appreciated. Keep up the good work!

MAX "every 4th cell" in a row
 

Hi Hugh

Building on to the formula you have, enter the following as an array
formula, which means when entering or editing use Ctrl+Shift+Enter to
commit and Excel will insert the curly braces { } around the formula.
Do not type them yourself.

{=MAX(--(MOD(COLUMN(Sheet1!8:8)+2,4)=0)*Sheet1!8:8)}

--
Regards

Roger Govier


"Hugh Murfitt" wrote in message
...
In Sheet2, cell N2, I want to calculate the maximum value:

MAX(sheet1!B8,sheet1!F8,sheet1!J8,etc,

I've already been shown (by some very learned and helpful fellows on
this
site - thanks) that in cell E2 of Sheet2 the SUM of these cells can be
obtained using

=SUMPRODUCT(--(MOD(COLUMN(sheet1!8:8)+2,4)=2), sheet1!8:8)

but don't know how to perform the MAX function in a similar manner.
Or even
if this is the best way.

Furthermore, I don't follow the relationship between the cell I'm in
and the
cells I'm trying to SUM, MAX, etc, and the figures +2 and =2 (in the
example). The concept of COLUMN(sheet1!8:8) -

Can anyone help? Thanks for help already received in this area. It
is
highly appreciated. Keep up the good work!

MAX "every 4th cell" in a row
 

Use

=MAX(IF(MOD(COLUMN(8:8),4)=2,8:8))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Hugh Murfitt" wrote in message
...
In Sheet2, cell N2, I want to calculate the maximum value:

MAX(sheet1!B8,sheet1!F8,sheet1!J8,etc,

I've already been shown (by some very learned and helpful fellows on this
site - thanks) that in cell E2 of Sheet2 the SUM of these cells can be
obtained using

=SUMPRODUCT(--(MOD(COLUMN(sheet1!8:8)+2,4)=2), sheet1!8:8)

but don't know how to perform the MAX function in a similar manner. Or
even
if this is the best way.

Furthermore, I don't follow the relationship between the cell I'm in and
the
cells I'm trying to SUM, MAX, etc, and the figures +2 and =2 (in the
example). The concept of COLUMN(sheet1!8:8) -

Can anyone help? Thanks for help already received in this area. It is
highly appreciated. Keep up the good work!

MAX €œevery 4th cell€� in a row
 

Thanks, fellas!

"Hugh Murfitt" wrote:

In Sheet2, cell N2, I want to calculate the maximum value:

MAX(sheet1!B8,sheet1!F8,sheet1!J8,etc,

Ive already been shown (by some very learned and helpful fellows on this
site - thanks) that in cell E2 of Sheet2 the SUM of these cells can be
obtained using

=SUMPRODUCT(--(MOD(COLUMN(sheet1!8:8)+2,4)=2), sheet1!8:8)

but dont know how to perform the MAX function in a similar manner. Or even
if this is the best way.

Furthermore, I dont follow the relationship between the cell Im in and the
cells Im trying to SUM, MAX, etc, and the figures +2 and =2 (in the
example). The concept of COLUMN(sheet1!8:8) -

Can anyone help? Thanks for help already received in this area. It is
highly appreciated. Keep up the good work!