Non array formula to get the last value

[snjpverma]

A non-array formula to get the last number in Row 1 after "YES" in Row 2. In the first table , the answer is 2. In the second table, the answer is 0 as there is no value after "Yes"

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]20
[/TD]
[TD]30
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]20
[/TD]
[TD]30
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

I think we'll have to agree to disagree. The very act of creating a spreadsheet is a personal creation. Moreover, Excel has a lot of specifications and limits besides BigNum. For example, Excel has a maximum of 16384 columns (XFD). Your formula would work fine replacing the Js with XFD. But you wouldn't want to for performance reasons. I've also seen formulas using a combination of LOOKUP and FIND. We can use BigNum, or 32767 because that is the maximum number of characters in a cell. Both work, neither is a personal creation. So which to choose? Perhaps we're searching an imported file where the column has a maximum of 100 characters. Why not use 100? Not really a personal creation, or an Excel creation, but a requirement of the project.

At some point the designer has to make a decision about how to handle certain situations. At long as the designer can justify it, and it works, I'm OK with it. Excel has a lot of ways to do the same thing. There are some advanced concepts as well to consider (e.g. INDEX is generally better than OFFSET due to volatility), but a lot of the design considerations do not always make the explanation.

So it's clear that we disagree on some things, but I think we both have helped a lot of people, you a few more I'd say.