+ Reply to Thread
Results 1 to 10 of 10

[SOLVED] Referencing to 2nd latest cell

  1. #1
    Registered User
    Join Date
    02-26-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2008
    Posts
    6

    Question [SOLVED] Referencing to 2nd latest cell

    I'm trying to find the 2nd latest cell in a column so I can calculate it with other cell.
    e.g. =({latest cell in column A}*{2nd latest cell in column B})

    PS: I'm sorry for asking 2 similar question in short time delay, but I just realized that I can't use the solution before in my problem

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,151

    Re: Referencing to 2nd latest cell

    What does "2nd latest" mean? Do you mean "second from the bottom row"? I saw the other thread and so I'm assuming that's what you mean, and the numbers are not strictly ascending after all. Try this:

    =INDEX(A:A,COUNT(A:A)-1)*INDEX(B:B,COUNT(B:B)-1)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Referencing to 2nd latest cell

    Hi,

    Not sure to fully understand your requirement ...
    Can you further explain ... or even better provide a sample worksheet ...

  4. #4
    Registered User
    Join Date
    02-26-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2008
    Posts
    6

    Re: Referencing to 2nd latest cell

    Actually it is a inventory management, I just want to get my inventory quantity and value without using any macros. Or anyone have better suggestion to make this inventory list?

    EDIT: I just want simple inventory management, not business capacity with general ledger and else
    Attached Files Attached Files
    Last edited by WeirdMan; 02-26-2010 at 01:12 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Referencing to 2nd latest cell

    Formula in cell I2.

    =MATCH(9.99E+307,D:D)

    Then to get the inventory use this.

    =(INDEX(D:D,$I$2)-INDEX(H:H,$I$2-1)-INDEX(B:B,$I$2))*INDEX(C:C,$I$2-1)+(INDEX(B:B,$I$2)*INDEX(C:C,$I$2))

    Regards
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-26-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2008
    Posts
    6

    Re: Referencing to 2nd latest cell

    Formula in cell I2.

    =MATCH(9.99E+307,D:D)

    Then to get the inventory use this.

    =(INDEX(D:D,$I$2)-INDEX(H:H,$I$2-1)-INDEX(B:B,$I$2))*INDEX(C:C,$I$2-1)+(INDEX(B:B,$I$2)*INDEX(C:C,$I$2))
    This formula can't be used if the TOTAL USED INVENTORY row is more than TOTAL BUY INVENTORY row. So if someday I bought too many item so i can use it in a week without purchasing any item, this formula won't work

  7. #7
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Referencing to 2nd latest cell

    Then use this.

    =(INDEX(D:D,$I$2)-INDEX(H:H,MATCH(9.99E+307,H:H))-INDEX(B:B,$I$2))*INDEX(C:C,$I$2-1)+INDEX(B:B,$I$2)*INDEX(C:C,$I$2)

    Regards

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,151

    Re: Referencing to 2nd latest cell

    Attached is your data with a proposed solution.

    Using a FIFO method you must, for bookkeeping purposes, consume the units that were bought first. Therefore I added a column to keep track of depletions under FIFO rules (yellow header). Then I have an array formula that calculates the total value of inventory purchased minus the value of inventory depleted, based on that new column.

    To enter the array formula, you would enter the formula without the braces, then press CTRL-SHIFT-ENTER (instead of just ENTER). Excel uses the braces to show that it's an array formula.

    On reviewing sailepaty's solution, I don't think it provides a general-purpose FIFO method because it does not take into account the fact that the inventory value can be composed of multiple lots bought at different prices. I think it works for this particular set of data but may not generalize to other situations, such as when several most recently-purchased lots are not yet depleted.

    (BTW I also included a solution using the "by lots" method because it was quick and I didn't notice at first that you needed FIFO.)
    Attached Files Attached Files
    Last edited by 6StringJazzer; 02-26-2010 at 02:11 PM.

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,151

    Re: Referencing to 2nd latest cell

    I'll also add that this is a classic example of why you see the following advice on this type of board: "Tell us your problem, not your solution."

    The problem of providing a valuation using FIFO for a set of purchases and a set of consumptions is much more "interesting" than just finding the second cell from the bottom and doing a multiplication.

    Hope I provided some useful input on this one.

  10. #10
    Registered User
    Join Date
    02-26-2010
    Location
    Indonesia
    MS-Off Ver
    Excel 2008
    Posts
    6

    Re: Referencing to 2nd latest cell

    Thanks, to all. StringJazzer's solution works for me. I thought people will think that I am trying to ask question that I don't even try to solve if I ask to provide me a FIFO valuation function. That's why I asked how to calculate 2nd last cell.

    Problem solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1