Is there a better solution to what I did with INDEX

[bines53]

Hello Friends,

I built a replacement OFFSET function with INDEX.
Question, the solution seems cumbersome, is there another solution?

Thank you !

 

[Somendra Misra]

Hi Bines,

Both the formula i.e. OFFSET and INDEX are making dynamic range for SUM function. What is there that you are finding cumbersome?

2nd if you can explain the purpose behind you formula than may be some reader can propose you an alternate one.

Regards,

 

[bines53]

Hi Somendra ,

Is it possible to improve the INDEX function ?

Thank you !

 

[Somendra Misra]

@bines53

The INDEX is very clean. Why you want to improve it?

Regards,

 

[bines53]

Hi Somendra ,

Thought possible ROW NUM IN function INDEX will get a minus (-$K$1) without using the function ROWS .

Thank you !

 

[Somendra Misra]

@bines53

Without knowing the purpose of the formula, its hard for me to suggest you something.

Regards,

 

[bines53]

Hi Somendra ,

For example, take the formula in cell G3, I want to summarize 246 lines, the last line is in cell B253 and the first line is 245 over cell B253, in this case the first cell is a cell B8.

Regards,

David

 

[Somendra Misra]

@bines53

You say the last line is in cell B253, but the last line in column B is 259 and column A is 256, can you clarify that?

Why are you saying the last line is B253?

Regards,

 

[bines53]

Hi Somendra ,

G3=SUM(INDEX($B$1:B253,ROWS($B$1:B253)-$K$1):B253)
G4=SUM(INDEX($B$1:B254,ROWS($B$1:B254)-$K$1):B254)

column A,Should not be treated

Regards,

 

[Somendra Misra]

Hi Bines,

It's may be my fault to make myself clear to you, What I want to ask is why you selected B253 as last line in your G3 formula? Why you started that as your starting point?

Regards,

 

[bines53]

Hi Somendra ,

I check all kinds of relationships in my data, I have to check them in some ranges.

Regards,

David

 

[juanito]

Hello David -

I think the oddness in your formulas arises because you count all the way to the end, and then backwards again. Wouldn't it be better just to count forwards?

I attach an alternative that does just that. Note that I created some named formulas, including one for the total number of records in your dataset: I didn't use this in the end, but you could use it if for some reason you need to keep the "forwards-backwards" approach.

- juanito