Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum last 3 values
Good morning
I need to sum the last 3 values in a range that can contain empty cells. A1 2.2 A2 A3 4.3 A4 1.7 A5 2.2 A6 A7 3.4 A8 A9 1.0 A10 So I need to sum 1.0, 3.4 and 2.2. The closest I've come is a formula that sums the row numbers themselves: 9, 7, 5 and not the values in the cells. TIA Lisa |
#2
|
|||
|
|||
One way
=SUM(A1000:INDEX(A:A,LARGE(IF(A2:A1000<"",ROW(A2: A1000)),3))) entered nwith ctrl + shift & enter -- Regards, Peo Sjoblom "Lisa" wrote in message ... Good morning I need to sum the last 3 values in a range that can contain empty cells. A1 2.2 A2 A3 4.3 A4 1.7 A5 2.2 A6 A7 3.4 A8 A9 1.0 A10 So I need to sum 1.0, 3.4 and 2.2. The closest I've come is a formula that sums the row numbers themselves: 9, 7, 5 and not the values in the cells. TIA Lisa |
#3
|
|||
|
|||
Thank you! I was very close.
-----Original Message----- One way =SUM(A1000:INDEX(A:A,LARGE(IF(A2:A1000<"",ROW (A2:A1000)),3))) entered nwith ctrl + shift & enter -- Regards, Peo Sjoblom "Lisa" wrote in message ... Good morning I need to sum the last 3 values in a range that can contain empty cells. A1 2.2 A2 A3 4.3 A4 1.7 A5 2.2 A6 A7 3.4 A8 A9 1.0 A10 So I need to sum 1.0, 3.4 and 2.2. The closest I've come is a formula that sums the row numbers themselves: 9, 7, 5 and not the values in the cells. TIA Lisa . |
#4
|
|||
|
|||
On Tue, 1 Mar 2005 10:34:24 -0800, "Lisa"
wrote: Good morning I need to sum the last 3 values in a range that can contain empty cells. A1 2.2 A2 A3 4.3 A4 1.7 A5 2.2 A6 A7 3.4 A8 A9 1.0 A10 So I need to sum 1.0, 3.4 and 2.2. The closest I've come is a formula that sums the row numbers themselves: 9, 7, 5 and not the values in the cells. TIA Lisa If your range of numbers is in, let us say A1:A100, then the following *array* formula will do what you describe: =SUM(TRANSPOSE(INDIRECT(ADDRESS( LARGE(ISNUMBER(A1:A100)*ROW( INDIRECT("1:100")),{1,2,3}),1)))) To enter an *array* formula, after typing or pasting it into the cell, hold down <ctrl<shift while hitting <enter. XL will place braces {...} around the formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Hiding Values Below Z Axis Minimum Value on Axis Charts | Charts and Charting in Excel | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Leading Zeros in Numeric Values | Excel Worksheet Functions |