Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cells
I am looking to find the sum of all the last 3 figures in Column K (Note:
Column K contains empty cells between the figures). Also, I am looking for the sum of all the last 12 figures in that same column (Column K). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cells
Was anything wrong with either of the two answers you got 2.5 hours ago to
this identical question that you posted in the worksheets.function newsgroup??? Rick "Loadmaster" wrote in message ... I am looking to find the sum of all the last 3 figures in Column K (Note: Column K contains empty cells between the figures). Also, I am looking for the sum of all the last 12 figures in that same column (Column K). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cells
On Sun, 13 Jul 2008 13:14:02 -0700, Loadmaster
wrote: I am looking to find the sum of all the last 3 figures in Column K (Note: Column K contains empty cells between the figures). Also, I am looking for the sum of all the last 12 figures in that same column (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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cel
My apologize, for the double entry and my tardiness in responding. The emails
that I received had blank pages for the replies. I searched the website looking for my initial query and found I was looking in the excel worksheet functions instead. Anyhow, the reason why the formula didnt work is because I explained it wrong. 1 A B C D etc to K 2 Sqn Year Month Day Monthly Totals 161 2008 Apr 23 36.7 162 2008 May 29 6.1 167 2008 Jun 28 23.4 As you can see from above the most recent entries are at the bottom of the column, Row 167 onward. There are various empty cells between the figures in column K depending on how many hours I fly in any one particular month. All other cell Columns have entries in them even it the squadron, month, or year is repeated within the columns. "Rick Rothstein (MVP - VB)" wrote: Was anything wrong with either of the two answers you got 2.5 hours ago to this identical question that you posted in the worksheets.function newsgroup??? Rick "Loadmaster" wrote in message ... I am looking to find the sum of all the last 3 figures in Column K (Note: Column K contains empty cells between the figures). Also, I am looking for the sum of all the last 12 figures in that same column (Column K). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cel
This must be a tough formula to figure out with the new perameters i just
specified this morning. "Ron Rosenfeld" wrote: On Sun, 13 Jul 2008 13:14:02 -0700, Loadmaster wrote: I am looking to find the sum of all the last 3 figures in Column K (Note: Column K contains empty cells between the figures). Also, I am looking for the sum of all the last 12 figures in that same column (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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cel
On Wed, 16 Jul 2008 11:56:00 -0700, Loadmaster
wrote: This must be a tough formula to figure out with the new perameters i just specified this morning. "Ron Rosenfeld" wrote: On Sun, 13 Jul 2008 13:14:02 -0700, Loadmaster wrote: I am looking to find the sum of all the last 3 figures in Column K (Note: Column K contains empty cells between the figures). Also, I am looking for the sum of all the last 12 figures in that same column (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 Not really. What "new" parameters? I thought you just wanted the last (bottom) 3 or 12 entries? What is hard is when you don't give us any information as to the problems with the solutions offered. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cel
I do want the last 3 and 12 entries in column K. My first post this morning
showed you the layout of the spreadsheet. What I was looking for did not work. It seems like the formula you gave me was looking for a larger number each month, this is not the case. Column K just has the monthly flying hours, some months are smaller/larger than others. The amount of empty cells between the monthly figure varies with however many flights I flew that month. "Ron Rosenfeld" wrote: On Wed, 16 Jul 2008 11:56:00 -0700, Loadmaster wrote: This must be a tough formula to figure out with the new perameters i just specified this morning. "Ron Rosenfeld" wrote: On Sun, 13 Jul 2008 13:14:02 -0700, Loadmaster wrote: I am looking to find the sum of all the last 3 figures in Column K (Note: Column K contains empty cells between the figures). Also, I am looking for the sum of all the last 12 figures in that same column (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 Not really. What "new" parameters? I thought you just wanted the last (bottom) 3 or 12 entries? What is hard is when you don't give us any information as to the problems with the solutions offered. --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cel
On Wed, 16 Jul 2008 16:54:11 -0700, Loadmaster
wrote: I do want the last 3 and 12 entries in column K. My first post this morning showed you the layout of the spreadsheet. What I was looking for did not work. It seems like the formula you gave me was looking for a larger number each month, this is not the case. Column K just has the monthly flying hours, some months are smaller/larger than others. The amount of empty cells between the monthly figure varies with however many flights I flew that month How did you conclude that the formula I gave you was "looking for a larger number each month"? The formula was designed to "look for" the three numeric entries (or 12) in the highest numbered rows, and it works as designed here. So please post some real examples of your data, a true copy of what you did with my formula, and the results you obtained. --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cel
Ron, I found what my error was in the formula after playing around with it
for a long while. I had a ) in place of a } and I was putting the word rng vice inputting the range. Thank-you very much for your help. "Ron Rosenfeld" wrote: On Wed, 16 Jul 2008 16:54:11 -0700, Loadmaster wrote: I do want the last 3 and 12 entries in column K. My first post this morning showed you the layout of the spreadsheet. What I was looking for did not work. It seems like the formula you gave me was looking for a larger number each month, this is not the case. Column K just has the monthly flying hours, some months are smaller/larger than others. The amount of empty cells between the monthly figure varies with however many flights I flew that month How did you conclude that the formula I gave you was "looking for a larger number each month"? The formula was designed to "look for" the three numeric entries (or 12) in the highest numbered rows, and it works as designed here. So please post some real examples of your data, a true copy of what you did with my formula, and the results you obtained. --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of last 3 figures in a column which also contain empty cel
Ron, I want to place the answer on another spreadsheet within the same
workbook. Where would I insert the name of the worksheet within the formula? "Loadmaster" wrote: Ron, I found what my error was in the formula after playing around with it for a long while. I had a ) in place of a } and I was putting the word rng vice inputting the range. Thank-you very much for your help. "Ron Rosenfeld" wrote: On Wed, 16 Jul 2008 16:54:11 -0700, Loadmaster wrote: I do want the last 3 and 12 entries in column K. My first post this morning showed you the layout of the spreadsheet. What I was looking for did not work. It seems like the formula you gave me was looking for a larger number each month, this is not the case. Column K just has the monthly flying hours, some months are smaller/larger than others. The amount of empty cells between the monthly figure varies with however many flights I flew that month How did you conclude that the formula I gave you was "looking for a larger number each month"? The formula was designed to "look for" the three numeric entries (or 12) in the highest numbered rows, and it works as designed here. So please post some real examples of your data, a true copy of what you did with my formula, and the results you obtained. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
repeating data in column to empty cells below without overwriting | Excel Worksheet Functions | |||
how insert same text in empty cells in column (10000 rows) | Excel Worksheet Functions | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) |