rewriting formula to eliminate "offset" function

I've got a CPU-intensive worksheet that I'm trying to clean up.

This is one of the formulas which I suspect is eating up resources. Rather than using the OFFSET function, how would it be written to just refer to column A to get the SUMIF calcs?

Please Login or Register  to view this content.

Thanks much!

What's the value of C4 ?

C4 is one of the inputs to the spreadsheet. It can range from 1 to 100, so I'd like to keep it as a variable.

I figured that much, I was hoping for the actual value like 10 or something so I could work out the math of the formula.
But anyway

The need for that to be a variable is the reason the formula is so complicated and inefficient.
Not much you can do about it.

You can do similar with INDIEX instead of OFFSET, but not sure it would be any better performance wise.

You can replace each offset function
OFFSET(B1828,(-1*$C$4)+1,-1,$C$4,1)
with
INDEX(A:A,ROW(B1828)-$C$4+1):A1828

OK, thanks. Curious, do you know if it is more efficient to create a number of columns, each with a simple formula, than to have one column (as in the example above) with a large, inefficient formula?

I lean towards using helper columns, if they make sense on a case by case basis.
Without a full understanding of what you're doing here, it's hard to say if that would help or not.

OK thanks. The formula is for RSI (relative strength indicator), which is used in analyzing stock prices.

I've attached a spreadsheet showing an alternative way to calculate it, using using columns. (even then the spreadsheet still
uses INDEX).

The final calculation gives you a number from 0 to 100. 50 would be a "flat" series of prices. Anything over 50 means stock is rising,
and anything under 50 means stock is falling.

Yes, helper columns make sense here.
Because you need the same calculation more than once per row anyway..
You have 2 formulas (G and H) that refer to F.

So it saves calculation time.
If you crammed both E and F calculations into 1 formula for G, then you'd have to do the F calculation AGAIN for H.

OK thank you! I didn't know that. I appreciate you help.

You're welcome.