Dynamic Ranges

tgsatchmo

New Member
Joined
Aug 27, 2014
Messages
3
Hello,

I am carrying a formula down column c that looks for a specific trigger word in the adjacent cell in column b, and when that word is found the formula will look for the max value in a range that starts with the column where the specific trigger word is located and extends down 20 rows. Then, as the formula makes its way down each row within that 20 row range, the range that I looking for the max value in remains fixed.

This I have no problem with creating...my problem begins when I carry the formula down past this 20 row range until the next trigger word is located and I want the new range to begin at and remain fixed to the location of the new trigger word. Any help is greatly appreciated.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
C2 copied down:

=IF($B2="JAD",MAX(OFFSET($A2,0,0,20)),"")

Replace JAD with the appropriate trigger.


Thank you very much for your response. However, my explanation may have fallen a bit short. While your solution does work, I need the Max reading within the range to show up in the exact row that it occurs in, not where the trigger word is. For example, if my trigger word is in C10, which initiates a range of C10 - C20, but the Max reading does not occur until row 13, I need that Max reading to show up in row 13. Is this possible?
 
Upvote 0
Thank you very much for your response. However, my explanation may have fallen a bit short. While your solution does work, I need the Max reading within the range to show up in the exact row that it occurs in, not where the trigger word is. For example, if my trigger word is in C10, which initiates a range of C10 - C20, but the Max reading does not occur until row 13, I need that Max reading to show up in row 13. Is this possible?

Try to post a sample with a window size of 3 rows (instead of 20) along with desired outcome in column C.
 
Upvote 0
Try to post a sample with a window size of 3 rows (instead of 20) along with desired outcome in column C.

Aladin Akyurek , thank you. I have attached an example of what I am looking to do. If you need moer info. let me know.
MESample.gif
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top