MAX Function arguments

[Guest]

I have a row of data, 1500,1,1200,5,1900,4 and so on, and I would like to use the MAX function to obtain the maximum number in a range for every second cell/column. Is there a way of achieving this?

 

[Peo Sjoblom]

One way

=MAX(1-MOD(COLUMN(B2:IV2),2),B2:IV2)

where your data starts in B2 (it will start counting from the first cell in
the range, i.e.

B2, D2, F2 etc.

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

I have a row of data, 1500,1,1200,5,1900,4 and so on, and I would like to

use the MAX function to obtain the maximum number in a range for every
second cell/column. Is there a way of achieving this?

 

[Guest]

Hi Peo,

Thanks for your quick reply; I tried this but with no luck (I could still be doig something wrong).

I have my data starting from cell B2, as you suggested:
1000000 99999 1200 12000 4000 15000 50000 5

I wan the max function to ONLY look at the contents of every second cell, so I need it to look at B3, B5 etc.
When I specify the formula as you suggested:
MAX(1-MOD(COLUMN(B2:IV2),2),B2:IV2)

The function returns 1000000

But in my sample, I want it to return 99999, which is the MAX value for every second cell.

I hope this may help.

 

[Terri]

Hi,

1) Try a pivot table - when you get into the data
section of the table, double-click and select Max

2) Check your functions in Excel. (See Help if need be.)

-----Original Message-----
I have a row of data, 1500,1,1200,5,1900,4 and so on,

and I would like to use the MAX function to obtain the
maximum number in a range for every second cell/column.
Is there a way of achieving this?

 

[keepITcool]

formulas need be entered as array formulas with ctrl shift enter

to do the values

in the EVEN columns:
or..=MAX(B2:IV2*(0=MOD(COLUMN(B2:IV2),2)))
in the odd columns
or,,=MAX(B2:IV2*(1=MOD(COLUMN(B2:IV2),2)))

or in the first column
=MAX(B2:IV2*(MOD(COLUMN(B2),2)=MOD(COLUMN(B2:IV2),2)))
or in the 2nd column
=MAX(B2:IV2*(MOD(COLUMN(B2),2)<>MOD(COLUMN(B2:IV2),2)))

or any variation thereof

have fun...



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


"=?Utf-8?B?UGV0ZXIgTGFtYnJvcw==?="

 

[Aladin Akyurek]

=MAX(IF(MOD(COLUMN($B$3:$F$3)-CELL("Col",$B$3)+0,2)=0,$B$3:$F$3))

which must be confirmed with control+shift+enter instead of just with enter.

Adjust the ranges to suit.

I have a row of data, 1500,1,1200,5,1900,4 and so on, and I would like to

use the MAX function to obtain the maximum number in a range for every
second cell/column. Is there a way of achieving this?

 

[Peo Sjoblom]

Change it to

=MAX((MOD(ROW(B2:B300),2)=1)*(B2:B300))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hi Peo,

Thanks for your quick reply; I tried this but with no luck (I could still be doig something wrong).

I have my data starting from cell B2, as you suggested:
1000000 99999 1200 12000 4000 15000 50000 5

I wan the max function to ONLY look at the contents of every second cell,

so I need it to look at B3, B5 etc.

 

[Guest]

Brilliant. Your suggestion or in the 2nd column
=MAX(B2:IV2*(MOD(COLUMN(B2),2)<>MOD(COLUMN(B2:IV2),2)))
worked fine.

Thank you very much for the suggestion.