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!

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!

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!

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!