Help to adapt Formula syntax to work with Dynamic Named Ranges

[Sam via OfficeKB.com]

Hi All,

Is it possible to adapt Formulas using A1 style notation to use Dynamic
Named Ranges?

1. Find the LAST numeric value in a single column of a Dynamic named Range.
The column also contains valid zero’s and invalid blanks (empty cells).

=INDEX(A:A,MATCH(9.99999999999999E307,A:A))

=INDEX(COST,MATCH(9.99999999999999E307,COST)) ?


2. Sum the LAST 5 numeric values in a single column Dynamic named Range .
The column also contains valid zero’s and invalid blanks (empty cells).


3. Sum the 5 LARGEST numeric values. I have 20 Columns and 500 Rows of
data. The Range is called Database. I need to use two columns from Database
to provide the answer. The first column I reference is called Total – that
holds the 5 Largest values, then I need to SUM the 5 corresponding values
in Column 18.

=INDEX(Database,SUM(LARGE(Total,Row(1:5))),18) ?


Assistance very much appreciated.

Regards,
Sam

 

[Domenic]

1. Find the LAST numeric value in a single column of a Dynamic named Range.
The column also contains valid zero’s and invalid blanks (empty cells).
=LOOKUP(9.99999999999999E+307,Cost)

2. Sum the LAST 5 numeric values in a single column Dynamic named Range .
The column also contains valid zero’s and invalid blanks (empty cells).

=SUM(N(OFFSET(A1,LARGE(IF(Cost<>"",ROW(Cost)),{1,2,3,4,5})-ROW(INDEX(Cost
,1)),0)))

....confirmed with CONTROL+SHIFT+ENTER.

3. Sum the 5 LARGEST numeric values. I have 20 Columns and 500 Rows of
data. The Range is called Database. I need to use two columns from Database
to provide the answer. The first column I reference is called Total – that
holds the 5 Largest values, then I need to SUM the 5 corresponding values
in Column 18.

=SUM(N(OFFSET(Database,MATCH(LARGE(IF(Total<>"",Total-ROW(Total)/10^10),{
1,2,3,4,5}),Total-ROW(Total)/10^10,0)-1,17,1)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for assistance. Your Formula for No.3 provided the expected
result - Thank you. Would you explain the syntax used and what does this
do: Total-ROW(Total)/10^10,0)-1,17,1?

3. Sum the 5 LARGEST numeric values. I have 20 Columns and 500 Rows of
data. The Range is called Database. I need to use two columns from Database
to provide the answer. The first column I reference is called Total – that
holds the 5 Largest values, then I need to SUM the 5 corresponding values
in Column 18.

=SUM(N(OFFSET(Database,MATCH(LARGE(IF(Total<>"",Total-ROW(Total)/10^10),{
1,2,3,4,5}),Total-ROW(Total)/10^10,0)-1,17,1)))
....confirmed with CONTROL+SHIFT+ENTER.

However, for No.1 and No.2 I get an incorrect zero. Can you think of
anything that may cause this in my Dynamic Range?

1. Find the LAST numeric value in a single column of a Dynamic named
Range.The column also contains valid zero’s and invalid blanks (empty cells)
..
=LOOKUP(9.99999999999999E+307,Cost)

2. Sum the LAST 5 numeric values in a single column Dynamic named Range.
The column also contains valid zero’s and invalid blanks (empty cells).
=SUM(N(OFFSET(A1,LARGE(IF(Cost<>"",ROW(Cost)),{1,2,3,4,5})-ROW(INDEX(Cost,1)
),0)))
....confirmed with CONTROL+SHIFT+ENTER.

Further assistance most appreciated.

Thanks,
Sam

 

[Domenic]

Hi Sam,

When you said that the column contains valid zero's and invalid blanks,
I thought you wanted zero values included in the evaluation. But if you
want to exclude them, change the first two formulas to the following...

=LOOKUP(9.99999999999999E+307,IF(Cost>0,Cost))

=SUM(N(OFFSET(A1,LARGE(IF(Cost>0,ROW(Cost)),{1,2,3,4,5})-ROW(INDEX(Cost,1
)),0)))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER. With
regards to the third formula, it can be modified to eliminate the IF
function. It's not necessary. Therefore, change the formula to the
following...

=SUM(N(OFFSET(Database,MATCH(LARGE(Total-ROW(Total)/10^10,{1,2,3,4,5}),To
tal-ROW(Total)/10^10,0)-1,17,1)))

....confirmed with CONTROL+SHIFT+ENTER.

Do the first two formulas now give you what you're looking for?

 

[Sam via OfficeKB.com]

Hi Domenic,

I do want zero values included in the evaluation. However, the result that
is returned is zero but it is not the LAST numeric value in the column. Any
ideas on what may be wrong?

1. Find the LAST numeric value in a single column of a Dynamic named
Range.The column also contains valid zero’s and invalid blanks (empty cells)
..

=LOOKUP(9.99999999999999E+307,Cost)

Further help appreciated.

Thanks
Sam

 

[Domenic]

The formula...

=LOOKUP(9.99999999999999E+307,Cost)

should give you the last numerical value in the column. Maybe the last
number you see in the column is actually formatted as text. Try the
following...

1) Select an empty cell

