thanks that should work great, just so viewers know F could be X / not part
of range (esepecially if this formula meant to be in ~col F. f/x would be
the last value. thanks.

"FiluDlidu" wrote:

I put your five columns into A:E because I couldn't be bothered to scroll
right, but you'll get the idea... Column F contains the value reached so far
(or whatever it is), then column G (I guess, even though you can put it
anywhere you want) contains the following function. The formula won't get
any longer if you add columns to your incrementing range.

=SUMPRODUCT(MIN(999*(A1:E1<F1)+A1:E1))

If you were to put your incrementing range into rows instead, change 999 to
70,000 to make sure it brings it above the highest row available (which, as
you know, is 65,536).

Does that work for you?

"Nastech" wrote:

hi, have 5 columns of numbers that ascend in value from left to right,
if different col val is highest value.. use next column to right as the
new goal level.
thanks

have a long equation, that would get longer with more columns, is there a
way to use mod / range / offset (if embedding problem) to make formula more
efficient?

formula that otherwise works is:
=IF((EC9CL9)*(CL90),CM9,IF((EC9CK9)*(CK90),CL9 ,IF((EC9CJ9)*(CJ90),CK9,IF((EC9CI9)*(CI90),CJ9 ,CI9))))


other idea's:
other formulas used for adding every other column might be an idea, not sure..


=IF(AC33="","",SUM(--(MOD(COLUMN($AC33:$AK33),2)MOD(COLUMN($AC33),2)), $AC33:$AK33))

=IF(AC33="","",SUMPRODUCT(--(MOD(COLUMN($AB33:$AJ33),2)=MOD(COLUMN($AB33),2)), $AB33:$AJ33,$AC33:$AK33))