Forum Discussion
Difference between prices
Hello, i have almost 2000 rows of prices for a specific product (and a large number pf products). I need excel to automatically subtract the price that i will enter today and price from yesterday. And i need it to do it every time i input new prices.
Hi Tea, to get the value from yesterday, you can use SUMIFS() or SUMPRODUCT(). You could also work with XLOOKUP inside FILTER().
In my example (see also attached document) I used SUMPRODUCT. You just have to make sure that the combination of product and date of yesterday only exists once. Otherwise Excel would sum up several values here.
I hope that helps.
I'm on my mobile so i can't attach any pictures. My sheet has in first row: date, product 1,product 2,....product n.
Collumns below are filled with dates (the 1st one) and prices below product names. In another sheet i need excel to automatically calculate the diference between the last two prices whenever i input new information (new prices in first sheet). So if i kad 10/3/2022 and prices for each product and i insert 10/4/2022 and new prices, i need another sheet to subtract them as soon as I'm done in the first sheet
You can display the entries in the columns with COUNTA() and then read out the value with Index. Then simply subtract one from the index, then you have the last value minus the second to last value. But you shouldn't have any empty cells in between.
Does that fit? See also the attached sheet.
=INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B))-INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B)-1)
