MOD of OFFSET question?

[Nastech]

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((EC9>CL9)*(CL9>0),CM9,IF((EC9>CK9)*(CK9>0),CL9,IF((EC9>CJ9)*(CJ9>0),CK9,IF((EC9>CI9)*(CI9>0),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))

 

[Nastech]

mod "or" offset..

 

[FiluDlidu]

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]

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.

 

[Nastech]

hi, curious if there would be a solution for a similar problem, have columns
of numbers up/dn from each other. want an actual value of daily percent
changes added to each other. if not mistaken gets a different result if just
using an Average.

solution might all externalize number of columns want to consider. as it
is, have to manually alter formula. %change equation represented is:
to/fm%-100
thanks.


=IF(K9=0,"",((EC9/ED9%)+(ED9/EE9%)+(EE9/EF9%))-300)