Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel formula help (97 SR-2 on NT4.0)

    Gurus,

    Please see attached file.

    I need the value from the User column, which user column determined by the last non-zero data from the review column in the row.

    Fer instance,

    On line 2, formula would return 12939 from column H, because 711 in column G is the last non-zero review data in the row. But on line 9, formula would return 90002 from L, because 75 in K is the last non-zero review data in the row.

    So, whatever the last review number is in the row, find the user number after it, and that should be result of the formula.

    I've tried all sorts of things with the COLUMN function and such, but it's just not going. Any help? Oh, my users don't even know what VB stands for, so I can't wow them with any code. They need a formula they can understand.

  2. #2
    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: Excel formula help (97 SR-2 on NT4.0)

    Isn't it just 2 columns to the left or will there we some reviews with no values?

    Formula in D2 is "=B2" in F2 is "=D2", etc?? copy the formula down the rows and then copy the columns
    Or am i missing something?
    Steve

  3. #3
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula help (97 SR-2 on NT4.0)

    It won't work to just go 2 columns to the left, because there is a variable number of reviews per line, and I need the User for just the last review, wherever that review may fall. A max of 10 per line, but can easily be just 1.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula help (97 SR-2 on NT4.0)

    If you enter this formula as an array, does that do what you want?

    =OFFSET(N9,0,-(COUNTIF(A9:N9,0)))

  5. #5
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula help (97 SR-2 on NT4.0)

    That's it! Thank you! It seems to work on all of the data!

    Can you describe that formula in "pseudo-code" or just plain verbose type language? I'm not familiar with the OFFSET function, and arrays seem like magic to me.

  6. #6
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula help (97 SR-2 on NT4.0)

    I got it!

    Clever, clever!

    Find the first zero, because the last review and user must be just before that, then offset -1 column from that and return the number.

    Elegant!

  7. #7
    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: Excel formula help (97 SR-2 on NT4.0)

    Not quite.
    Offset(Start, rows,columns)
    Start at cell N9. Move 0 rows down, move to the left (-) the count of number of columns that have 0 in them.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula help (97 SR-2 on NT4.0)

    Steve,

    I did it by nesting IF statements. It does not exceed the limit.
    =IF(M2>0,N2,IF(K2>0,L2,IF(I2>0,J2,IF(G2>0,H2,IF(F2 >0,F2,IF(C2>0,D2,IF(A2>0,B2,"nutin yet")))))))
    Why would the Array formula be preferred?
    Is the array approach better, performance wise?

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  9. #9
    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: Excel formula help (97 SR-2 on NT4.0)

    IFs can be very slow. I try to avoid them.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula help (97 SR-2 on NT4.0)

    A non-volatile (thus less costly) formula would be...

    =INDEX(A2:N2,MATCH(0,A2:N2,0)-1)
    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
  •