# Thread: find rightmost positive cellvalue in a row (excel2000)

1. ## find rightmost positive cellvalue in a row (excel2000)

got an interesting question to you all i think.

First of all we need a formula to put directly into a sheet no VBA-macro (should be more easier the macro)
How to find the column of the rightmost cell on a row with a positive number?
When knowing the column, we want to put the column heading into the c-column of that particular row.

To my feeling it would be very hard to do so into one formula.
citeria = on the same row
find rightmost cell with a value >1
find the colum number of this cell
put the column header into cell c of hat same row
I think it is to dynamic to achieve this with one formula and it is best done with a procedure
Still I would like to know and dow it with a one formula in the cel "c"-rowto be searched into

Is it to be done ?
Thanks and kind regards
jan

2. ## Re: find rightmost positive cellvalue in a row (excel2000)

Since you want the formula in the same row as the row you are investigating, I assume that you want to exclude the first cells of this row from the result. the following monster formula returns the last column in row 1 (starting with D) containing a positive number, or a blank if there is no such column:

=IF(MAX(COLUMN(D1:IV1)*(D1:IV1>0))=0,"",IF(MAX(COL UMN(D1:IV1)*(D1:IV1>0))<=26,"",CHAR(TRUNC((MAX(COL UMN(D1:IV1)*(D1:IV1>0))-1)/26+64)))&CHAR(MOD(MAX(COLUMN(D1:IV1)*(D1:IV1>0))-1,26)+65))

Notes:
1. It is an array formula, to be confirmed with Ctrl+Shift+Enter.
2. The formula can be filled down to other rows.
3. The formula will probably be across two or more lines in the post, because it is so long, but it is to be entered as one line.

Thanks to <!profile=sdckapr>sdckapr<!/profile> for the formula to convert a column number to the corresponding column heading (in <post#=320010>post 320010</post#>)

The "core" of this formula is the part (MAX(COLUMN(D1:IV1)*(D1:IV1>0))) (repeated several times). This array formula returns the column number of the last positive entry:
COLUMN(D1:IV1) generates a list of column numbers 4, 5, ..., 256.
These are multiplied TRUE = 1 if the entry is positive and by FALSE = 0 otherwise. So all column numbers with non-positive entries are converted to 0.
MAX returns the highest column number that has not been turned into 0.

The formula will only work correctly if the row contains numeric entries.

3. ## Re: find rightmost positive cellvalue in a row (excel2000)

Hans,

this is indeed a monster formula which will take me some time and effort to comprehend.
How do you come to be able to produce such a thing of beauty?

Jan

4. ## Re: find rightmost positive cellvalue in a row (excel2000)

Beauty? That's a matter of taste, I think.

I created the formula in several steps, starting with the "core" part =MAX(COLUMN(D1:IV1)*(D1:IV1>0)) entered as an array formula. I then used that as intermediary result, and used Steve's formula to return the column letter(s). Then I expanded it to handle the case where there are no non-negative entries, and finally I substituted the "core" formula into the second formula, in order to have the entire formula in one cell.

5. ## Re: find rightmost positive cellvalue in a row (excel2000)

That is shorter indeed, but shouldn't it be 9.99999999999999E+307 instead of 9.99999999999999+307 ? Otherwise, large values will upset the formula.

6. ## Re: find rightmost positive cellvalue in a row (excel2000)

I like the use of address much better than my more convoluted calculation. It is much more straightforward.

Steve

7. ## Re: find rightmost positive cellvalue in a row (excel2000)

Hi Hans,

Did I include too many 9's or what? I'm counting 15 of them. That would be the correct number.

Although I didn't mention, it would be better to define a name, say, BigNum, as referring to that largest number Excel knows:

9.99999999999999E+307

and plug in BigNum in the formula.

8. ## Re: find rightmost positive cellvalue in a row (excel2000)

My remark was not about the number of 9's but about the lack of E for Exponent in your original formula (which in itself is better than mine)

9. ## Re: find rightmost positive cellvalue in a row (excel2000)

A bit shorter: The data is assumed to start in D1...