Difference between newest entry and last entry

Christina Riot

I am trying to find a formula that will take the most recent entry and subtract it from the last entry. I have rows and rows of inventory items and I need to calculate the difference in quantity between orders and also the amount of days between orders. As orders come onto the sheet i'd like the formula to run so i can connect to the dashboard. I have searched and searched for a formula and cannot seem to find anything to fit what i need.

Thank you!!

SoS | Dan Palenchar

Can you add a screenshot of your sheet? If the data is sensitive you could create a copy and remove the data, just leave the column headers and one fictitious example.

And can you be more specific about what you mean by "take the most recent entry and subtract it form the last entry?"

I am making a lot of assumptions with the answer below, without seeing your Sheet and understanding fully what you are trying to accomplish it is hard to know what exactly would work for you.

If you add an auto-number column to your sheet, you can have a separate sheet that automatically pulls in data from the last two entries.

In Row 1, in Column 1 you would have a formula to pull in the most recent entry using formula

=MAX({Auto Number From Inventory Sheet})

In Row 2:

=MAX({Auto Number From Inventory Sheet})-1 (you might need to add a VALUE formula around the Max)

Then, you could have the same columns as in your inventory sheet, or a subset. In these, use

=INDEX({Inventory Sheet Field to Pull In}, MATCH([Column 1], {Auto Number From Inventory Sheet}, #)

Note that depending on how the inventory sheet is sorted the "#" will be -1, 0, or 1

You could then compute the differences easily between various fields. Perhaps that would be row 3... Difference in quantity would be as simple as

=[Quantity Column]1-[Quantity Column 2]

You could accomplish the same thing by only retaining 2-3 entries on your inventory sheet by using a Move Row automation to an external sheet, and then you could compute differences in orders on the same sheet (limit to 3 rows) or an external (limit to 2 rows).