0

I have a given number n of rows that should be multiplied with a factor x to reference a cell above the current cell.

n is a fixed number

x is given in another cell and can be changed.

How can I achieve this without using volatile functions like INDIRECT or OFFSET (this has to be done a few dozen times)?

Example:

I have numbers in 2 blocks of 26 rows each. In cell C2 is the factor that defines the number of blocks to go up, so 1 to get to A27, 2 to get to A1). To clarify, C3 shows the height of my blocks, this number does not need to be a reference, it can be given as a number in my formula. I imagine it would like this:

enter image description here

Looking at the simplified example one might wonder, why a simple IF formula is not sufficient, but the number of block may vary a lot (probably [1,10]), so referencing fixed cells is not working for me.

I am using Excel 2016. If possible, I want to achieve this using formulas. If not, an approach with VBA would be acceptable.

I am not sure if it is possible to reference to a cell above the active cell. INDIRECT looked somewhat promising but I could find out a way to somehow multiply the amount of cells to go up and being a volatile function it is unpractical for my needs.

I think I would need to be able to input something like A53-26*x, is there a way to do maths with row numbers in a reference?

I have found these posts, that I think do not solve my problem:

Excel: Reference cell above

Excel: Reference to current cell

e-shirt
  • 101
  • 3
  • 1
    Could you please visualize your issue with a few simplified samples? A screenshot, or even some markdown data? – JvdV Apr 04 '23 at 07:33
  • 1
    Where exactly is n located? Will A1 always be A1? Why don't you try to solve it with `OFFSET` and post it so we can suggest (post) an improvement? – VBasic2008 Apr 04 '23 at 11:38
  • n is the number of rows, here: 26. It's not located anywhere, it is defined by the data structure and can only be given as a real number, not as cell reference. Also, OFFSET is volatile. The function has to be non-volatile. – e-shirt Apr 04 '23 at 13:15
  • 1
    It could be done using INDEX MATCH MAX, but your explanation is not sufficient for me to post a working formula for you. Maybe you could post how you would approach this using OFFSET, so we can translate it into INDEX (like Vbasic2008 suggested already). The better info you give, the better answer you get. – P.b Apr 04 '23 at 16:03

0 Answers0