In the latest worksheet I see "Total and Profit of Closing Stock as per FIFO stock valuation Method"; this I understand. (I work in computer security, but my college degree is in Accounting.) In rows 3 through 12, various purchases increase the number of units in inventory, and to the value of that inventory (according to what you paid for each unit). In row 13 you sold some of that inventory—under FIFO, the first 32 units. You paid 44 800 rials for those 32 units, and received 64 000 rials for them. Inventory is reduced by 32 units; the value of inventory is reduced by 44 800 rials; and profit is recorded for the remaining 19 200 rials.
Whoever gave you the formula in column I used SUMPRODUCT a lot. I've never had occasion to use SUMPRODUCT, so maybe, after all the time you've spent with me, I still won't be able to help you. But I at least understand what you're doing in this worksheet, keeping track of the size and value of your inventory.
Now, how does this relate to your request? You wrote "the average difference between the settlement date and the sales date that it includes", so I think I was on the right track when I said, last time, it has to do with the number of days. But the average number of days. So going back to your second worksheet, 20200407 Newest-1.xlsb:
Row 3: The "average" number of days between payment and sale is 2.
Row 7: Settlement is 5 days after the sale.
Row 8: Remaining settlement is 15 days after the sale.
Row 12: Average of of 29 days (for the first 10K units), 20 days (the second 10K units) and 10 days (the last 10K units), which is 19 2/3 days. Got it. It happens in this case that all three sales were for the same amount, but I suppose you want to weight the averages by the size of each purchase, so if the sales amounts on those three days had been 5K, 10K and 15K units—the 5K sale being on the 1st of the month—the average would have come to 16.5 instead.
If the above is right, then I take it your original question is how to calculate that in Excel. Maybe someone much smarter than I can come up with a formula that will do it, but I think it'll have to be a VBA program. Maybe we can make it a worksheet function, one you write yourself. The question in my mind is how to track the value of inventory at each point, so that the calculation for a sale on line 293 will correctly understand which units (from which purchase date) are being sold.
We can figure that out. But first, have I finally understood and correctly described what you want?
.