Forum Discussion
Excel stepped formula
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 sel...
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...
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...
djclements you're too good. That's perfect. Thank you!
