| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Array formula copying blanks as zeros
Hi all,
(Excel 2003) Im using an array formula to copy certain rows of data from a table at the top of my worksheet, to another table further down the sheet (rows copied dependent on a certain cell entry in the original top table): {=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$ C$902)),1)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$9 02=C1089,ROW($C$3:$C$902)),1),COLUMN())))} This works fine except where there are blank cells in my transferred data row. I need the cells to remain blank when transferred down the worksheet but excel is turning the blank cells into a 0 (zero) in my lower table. Ive been trying to alter my formula to specifically check for blank cells in my original table (to return a blank), and have also tinkered with working an ISBLANK expression into my formula, but cant hit the right combination to get the formula to work. Would appreciate any help to get me over this one. Many thanks for looking. Steve |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Array formula copying blanks as zero's
What type of data is the formula returning? Is it text? Is it numeric? Can
it be both? -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi all, (Excel 2003) I'm using an array formula to copy certain rows of data from a table at the top of my worksheet, to another table further down the sheet (rows copied dependent on a certain cell entry in the original top table): {=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$ C$902)),1)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$9 02=C1089,ROW($C$3:$C$902)),1),COLUMN())))} This works fine except where there are blank cells in my transferred data row. I need the cells to remain blank when transferred down the worksheet but excel is turning the blank cells into a 0 (zero) in my lower table. I've been trying to alter my formula to specifically check for blank cells in my original table (to return a blank), and have also tinkered with working an ISBLANK expression into my formula, but can't hit the right combination to get the formula to work. Would appreciate any help to get me over this one. Many thanks for looking. Steve |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Array formula copying blanks as zero's
Hi,
The transferred rows of data contain text, a date, whole numbers and numbers to 2 decimal places so yes, it is both. "T. Valko" wrote: What type of data is the formula returning? Is it text? Is it numeric? Can it be both? -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi all, (Excel 2003) I'm using an array formula to copy certain rows of data from a table at the top of my worksheet, to another table further down the sheet (rows copied dependent on a certain cell entry in the original top table): {=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$ C$902)),1)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$9 02=C1089,ROW($C$3:$C$902)),1),COLUMN())))} This works fine except where there are blank cells in my transferred data row. I need the cells to remain blank when transferred down the worksheet but excel is turning the blank cells into a 0 (zero) in my lower table. I've been trying to alter my formula to specifically check for blank cells in my original table (to return a blank), and have also tinkered with working an ISBLANK expression into my formula, but can't hit the right combination to get the formula to work. Would appreciate any help to get me over this one. Many thanks for looking. Steve . |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Array formula copying blanks as zero's
yes, it is both.
Well, that doesn't help matters! Try this (array entered): =IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C $902)),1)),"",IF(INDEX(INDIRECT(ADDRESS(LARGE(IF($ C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN())),1 )="","",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C108 9,ROW($C$3:$C$902)),1),COLUMN())))) -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi, The transferred rows of data contain text, a date, whole numbers and numbers to 2 decimal places so yes, it is both. "T. Valko" wrote: What type of data is the formula returning? Is it text? Is it numeric? Can it be both? -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi all, (Excel 2003) I'm using an array formula to copy certain rows of data from a table at the top of my worksheet, to another table further down the sheet (rows copied dependent on a certain cell entry in the original top table): {=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$ C$902)),1)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$9 02=C1089,ROW($C$3:$C$902)),1),COLUMN())))} This works fine except where there are blank cells in my transferred data row. I need the cells to remain blank when transferred down the worksheet but excel is turning the blank cells into a 0 (zero) in my lower table. I've been trying to alter my formula to specifically check for blank cells in my original table (to return a blank), and have also tinkered with working an ISBLANK expression into my formula, but can't hit the right combination to get the formula to work. Would appreciate any help to get me over this one. Many thanks for looking. Steve . |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Array formula copying blanks as zero's
Cheers Biff, works a treat.
"T. Valko" wrote: yes, it is both. Well, that doesn't help matters! Try this (array entered): =IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C $902)),1)),"",IF(INDEX(INDIRECT(ADDRESS(LARGE(IF($ C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN())),1 )="","",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C108 9,ROW($C$3:$C$902)),1),COLUMN())))) -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi, The transferred rows of data contain text, a date, whole numbers and numbers to 2 decimal places so yes, it is both. "T. Valko" wrote: What type of data is the formula returning? Is it text? Is it numeric? Can it be both? -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi all, (Excel 2003) I'm using an array formula to copy certain rows of data from a table at the top of my worksheet, to another table further down the sheet (rows copied dependent on a certain cell entry in the original top table): {=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$ C$902)),1)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$9 02=C1089,ROW($C$3:$C$902)),1),COLUMN())))} This works fine except where there are blank cells in my transferred data row. I need the cells to remain blank when transferred down the worksheet but excel is turning the blank cells into a 0 (zero) in my lower table. I've been trying to alter my formula to specifically check for blank cells in my original table (to return a blank), and have also tinkered with working an ISBLANK expression into my formula, but can't hit the right combination to get the formula to work. Would appreciate any help to get me over this one. Many thanks for looking. Steve . . |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Array formula copying blanks as zero's
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Cheers Biff, works a treat. "T. Valko" wrote: yes, it is both. Well, that doesn't help matters! Try this (array entered): =IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C $902)),1)),"",IF(INDEX(INDIRECT(ADDRESS(LARGE(IF($ C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN())),1 )="","",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C108 9,ROW($C$3:$C$902)),1),COLUMN())))) -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi, The transferred rows of data contain text, a date, whole numbers and numbers to 2 decimal places so yes, it is both. "T. Valko" wrote: What type of data is the formula returning? Is it text? Is it numeric? Can it be both? -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi all, (Excel 2003) I'm using an array formula to copy certain rows of data from a table at the top of my worksheet, to another table further down the sheet (rows copied dependent on a certain cell entry in the original top table): {=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$ C$902)),1)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$9 02=C1089,ROW($C$3:$C$902)),1),COLUMN())))} This works fine except where there are blank cells in my transferred data row. I need the cells to remain blank when transferred down the worksheet but excel is turning the blank cells into a 0 (zero) in my lower table. I've been trying to alter my formula to specifically check for blank cells in my original table (to return a blank), and have also tinkered with working an ISBLANK expression into my formula, but can't hit the right combination to get the formula to work. Would appreciate any help to get me over this one. Many thanks for looking. Steve . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT with trying to create array of non-blanks | Excel Worksheet Functions | |||
| Sumproduct copying blanks or how to insert zero into blanks | Excel Worksheet Functions | |||
| Conditional Average Array with Dates, <blanks, 0 | Excel Worksheet Functions | |||
| Copying an Array Formula | Excel Discussion (Misc queries) | |||
| Copying an array formula... | Excel Worksheet Functions | |||
![Struggling in Sheffield[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
