Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MOD of OFFSET question?
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)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MOD of OFFSET question?
mod "or" offset..
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MOD of OFFSET question?
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)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MOD of OFFSET question?
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)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
MOD of OFFSET question?
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Question | Excel Worksheet Functions | |||
Offset question | Excel Worksheet Functions | |||
OFFSET() question for '97 | Excel Discussion (Misc queries) | |||
An OFFSET question | Excel Worksheet Functions | |||
sum offset question | Excel Worksheet Functions |