Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flying Hours
I would like to have the sum of the last 3 entries in a column that have
spaces between the figures. I also need the sum of the last 12 entries in a column that have spaces between the figures. Note: These figures are all in column K. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flying Hours
hi
can you post examples of your data that has "spaces between the figures"? regards FSt1 "Loadmaster" wrote: I would like to have the sum of the last 3 entries in a column that have spaces between the figures. I also need the sum of the last 12 entries in a column that have spaces between the figures. Note: These figures are all in column K. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flying Hours
Try this formula for last three numbers in K1:K100 (extend if necessary)
=SUM(IF(ROW(K1:K100)*ISNUMBER(K1:K100)=LARGE(ROW( K1:K100)*ISNUMBER(K1:K100),3),K1:K100)) This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar For last 12 just change the 3 to a 12. If there are less than 3 (or 12) numbers then all numbers will be summed "FSt1" wrote: hi can you post examples of your data that has "spaces between the figures"? regards FSt1 "Loadmaster" wrote: I would like to have the sum of the last 3 entries in a column that have spaces between the figures. I also need the sum of the last 12 entries in a column that have spaces between the figures. Note: These figures are all in column K. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flying Hours
While I'm sure there must be a shorter solution, you can use the following
array-entered** formula to do what you asked... =INDEX(K:K,LARGE(ROW(K1:K1000)*(K1:K1000<""),1))+ INDEX(K:K,LARGE(ROW(K1:K1000)*(K1:K1000<""),2))+I NDEX(K:K,LARGE(ROW(K1:K1000)*(K1:K1000<""),3)) ** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. I assumed a maximum data row of 1000, adjust as necessary for your actual setup. Rick "Loadmaster" wrote in message ... I would like to have the sum of the last 3 entries in a column that have spaces between the figures. I also need the sum of the last 12 entries in a column that have spaces between the figures. Note: These figures are all in column K. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flying Hours
If you only have numbers and/or spaces, try this *array* formula:
=SUM(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100< ""),3))) If there might be text within the range, try this *array* formula: =SUM(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100< "")*ISNUMBER(A1:A100),3)) ) Replace the 3 with a 12 to sum the last 12 rows. -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Loadmaster" wrote in message ... I would like to have the sum of the last 3 entries in a column that have spaces between the figures. I also need the sum of the last 12 entries in a column that have spaces between the figures. Note: These figures are all in column K. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flying Hours
On Sun, 13 Jul 2008 10:32:00 -0700, Loadmaster
wrote: I would like to have the sum of the last 3 entries in a column that have spaces between the figures. I also need the sum of the last 12 entries in a column that have spaces between the figures. Note: These figures are all in column K. In general, this **array-entered** formula: =SUM(LOOKUP(LARGE(ISNUMBER(rng)*ROW(rng),{1,2,3}), ROW(rng),rng)) To **array-enter** a formula, hold down <ctrl<shift while hitting <enter. If you did it correctly, Excel will place braces {...} around the formula. For versions of Excel prior to 2007, rng cannot refer to the entire column. So the largest rng would be K1:K65535. In Excel 2007, rng could refer to the entire column K:K For the top 12: =SUM(LOOKUP(LARGE(ISNUMBER(rng)*ROW(rng),ROW(INDIR ECT("1:12"))),ROW(rng),rng)) also **array-entered** with <ctrl<shift<enter. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Flying Bricks Chart | Charts and Charting in Excel | |||
Anyone have a template for a flying club? | Charts and Charting in Excel | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
Calculating Flying hours allowing for GMT or BST | Excel Worksheet Functions | |||
Convert hours and minutes in time format into fractions of hours.. | Excel Worksheet Functions |