Locate multiple lowest prices for high value and show savings

[Standing bear]

Hi I have a group of items all that are the same product but sold in different pack amounts. I need to have a formula that will show the higher pack amounts have a savings and are not priced above the lower options. Is there a way to do this maybe with minifs?


Example below:
Product is batteries
This is sold as 1/each; 4/ pack; 12/dozen; 24/carton

UOM. Pack Amt. Sell price.

Each. 1. 4

Pack. 4. 10.

Dozen. 12. 15.

Carton. 24.. 20.

But how can I show per line the unit price for each and the resulting price that is and then also of the price is better or the same or higher?

For instance the 12/dozen is sold for $15 and the lower units come as 1/each for $4 and the second lowest option is the 4/pack sold for $10. In this case I am looking to see if the 4/pack is below the each and then also if the 12/dozen is below the 4/pack and also the 1/each.

But is there a way to break this out in columns to show the multiple options there could be based on the higher unit what the lower units available are and if there is a savings?

 

[ETAF]

which version of excel are you using
depending on how the actual data is structured - maybe able to use a lookup , but as i say really denpends on the structure and information in the spreadsheet

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

 

[Peter Bartholomew]

This shows a long and boring 365 formula that does something to sort the problems present within the original data.



From there is remains to sort the best price for each quantity.