Excel stepped formula

[RundertheC]

Hi,

I hope someone can assist me with a formula to apply to a large dataset. Volumes are currently both produced and sold simultaneously, with the price per hour fluctuating. The objective is to sell the volumes produced at the highest available price on the day that it gets produced. However, there is a limit to the volumes that can be sold on an hourly basis. Therefore, the formula should consider the daily volumes and aim to sell them at the highest prices available throughout the day.

[#2 · Accepted solution]

@RundertheC Here's one possibility:

=MIN(MAX(SUMIFS(volume, dates, date) - COUNTIFS(dates, date, prices, ">" & price) * 100, 0) / COUNTIFS(dates, date, prices, price), 100)

With range references, it would look like this:

=MIN(MAX(SUMIFS($D$2:$D$21, $A$2:$A$21, A2) - COUNTIFS($A$2:$A$21, A2, $C$2:$C$21, ">" & C2) * 100, 0) / COUNTIFS($A$2:$A$21, A2, $C$2:$C$21, C2), 100)

See attached sample workbook, if needed...

[#3]

@djclements you're too good. That's perfect. Thank you!