Need help with dynamic range selection in formula

randyharris

Board Regular
Joined
Oct 6, 2003
Messages
88
This one is stumping me, would appreciate some ideas.

I have three columns:
  • Column A contains monthly return % figures
  • Column B tracks the drawdown%
  • Column C indicates when the drawdown hits 15% or more (negative)
I'm hoping for a single formula in column B which adjusts the starting point based on the "Yes" indicators in column C.

The drawdown formula starting in cell B2 goes from the range $A$1:A1, and as the formula goes down it goes from $A$1:current row.

What I'm trying to do is whenever column C shows "Yes", then then in column B, the row after the "Yes" would instead of starting from $A$1, would start from the row after the "Yes" Row.

In the attached picture, cell B34, I would want it to be looking at the range $A$34:A34, instead of $A$1:A34. And if cell C50 had a "Yes", then B50 would be looking at the range $A$51:A51

Was thinking about maybe incorporating a check for the YES and if there, using the Offset formula, but couldn't figure it out.
 

Attachments

  • Screen Shot 2020-10-29 at 2.23.25 PM.png
    Screen Shot 2020-10-29 at 2.23.25 PM.png
    208.1 KB · Views: 5

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
Your example doesnt make complete sense to me. You use row 1 with the formula in row 2 then later on you use the same row for where you want to look and the formula. However you can use some sort of index setup. Ive used it with a SUM formula here as an example:

=SUM(INDEX($A$1:A1,IF(ISNUMBER(MATCH("yes",$C$1:C1,0)),LOOKUP("yes",$C$1:C1,ROW($C$1:C1)),1)):INDEX($A$1:A1,ROW(A1)))

This will move the sum range based on seeing yes in column C. Use the evaluate formula tool to see what its doing as it moves down the column.
 

randyharris

Board Regular
Joined
Oct 6, 2003
Messages
88
Steve the Fish (haha)

Sorry I didn't explain it as clearly as it should have been. Your formula is really close, but I'm having trouble adapting it.

If you look at the image below, the formula in Column B is a VBA function, drawdown.
  • B2 is looking at the range of $A$2:A2
  • B3 looks at the drawdown range of $A$2:A3, and so on down the column

The Trigger column C is what I hope to use the name the drawdown range dynamic.
  • Row 33 is a Trigger "Yes", this means that
  • cell B34, seeing the Trigger in C33, it would change the range it looks at from $A$2:A34 to $A$34:B34
So the drawdown formula in Column B, I'm hoping to start at the current row instead of A2 when there is a Yes in column C, (one row above the current formula in column B's row")



Screen Shot 2020-10-30 at 2.04.56 PM.jpg
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
Try this:

=DRAWDOWN(INDEX($A$2:A2,IFERROR(LOOKUP(2,1/($C$2:C2="yes"),ROW($C$2:C2)-1),1)):INDEX($A$2:A2,ROW(A2)-1))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,876
Messages
5,627,421
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top