Need to get Absolute reference to cell in a Match Function result

[montyj]

Hola!

I have a need to use the Slope function on a range of values that include zero’s in the end cells and the position the zero’s start changes ever week. For example:

SLOPE(E59:E72,J59:J72)

The first range, the Y-axis, may currently have the first 10 cells filled with non-zero values, while the remaining cells are all zero values. Every week a new row of data is added from another sheet.

The second range, the X-axis, is filled with a sequential number set, ie 1-14.

But the SLOPE function does not return the correct results if I select the entire range; I need to have it stop when it see’s the first zero in the range.

I can use the MATCH function to find the ‘relative’ row number’ in the range where the zero’s start, then just subtract 1, but the SLOPE function needs absolute cell positions.

In this case MATCH returns “14”, which I need to somehow turn into “$E$72” so I can give SLOPE the correct range to work with for that week. I will use the same formula in the other range as well so they match.

So I need to come up with a way to convert/derive an absolute cell position from: MATCH(0,E59:E74,0). I think I can then stick that function in the SLOPE formula so it will then only use the range w/o any zero’s that changes each week.

If I can get something to work, it will save me having to manually update the range values every week when new data comes in. Perhaps there is something better than MATCH for this task; it seemed the simplest approach but I just need to convert that relative array position to the real spreadsheet position.

Thanks.
Monty

Update: I experimented some more and came up with:

ADDRESS(MATCH(0,E59:E74,0)-1,5, 1, ) which produces “$E$13”.

But that is not something that produces “$E$71” or just “E71”, which is the actual spreadsheet cell reference I need to plugin to the SLOPE function.

[anon73440385]

Hello,

one solution could be to correctly build the range as a string and then use function INDIRECT() - See the following sample file:

Q289622-SLOPE.ods

Steps:

• Find first zero index: =MATCH(0;E59:E72;0) (cell E74 in my sample file)
• Find last non-zero index: E74-1 (cell E75 in my sample file)
• Build address range for Y : ="E59:E" & E75+59-1
  (one go: ="E59:E" & MATCH(0;E59:E72;0)+59-2) [E76]
• Build address range for X : ="J59:J" & E75+59-1
  (one go: ="J59:J" & MATCH(0;E59:E72;0)+59-2) [E77]
• Calculate slope using Indirect: =SLOPE(INDIRECT(E76);INDIRECT(E77))
  (one go: =SLOPE(INDIRECT("E59:E" & MATCH(0;E59:E72;0)+59-2);INDIRECT("J59:J" & MATCH(0;E59:E72;0)+59-2))

Hope that helps.

[montyj]

Opaque,

Thanks! That gave me some clues, so with some more tweaking, came up with this:

=SLOPE(E59:(INDIRECT(CONCATENATE(“E”,TEXT(SUM(VALUE(RIGHT(ADDRESS(MATCH(0,E59:E137,0)-1,5, 1, ),2)),59)-1,0)))),J59:(INDIRECT(CONCATENATE(“J”,TEXT(SUM(VALUE(RIGHT(ADDRESS(MATCH(0,E59:E137,0)-1,5, 1, ),2)),59)-1,0)))))

It’s sure not pretty, but it works! This referencing a different set of locations than my original post, but shows the long way around. I may experiment in ‘cleaning it up’, but for now it gets me where I want to go!

Monty

[montyj]

Using Opaques response for some ideas, here is my (really ugly!) solution:

=SLOPE(E59:(INDIRECT(CONCATENATE(“E”,TEXT(SUM(VALUE(RIGHT(ADDRESS(MATCH(0,E59:E137,0)-1,5, 1, ),2)),59)-1,0)))),J59:(INDIRECT(CONCATENATE(“J”,TEXT(SUM(VALUE(RIGHT(ADDRESS(MATCH(0,E59:E137,0)-1,5, 1, ),2)),59)-1,0)))))