Opportunity cost calculation

Hello,

Need help with opportunity cost calculation

If there is more data set for the item ie in rows, need to calculate based on the difference in prices

More details in the attachment.

Thank you

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned. Always upload a truly representative sample.

For instance is the data always sorted by Item #
Does every row contain an item #, A5 & A6 don't contain a reference, any function solution is almost certainly going to need that.
You don't say what your Excel version is so we don't know whether you have all the latest functions.

That said, and assuming there are item #s in each row then in E2 copied down

Formula:

Please Login or Register  to view this content.

If you change your structure to fill in the blanks on column A with all the numbers (rather than leaving as blank)

then this might assist in F2 as an array formula (control + shift + enter) and dragged down

Please Login or Register  to view this content.

and this in G2 and drag down

Please Login or Register  to view this content.