Adjacent Cell to Last Cell in a Row

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am creating two columns where data is continually added. In one
column I have units either lbs or kgs listed. In the second column I
have a price. I want to create a formula that will pick the last entry
in the units column and depending on whether it was lbs or kgs
multiply the number in the cell beside it in a further calculation

lbs 3.34 kg 5.67
kg 4.50 lb 8.94
lbs 8.94 kg 5.82
lbs 4.33 lb 6.22
lbs 2.68
kg 7.48


In this case in the first example the formula would determine that kg
was the last entry and use 7.48 for further calculation.

In the second example the formula would determine that lb was the last
entry and use 6.22 and multiply by 2.2046 in a further calculation.

Thanks

Tom
 
If there are no gaps in the column you can use COUNTA(A:A) to get the row of
the last collumn, then INDEX of that column to get the item. Example:
=INDEX(A:A,COUNTA(A:A)) would pick up "kg", or INDEX(D:D,COUNTA(D:D)) would
pick up the "lb".
Then multiply as you wish.
Further example:
=IF(INDEX(A:A,COUNTA(A:A))="kg",INDEX(B:B,COUNTA(A:A)),INDEX(B:B,COUNTA(A:A))*2.2046)
 
Back
Top