I have a table with orders for stocking in an inventory, so the same product appears multiple times in the table, however possibly with different prices as time passes. I want to make calculations with the prices for a given period.
I have a table with sold products, and a table with the purchases of raw materials for these products. I buy (more or less) the same raw materials every time, and I want to make a calculation in the products' table regarding the profit. There I need to sum up all the raw materials, but per material only the most recent purchase. So if I have bought Paper as raw material in 2010, and once more in 2012 (for a different price), I need to make the profit calculations for all my sold products since 2012 with the new Paper price. And obviously I have a lot of these raw materials.
So I was thinking about this formula, but one that works in Excel: FOR(Sold_Products[Date]>Raw_Materials[Date],SUM([Raw_Materials[Price]))
=== OLD EXAMPLE ===
See the example below:
\1
For example, I'd like to sum up all the prices before today's date. I was thinking about something like IF(TODAY()<B:B,SUM(C:C),"") but obviously the IF-statement is inappropriate here. A FOR-statement would be nice, but that doesn't exist in Excel.
Thanks a lot in advance!
Bookmarks