Dynamic ranges

[sandra_m]

Hello,

I have a sheet called “Calculation” on which column E that contains (from row 4 below) approx. 50 numbers (results from a formula) and somewhere in these 50 numbers one row that contains the text “maximum” (also as a result from a formula).

This formula is for row 4 “=IF(D4=0;"maximum";D4)” and it goes down till the last row.

On column D I have only one zero, so there will be only one “maximum” on column E.

I need 2 dynamic ranges on column E: the first one from row 4 until the row that contains “maximum” and the 2nd one from “maximum” until the last number in the column E (because in the end I need the maximums of the 2 ranges).

Could anyone help me with these ranges? Thanks a lot.

 

[Haseeb Avarakkan]

Hello Sandra, Try

For range until maximum, This will include maximum

=$D$4:INDEX($D:$D,MATCH("maximum",$D:$D,0))

For range from maximum +1 cell to down last number

INDEX($D:$D,MATCH("maximum",$D:$D,0)+1):INDEX($D:$D,MATCH(1E+100,$D:$D))

 

[Sandeep Warrier]

Hi,

Named Range 1

=INDEX(E:E,4,0):INDEX(E:E,MATCH("maximum",E:E,0)-1,0)

Named Range 2

=INDEX(E:E,MATCH("maximum",E:E,0),0):INDEX(E:E,MATCH(9.99999E307,E:E),0)

 

[Aladin Akyurek]

Hello,

I have a sheet called “Calculation” on which column E that contains (from row 4 below) approx. 50 numbers (results from a formula) and somewhere in these 50 numbers one row that contains the text “maximum” (also as a result from a formula).

This formula is for row 4 “=IF(D4=0;"maximum";D4)” and it goes down till the last row.

On column D I have only one zero, so there will be only one “maximum” on column E.

I need 2 dynamic ranges on column E: the first one from row 4 until the row that contains “maximum” and the 2nd one from “maximum” until the last number in the column E (because in the end I need the maximums of the 2 ranges).

Could anyone help me with these ranges? Thanks a lot.

Am I correct in observing that the actual range is in column D and the range in E is constructed from that of D using the formula...

=IF(D4=0;"maximum";D4)

which is entered in E4 and copied down?

 

[sandra_m]

Yes, you are correct. Column E contains from row 4 until the last row information from column D, except the row that contains "maximum" (on column D there is a zero instead of maximum).

 

[sandra_m]

I tried both solutions and they both are suitable for my requests. Thank you so much for the help you provided.

 

[Aladin Akyurek]

Yes, you are correct. Column E contains from row 4 until the last row information from column D, except the row that contains "maximum" (on column D there is a zero instead of maximum).

That would mean that you could have the dynamic named ranges constructed
directly from column D (that is, E is not not needed)...

Although there are already two replies defining the required ranges, I'll also do so...

Define Size as referring to:

=MATCH(9.99999999999999E+307;Calculation!$D:$D)

Note 1. 9.99999999999999E+307 can be shortened to: 9.99E+307.

Note 2. If your system requires comma instead of a dot for decimal numbers, replace the dot in that big number with a comma.

Define MaxPos as referring to:

=MATCH(0;Calculation!$D:$D)

Now we can define the two ranges that you require:

Define BeforeMax (or any other more convenient name) as referring to:

=Calculation!$D$4:INDEX(Calculation!$D:$D,MaxPos-1)

Define AfterMax as referring to:

=INDEX(Calculation!$D:$D,MaxPos+1):INDEX(Calculation!$D:$D,Size)

 

[sandra_m]

Hi,

I tried this and the values I receive are #N/A. Could this be perhaps because column D contains numbers that are results of a formula? For example, D9=IF(LEN(C9)=0;"";IF(C9=0;0;IF(ISNUMBER(D8);D8+C9;IF(LEN(D8)=0;C9)))).

 

[Aladin Akyurek]

Hi,

I tried this and the values I receive are #N/A. Could this be perhaps because column D contains numbers that are results of a formula? For example, D9=IF(LEN(C9)=0;"";IF(C9=0;0;IF(ISNUMBER(D8);D8+C9;IF(LEN(D8)=0;C9)))).

Hope you replaced comma's with semi-colons as I was not consistent with the latter...

What do we get if we would try the following:

=IF(ISNUMBER(D9);1;IF(D9="";"#";"@"))