|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 13th, 2009, 05:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum or Average with #N/A cells
I'm using a formula to bring results from another worksheet but in some cases
I get the #N/A result as I don´t have data on the original worksheet. To get the graphics on the final worksheet I use the formula with NA() not to give me 0 values on the chart and I also use the conditional formula to get the #N/A results just like the cells were empty (the same color I use for that cell) but when I print my page, I get the #N/A in black (so I can see them) and additional I'm not able to get a SUM or AVERAGE on each column as I get the #N/A for those cells with that mark. Two examples of the formula I'm using a =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))) =IF(L9/L8-1=0,NA(),L9/L8-1) How may I get the SUM or the AVERAGE for that column and how may I print my file without seeing the #N/A's in black? 
|
|
#2
April 13th, 2009, 06:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum or Average with #N/A cells
To sum a range that contains errors:
=SUMIF(A1:A10,"1E100") To average a range that contains errors: Array entered** =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. To not print errors: In Excel 2002: FilePage SetupSheet tab Under Print...Cell errors asselect blank OK -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... I'm using a formula to bring results from another worksheet but in some cases I get the #N/A result as I don´t have data on the original worksheet. To get the graphics on the final worksheet I use the formula with NA() not to give me 0 values on the chart and I also use the conditional formula to get the #N/A results just like the cells were empty (the same color I use for that cell) but when I print my page, I get the #N/A in black (so I can see them) and additional I'm not able to get a SUM or AVERAGE on each column as I get the #N/A for those cells with that mark. Two examples of the formula I'm using a =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))) =IF(L9/L8-1=0,NA(),L9/L8-1) How may I get the SUM or the AVERAGE for that column and how may I print my file without seeing the #N/A's in black?
|
|
#3
April 13th, 2009, 06:53 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum or Average with #N/A cells
You can use ISNA to suppress #N/A errors... use it like this
=IF(ISNA(your_formula),"",your_formula) ISNA returns true if the formula within ISNA returns #N/A -- Pl click the ''''Yes'''' button (if you see it - don''''t worry if you don''''t), if this answer was helpful. "PaulinaDi" wrote: I'm using a formula to bring results from another worksheet but in some cases I get the #N/A result as I don´t have data on the original worksheet. To get the graphics on the final worksheet I use the formula with NA() not to give me 0 values on the chart and I also use the conditional formula to get the #N/A results just like the cells were empty (the same color I use for that cell) but when I print my page, I get the #N/A in black (so I can see them) and additional I'm not able to get a SUM or AVERAGE on each column as I get the #N/A for those cells with that mark. Two examples of the formula I'm using a =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))) =IF(L9/L8-1=0,NA(),L9/L8-1) How may I get the SUM or the AVERAGE for that column and how may I print my file without seeing the #N/A's in black?
|
|
#4
April 13th, 2009, 09:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum or Average with #N/A cells
Thanks Sheeloo, but the problem with this formula is that I get the chart
with a line in zero although there is no value, but it helps me with the AVERAGE formula. What I really need is to make the formula not to show the #N/A value when I print the file (as it doesn´t matter I use the conditional formatting, if my cell is for example in gray I get the #N/A in black) and not to appear a line on my chart. That's why I learned (here in this forum) to use the NA() indicator. "Sheeloo" wrote: You can use ISNA to suppress #N/A errors... use it like this =IF(ISNA(your_formula),"",your_formula) ISNA returns true if the formula within ISNA returns #N/A -- Pl click the ''''Yes'''' button (if you see it - don''''t worry if you don''''t), if this answer was helpful. "PaulinaDi" wrote: I'm using a formula to bring results from another worksheet but in some cases I get the #N/A result as I don´t have data on the original worksheet. To get the graphics on the final worksheet I use the formula with NA() not to give me 0 values on the chart and I also use the conditional formula to get the #N/A results just like the cells were empty (the same color I use for that cell) but when I print my page, I get the #N/A in black (so I can see them) and additional I'm not able to get a SUM or AVERAGE on each column as I get the #N/A for those cells with that mark. Two examples of the formula I'm using a =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))) =IF(L9/L8-1=0,NA(),L9/L8-1) How may I get the SUM or the AVERAGE for that column and how may I print my file without seeing the #N/A's in black?
|
|
#5
April 13th, 2009, 09:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum or Average with #N/A cells
Thanks Valko. What does the "1E100" indicator means? Because that worked for
the SUM columns but I don´t know where does it come from in order to use it in any other spreadsheet I have the same problem. Could you pls answer me this question? Regarding the AVERAGE formula, why should I use the Array option and not just the AVERAGE formula alone? What the array make? And finally, I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. "T. Valko" wrote: To sum a range that contains errors: =SUMIF(A1:A10,"1E100") To average a range that contains errors: Array entered** =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. To not print errors: In Excel 2002: FilePage SetupSheet tab Under Print...Cell errors asselect blank OK -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... I'm using a formula to bring results from another worksheet but in some cases I get the #N/A result as I don´t have data on the original worksheet. To get the graphics on the final worksheet I use the formula with NA() not to give me 0 values on the chart and I also use the conditional formula to get the #N/A results just like the cells were empty (the same color I use for that cell) but when I print my page, I get the #N/A in black (so I can see them) and additional I'm not able to get a SUM or AVERAGE on each column as I get the #N/A for those cells with that mark. Two examples of the formula I'm using a =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))) =IF(L9/L8-1=0,NA(),L9/L8-1) How may I get the SUM or the AVERAGE for that column and how may I print my file without seeing the #N/A's in black?
|
|
#6
April 14th, 2009, 01:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum or Average with #N/A cells
Replied to your other post
-- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks Valko. What does the "1E100" indicator means? Because that worked for the SUM columns but I don´t know where does it come from in order to use it in any other spreadsheet I have the same problem. Could you pls answer me this question? Regarding the AVERAGE formula, why should I use the Array option and not just the AVERAGE formula alone? What the array make? And finally, I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. "T. Valko" wrote: To sum a range that contains errors: =SUMIF(A1:A10,"1E100") To average a range that contains errors: Array entered** =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. To not print errors: In Excel 2002: FilePage SetupSheet tab Under Print...Cell errors asselect blank OK -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... I'm using a formula to bring results from another worksheet but in some cases I get the #N/A result as I don´t have data on the original worksheet. To get the graphics on the final worksheet I use the formula with NA() not to give me 0 values on the chart and I also use the conditional formula to get the #N/A results just like the cells were empty (the same color I use for that cell) but when I print my page, I get the #N/A in black (so I can see them) and additional I'm not able to get a SUM or AVERAGE on each column as I get the #N/A for those cells with that mark. Two examples of the formula I'm using a =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O14),--('2009'!$B$8:$B$256$O15))) =IF(L9/L8-1=0,NA(),L9/L8-1) How may I get the SUM or the AVERAGE for that column and how may I my file without seeing the #N/A's in black?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
