Use SUMPRODUCT and MOD to Find MAX in Odd Numbered Columns

[azbasketcat]

I am trying to use the following formula to find the MAX value in a row of data.

=SUMPRODUCT(MAX((C1:Z1)*MOD(COLUMN(C1:Z1),2)))

Esentially, I want to ignore the values in odd Columns like D and F and choose the max value in even columns like C and E.

When I select parts of the formula and hit F9, it looks like Excel is returning the correct values in the arrays, but the ultimate result gives me the max value of all columns, not just the odd columns.

I can get it to work using Ctrl+Shift+Enter, but I thought SUMPRODUCT would not require this to be treated as a CSE array formula.

Ultimately, I want to find the MAX of the absolute values of a large row of data =max(max(c1:z1),abs(min(c1:z1))), but I think this SUMPRODUCT question will help me get most of the way there.

Any help would be appreciated. Thanks - Randy

 

[shg]

Ultimately, I want to find the MAX of the absolute values of a large row of data =max(max(c1:z1),abs(min(c1:z1))),

How about =max(abs(c1:z1)) ?

 

[azbasketcat]

Thanks for the suggestion. I was able to get that formula to work as a CSE, as well as a version of that formula that only includes the odd numbered columns.

However, I recently became aware of all the amazing things you can do with SUMPRODUCT and I thought there would be a way to use SUMPRODUCT for this calculation, as opposed to a CSE formula. Really just trying to broaden my Excel skills.

Thanks again.

 

[T. Valko]

Thanks for the suggestion. I was able to get that formula to work as a CSE, as well as a version of that formula that only includes the odd numbered columns.

However, I recently became aware of all the amazing things you can do with SUMPRODUCT and I thought there would be a way to use SUMPRODUCT for this calculation, as opposed to a CSE formula. Really just trying to broaden my Excel skills.

Thanks again.

Try one of these...

If you will never insert new columns before the data range:

=MAX(INDEX((MOD(COLUMN(C1:Z1),2)=1)*ABS(C1:Z1),0))

If you might insert new columns before the data range:

=MAX(INDEX((MOD(COLUMN(C1:Z1)-COLUMN(C1),2)=0)*ABS(C1:Z1),0))

 

[azbasketcat]

Works great. Thanks!!

 

[T. Valko]

Works great. Thanks!!

You're welcome!