Excel Stock Usage Formula

BenDukes

New Member
Joined
Jul 29, 2022
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hi I'm looking for a formula which can calculate the usage value for inventory items at different prices.

For example my at the start of July I have 20 George Foreman Grills @ 15.00 and in July I receive a delivery of 20 George Foreman Grills @ 12.50. Then in July I have used 30 George Foreman Grills on a LIFO valuation.

I'm looking for a formula which will value the 30 used.

Basically if I enter 30 in the QTY used column I want a formula which will look up to the opening stock QTY column and if that is less than or equal to the QTY used it will multiply the amount by the O/S price e.g 20 x 15 then it will say we still need 10 so move on to the next delivery and say we need 10 still and check if that is less than or equal to 10 and if it is will take the 10 multiply that by the price and then add them both together and keep moving along the delivery columns until it has met the usage value?

Is that something I can do in excel?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,182,099
Messages
5,933,642
Members
436,902
Latest member
Ameratsu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top