| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cells
Hi all
Have numbers on column D, that are placed there by using the following formula: =IF(B14<=2,C14,0). This formula is copied down until the 400th row (some times more). I am looking for a way (formula) to, in column E, have as result last 20 rows of non blank data. I.E B C D E 4 200 0 96 4 200 0 96 2 96 96 52 2 96 96 3 141 0 1 52 52 Tks in advace |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Blank cells
I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Antonio wrote: Hi all Have numbers on column D, that are placed there by using the following formula: =IF(B14<=2,C14,0). This formula is copied down until the 400th row (some times more). I am looking for a way (formula) to, in column E, have as result last 20 rows of non blank data. I.E B C D E 4 200 0 96 4 200 0 96 2 96 96 52 2 96 96 3 141 0 1 52 52 Tks in advace -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Blank cells
Tks Dave
When I enter the formula the outcome is "False" Can u pls help in correcting this?? Tks in advance "Dave Peterson" wrote: I selected E1:E20 and used this formula: =IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Antonio wrote: Hi all Have numbers on column D, that are placed there by using the following formula: =IF(B14<=2,C14,0). This formula is copied down until the 400th row (some times more). I am looking for a way (formula) to, in column E, have as result last 20 rows of non blank data. I.E B C D E 4 200 0 96 4 200 0 96 2 96 96 52 2 96 96 3 141 0 1 52 52 Tks in advace -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Blank cells
Did you try to retype the formula? Safer to copy and paste, then you'll get
it right. -- David Biddulph "Antonio" wrote in message ... Tks Dave When I enter the formula the outcome is "False" Can u pls help in correcting this?? Tks in advance "Dave Peterson" wrote: I selected E1:E20 and used this formula: =IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Antonio wrote: Hi all Have numbers on column D, that are placed there by using the following formula: =IF(B14<=2,C14,0). This formula is copied down until the 400th row (some times more). I am looking for a way (formula) to, in column E, have as result last 20 rows of non blank data. I.E B C D E 4 200 0 96 4 200 0 96 2 96 96 52 2 96 96 3 141 0 1 52 52 Tks in advace -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Blank cells
hi David
Tryed it also, but it seems not to work, as the result as follows: B C D E 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 0 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 0 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 4 200 0 4 200 0 4 96 0 2 96 96 2 96 96 3 141 0 1 52 52 Rgds António "David Biddulph" wrote: Did you try to retype the formula? Safer to copy and paste, then you'll get it right. -- David Biddulph "Antonio" wrote in message ... Tks Dave When I enter the formula the outcome is "False" Can u pls help in correcting this?? Tks in advance "Dave Peterson" wrote: I selected E1:E20 and used this formula: =IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Antonio wrote: Hi all Have numbers on column D, that are placed there by using the following formula: =IF(B14<=2,C14,0). This formula is copied down until the 400th row (some times more). I am looking for a way (formula) to, in column E, have as result last 20 rows of non blank data. I.E B C D E 4 200 0 96 4 200 0 96 2 96 96 52 2 96 96 3 141 0 1 52 52 Tks in advace -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Blank cells
If you're still getting the FALSE result, copy your formula back from the
formula bar to the group here. Did Excel add the curly brackets when you did Control Shift Enter? [But if you didn't do that, I'd expect a result of #VALUE!, not FALSE, so I still suspect an erroneously typed formula.] I'm confused that you say the formula didn't work and that you got the result FALSE, as the values you give below seem to show the correct values in E1:E20 as one would expect from that formula, with the last 20 non-blank values from column D transferred to column E as you requested. -- David Biddulph "Antonio" wrote in message ... hi David Tryed it also, but it seems not to work, as the result as follows: B C D E 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 0 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 0 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 4 200 0 4 200 0 4 96 0 2 96 96 2 96 96 3 141 0 1 52 52 Rgds António "David Biddulph" wrote: Did you try to retype the formula? Safer to copy and paste, then you'll get it right. -- David Biddulph "Antonio" wrote in message ... Tks Dave When I enter the formula the outcome is "False" Can u pls help in correcting this?? Tks in advance "Dave Peterson" wrote: I selected E1:E20 and used this formula: =IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Antonio wrote: Hi all Have numbers on column D, that are placed there by using the following formula: =IF(B14<=2,C14,0). This formula is copied down until the 400th row (some times more). I am looking for a way (formula) to, in column E, have as result last 20 rows of non blank data. I.E B C D E 4 200 0 96 4 200 0 96 2 96 96 52 2 96 96 3 141 0 1 52 52 Tks in advace -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Blank cells
And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used. Antonio wrote: hi David Tryed it also, but it seems not to work, as the result as follows: B C D E 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 0 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 0 4 200 0 0 4 200 0 0 4 96 0 96 2 96 96 96 2 96 96 0 3 141 0 52 1 52 52 4 200 0 4 200 0 4 96 0 2 96 96 2 96 96 3 141 0 1 52 52 Rgds António "David Biddulph" wrote: Did you try to retype the formula? Safer to copy and paste, then you'll get it right. -- David Biddulph "Antonio" wrote in message ... Tks Dave When I enter the formula the outcome is "False" Can u pls help in correcting this?? Tks in advance "Dave Peterson" wrote: I selected E1:E20 and used this formula: =IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Antonio wrote: Hi all Have numbers on column D, that are placed there by using the following formula: =IF(B14<=2,C14,0). This formula is copied down until the 400th row (some times more). I am looking for a way (formula) to, in column E, have as result last 20 rows of non blank data. I.E B C D E 4 200 0 96 4 200 0 96 2 96 96 52 2 96 96 3 141 0 1 52 52 Tks in advace -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
| Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
| How to count blank cells (Rows) between Non-Blank cells in Col "A" | Excel Discussion (Misc queries) | |||
| Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
| Making Blank Cells Really Blank (Zen Koan) | Excel Worksheet Functions | |||
Hybrid Mode
