Excel函数优化需求：含重置逻辑的「上次信号后最低价」公式

Got it, let's break this down simply—since you're working with basic Excel skills, we'll tweak your existing formula to add that reset-on-signal functionality without getting into overly complex functions.

First, Let's Clarify the Setup #

I’m assuming:

• Column A = 实时价格（Live Price） (your only manual input)
• Column C = Signal Trigger (this is where you mark when a signal fires—either manually typing "Signal" or using an IF formula to auto-mark it, e.g., =IF(A3<90,"Signal","") if price <90 triggers a signal)
• Column B = 上次信号后最低价（Lowest since last signal） (the column we’re fixing)

The Updated Formula #

For cell B3 (assuming your header row is row 2, and data starts at row 3), use this formula:

=IF(C3="Signal", A3, MIN(INDEX(A:A, IFERROR(LOOKUP(2, 1/(C$2:C2="Signal"), ROW(C$2:C2)), 2)):A3))

Let's Break Down What Each Part Does:

• IF(C3="Signal", A3, ...): If the current row has a signal, the "lowest since last signal" is just the current live price—this is our reset trigger.
• LOOKUP(2, 1/(C$2:C2="Signal"), ROW(C$2:C2)): This finds the row number of the last signal before the current row. The 1/(...) trick turns non-signal rows into errors (which LOOKUP ignores), so it grabs the most recent signal row.
• IFERROR(..., 2): If there are no signals yet (e.g., early rows), it defaults to row 2 (your first data row) so we just calculate the running minimum from the start.
• MIN(INDEX(A:A, ...):A3): Takes the minimum of all live prices from the last signal row (or first data row) up to the current row—exactly the "lowest since last signal" value we need.

Adjust for Your Signal Rule #

If your signal isn’t a manual "Signal" tag, replace C3="Signal" with your actual trigger condition. For example:

• If signal fires when live price drops below 90: =IF(A3<90, A3, MIN(...))
• If signal uses another column’s value: =IF(D3="Trigger", A3, MIN(...))

Example Walkthrough #

Here’s how this works with sample data:

• Row 4: Signal fires, so B4 resets to A4 (90)
• Row 5: Calculates min from row 4 to 5 → 90
• Row 7: Signal fires again, B7 resets to A7 (85)
• Row 8: Calculates min from row7 to 8 → 85

Old Excel Compatibility Note #

If you’re using a pre-365/2021 Excel version, you’ll need to enter the formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter.

内容的提问来源于stack exchange，提问作者Loadwick