[SOLVED] Referencing to 2nd latest cell

**WeirdMan** · 02-26-2010, 12:26 PM

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

**6StringJazzer** · 02-26-2010, 12:44 PM

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)

**JeanRage** · 02-26-2010, 12:44 PM

Hi,

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

**WeirdMan** · 02-26-2010, 01:09 PM

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

**sailepaty** · 02-26-2010, 01:22 PM

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

**WeirdMan** · 02-26-2010, 02:00 PM

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

**sailepaty** · 02-26-2010, 02:07 PM

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

**6StringJazzer** · 02-26-2010, 02:08 PM

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.)

**6StringJazzer** · 02-26-2010, 02:11 PM

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.

**WeirdMan** · 02-26-2010, 11:14 PM

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.