Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2001
    Location
    Agoura Hills, California, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Look Backwards? (Excel 2002/10.4524.4219)

    I'm trying to re-work the ideas presented in this post about automatic column-updating. My co-worker is doing some inventory work in Excel (don't ask--database phobic here), and he has 60 5-column sections across and rows down for each item. Each of the 60 sections represents a day on which there may or may not have been a transaction on that particular item. Needless to say, some rrows have blanks, as nothing transactioned on that item that day. he wants to return per row, the last date a transaction occured.

    Because I need this constantly calculated, I would prefer some equivalent to HLOOKUP function that looks backwards from the end for the first non-blank. and reports back the date row. I'd rather not have a macro that needs to be re-run everytime we update the Inventory.

    I may be just missing some critical INDEX() magic or something easier, and encourage all routes to success.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Look Backwards? (Excel 2002/10.4524.4219)

    Hi Jeb,

    Something like:
    =MAX(IF((E4:N4)<>0,E$3:N$3,))
    entered as an array formula (Ctrl-Shift-Enter) should work.

    See attachment.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look Backwards? (Excel 2002/10.4524.4219)

    What circumstances indicated that an array-type formula is appropriate for this solution?

  4. #4
    Lounger
    Join Date
    Jul 2001
    Location
    Agoura Hills, California, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look Backwards? (Excel 2002/10.4524.4219)

    You rock. MAN. This is the best forum on the web, bar-none. I didn't even have to bust up his crazy centered-across-columns look. Lemme see, I need to give you a smily-salute... <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25>

    Ok, that out of the way, I second the motion to ask you why this needed to be an array formula.

  5. #5
    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: Look Backwards? (Excel 2002/10.4524.4219)

    If you wanted to SUM or COUNT the conditional you could use SUMIF or COUNTIF formulas. You could even use SUMIF /COUNTIF if you wanted to make an "AverageIF".

    There is no formula in excel that does a MAXIF (or a MINIF, or anything other one). You could create one or you can use the built-in ARRAY formulas.

    An excellent primer on ARRAY Formuls is by Chip Pearson and it should give you a better feel for them.

    What can get confusing is that some of the formulas are entered as "ARRAY formulas" with ctrl-shift-enter, though some of them do not require it.

    In general if the result is not a single value but a list (an array) of values, you will need a ctrl-shift-enter (eg like LINEST, MMULT, MINVERSE, LOGEST, TREND, FREQUENCY, GROWTH). If you are only getting 1 result it still might require an array if you are essentially creating an intermediate array via a formula.
    For example:
    =MAX(IF((E4:N4)<>0,E$3:N$3,))
    creates an array of 10 values from [IF((E4:N4)<>0,E$3:N$3,)] which would be equiv to entering In E1:
    If(E4=0,E3) and then copying it to F1:N1

    And then calc max(E1:N1)

    This "intermediate array" is what requires the ctrl-shift-enter. The built-in functions (SUMIF, COUNTIF, SUMPRODUCT, etc) work with intermediate array "naturally" and don't require the ctrl-shift-enter. You could create UDFs that worked similarly and do not require ctrl-shift-enter (unless your result is an array!)

    Hope this helps,
    Steve

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Look Backwards? (Excel 2002/10.4524.4219)

    Thanks Steve,

    Well put.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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