Results 1 to 9 of 9
  1. #1
    Lounger
    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 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. #2
    Plutonium Lounger
    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 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. #3
    Lounger
    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

  4. #4
    Plutonium Lounger
    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 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. #5
    Plutonium Lounger
    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    3 Star Lounger
    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.

    Aladin
    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    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)

  9. #9
    3 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •