In article ,
"Sam via OfficeKB.com" wrote:
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<"",To tal-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!
|