Scotster
Board Regular
- Joined
- May 29, 2017
- Messages
- 55
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Just a query regarding the result from an Index formula really. I have a formula that I got help to create but I'm struggling to understand how the address is realised from the resulting index calculation.
The very simplified version of the formula is below:
The "7" in the above is calculated but I wanted to give it as basic as I could. Table below to show the result from using the above formula.
The Index part of the formula will return "80" on its own. However, using the SUM, it references the resulting Index address of A8.
It made me wonder, is there a way of simply showing the address as the result an index formula, rather than the value of the location? I've used Match etc to find the address, but this would be far more elegant if it was easy to do. Is this a well known use of the function? I had never even considered it before.
The very simplified version of the formula is below:
Code:
=SUM(INDEX(A$2:A9,7,1):A9)
The "7" in the above is calculated but I wanted to give it as basic as I could. Table below to show the result from using the above formula.
The Index part of the formula will return "80" on its own. However, using the SUM, it references the resulting Index address of A8.
It made me wonder, is there a way of simply showing the address as the result an index formula, rather than the value of the location? I've used Match etc to find the address, but this would be far more elegant if it was easy to do. Is this a well known use of the function? I had never even considered it before.
10 | Result | ||
20 | 170 | ||
30 | |||
40 | |||
50 | |||
60 | |||
70 | |||
80 | |||
90 |