Results 1 to 9 of 9

20040306, 13:31 #1
 Join Date
 Feb 2004
 Location
 Belgium
 Posts
 30
 Thanks
 2
 Thanked 0 Times in 0 Posts
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 VBAmacro (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 ccolumn 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

20040306, 14:21 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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#>)
Added:
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 nonpositive 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.

20040306, 17:05 #3
 Join Date
 Feb 2004
 Location
 Belgium
 Posts
 30
 Thanks
 2
 Thanked 0 Times in 0 Posts
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

20040306, 17:13 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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 nonnegative entries, and finally I substituted the "core" formula into the second formula, in order to have the entire formula in one cell.

20040307, 20:53 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: find rightmost positive cellvalue in a row (excel2000)
Hi Aladin,
That is shorter indeed, but shouldn't it be 9.99999999999999E+307 instead of 9.99999999999999+307 ? Otherwise, large values will upset the formula.

20040307, 21:42 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040307, 22:50 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
AladinMicrosoft MVP  Excel

20040307, 22:55 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: find rightmost positive cellvalue in a row (excel2000)
Hello Aladin,
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)

20040308, 00:19 #9
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: find rightmost positive cellvalue in a row (excel2000)
A bit shorter: The data is assumed to start in D1...
=SUBSTITUTE(ADDRESS(1,MAX(((D1:INDEX(1:1,MATCH(9.9 9999999999999E+307,1:1)))>0)*COLUMN(D1:INDEX(1:1,M ATCH(9.99999999999999E+307,1:1)))),4),"1","")
which also must be confirmed with control+shift+enter instead of just with enter.
E for Exponent added at Edit. Hans, thanks for catching that.Microsoft MVP  Excel