2) Edit > Copy

3) Select your column

4) Edit > Paste Special > Add > Ok

Does this help?

Also, regarding the third formula, you may want to stick with the
original one I gave which includes the IF function. Otherwise, the
formula will sum the corresponding values for blank cells in cases where
there's less than 5 numbers in the column.

 

[Sam via OfficeKB.com]

Hi Domenic,

I don't know why your suggested Formula's for my No.1 and No.2 scenarios
return zero but the following may have some bearing:

I've taken a closer look at the actual content of the cells in the single
column Dynamic Range. The value is based on an underlying SUM Formula using
logical values. This Formula displays the individual numeric values that
reside in each cell. The distinction I'm trying to make is that the numeric
values being looked-up are the result of a calulated Formula that reside in
the same cell and not just a pure numeric value that stands on its own in
each cell. So, LOOKUP sees actual cell content as eg: SUM(($H16<=350)+(
$J16<=350)+($L16<=350)) but the actual displayed numeric value in the cell
is 2.

Could this be the reason for the incorrect zero results from your Formulas?
If so, is there a workaround so that LOOKUP sees the actual displayed
numeric value rather than the Formula content.

Further help appreciated.

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

Tried suggested check for incorrect TEXT formatting. The whole column is
formatted as GENERAL - no joy.

Your help is most appreciated.

Thanks
Sam

 

[Domenic]

Hi Sam,

The fact that the numerical values in your column are derived as a
result of formulas should make no difference.

So, I'm not sure why the last value is not being recognized as a
numerical one. Trying testing it with the following...

=ISNUMBER(Cell Reference)

What do you get as a result?

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for ongoing help. As suggested, I tried recommended Formula:

=ISNUMBER(Cell Reference)

It returned the result TRUE.

Appreciate, if anything else comes to mind.

Thanks
Sam

 

[Domenic]

Can you confirm that the dynamic range 'Cost' refers to a single column
only?

 

[Sam via OfficeKB.com]

Hi Domenic,

Dynamic Range Cost is a single column.

Unfortunately, I'm still getting to grips with how Dynamic Ranges work.

However, I think this may be the root of my problem -
This is the Formula in the Define Name Refers To Box:
=OFFSET(Stock!$R$70,1,0,COUNT(Stock!$R:$R)-1,1)

There is the column heading COST in Row 70 and the actual mumeric data
starts in Row 71.

There is a numeric value in column R above the start of the Named Range
Cost in cell R33 but gets included in the Dynamic Range stretching the full
length of the column using COUNT(Stock!$R:$R)-1,1).

Is it possible to still keep the Range Dynamic starting at Row 71 but
somehow restrict COUNT(Stock!$R:$R)-1,1)so that it starts from Row 71 and
not Row 1 including the entire length of the column.

Thanks
Sam

 

[Domenic]

Ah yes! There's your problem! Try defining your range using the
following formula instead...

=Stock!$R$71:INDEX(Stock!$R$71:$R$65536,MATCH(9.99999999999999E+307,Stock
!$R$71:$R$65536))

Does this help?

 

[Sam via OfficeKB.com]

Hi Domenic,

That's Perfect:-

=Stock!$R$71:INDEX(Stock!$R$71:$R$65536,MATCH(9.99999999999999E+307,Stock
!$R$71:$R$65536))

Thank you very much for all your time and patience. Most appreciated.

Thanks
Sam