MOD of OFFSET question?

N

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))
 
F

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?
 
N

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.
 
N

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top