Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm
just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
I use Excel 2000
Ok, I don't know if that setting is available in Excel 2000. Seems like a fairly basic option that should be available with every version of Excel. =SUMIF(A1:A10,"<1E100") what does "<1E100" means 1E100 is scientific notation for a very large number. 1 followed by 100 zeros. Chances are pretty good that the numbers you're dealing with are less than 1E100 so the formula sums all numeric values that are less than 1E100 and ignores any errors. =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula You don't have to but don't be "affraid" to. I like array formulas. They can be very powerful and simplify a complex task. You can also use this non-array formula: =SUMIF(A1:A10,"<1E100")/COUNT(A1:A10) -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Hi,
1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Thanks a lot for your answers. I understood all of them.
Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Thanks for your answer. I already understood the issues and really appreciate
your explanation. I still have a question when I print and get the #N/A cells on my printed sheet. Pls, could you look at the question I just posted to Ashish Mathur? "T. Valko" wrote: I use Excel 2000 Ok, I don't know if that setting is available in Excel 2000. Seems like a fairly basic option that should be available with every version of Excel. =SUMIF(A1:A10,"<1E100") what does "<1E100" means 1E100 is scientific notation for a very large number. 1 followed by 100 zeros. Chances are pretty good that the numbers you're dealing with are less than 1E100 so the formula sums all numeric values that are less than 1E100 and ignores any errors. =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula You don't have to but don't be "affraid" to. I like array formulas. They can be very powerful and simplify a complex task. You can also use this non-array formula: =SUMIF(A1:A10,"<1E100")/COUNT(A1:A10) -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
I can't believe there isn't a print option that let's you choose how to
handle errors. I don't use Excel 2000 as you do so the menu path to that option in Excel 2000 may be different than what I posted. Look around for it. If that option is not available... Have you tried using conditional formatting to set the text color of those cells that contain #N/A to be the same as the background color of those cells? For example, the background cell color is white, set the font to also be white. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for your answers. I understood all of them. Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Incredible but true....
Unfortunately I'm not able to paste the image I get but it is something like this: Page SetUp Page - Margins - Header / Footer - Sheet On Sheet you have Print Area Print Titles Gridlines Black and White Draft Quality Row and Comumn headings Comments Page Order..... As you see, I'm not able to click on any Error function under the Print option to disable it. I have also looked for it into the Tools / Options menu and the Printer menu but I don´t have it. Talking about the other option, I did it yesterday, I changed the color for the #N/A cells into gray (as my cell) but THEY APPEAR in black, not just in gray but in black. The only way they function is if I turn the error into white and I leave the cell in white but honestly for some cases I need to use colors in order to separate the columns and to make it easier to look for when my boss is watching them. That's why I wanted the gray columns to use a special formula. "T. Valko" wrote: I can't believe there isn't a print option that let's you choose how to handle errors. I don't use Excel 2000 as you do so the menu path to that option in Excel 2000 may be different than what I posted. Look around for it. If that option is not available... Have you tried using conditional formatting to set the text color of those cells that contain #N/A to be the same as the background color of those cells? For example, the background cell color is white, set the font to also be white. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for your answers. I understood all of them. Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Well, I'm out of ideas.
Take a look around this website: http://peltiertech.com/ It's supported by Excel MVP Jon Peltier, a charting expert. He may have something on there that will work. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Incredible but true.... Unfortunately I'm not able to paste the image I get but it is something like this: Page SetUp Page - Margins - Header / Footer - Sheet On Sheet you have Print Area Print Titles Gridlines Black and White Draft Quality Row and Comumn headings Comments Page Order..... As you see, I'm not able to click on any Error function under the Print option to disable it. I have also looked for it into the Tools / Options menu and the Printer menu but I don´t have it. Talking about the other option, I did it yesterday, I changed the color for the #N/A cells into gray (as my cell) but THEY APPEAR in black, not just in gray but in black. The only way they function is if I turn the error into white and I leave the cell in white but honestly for some cases I need to use colors in order to separate the columns and to make it easier to look for when my boss is watching them. That's why I wanted the gray columns to use a special formula. "T. Valko" wrote: I can't believe there isn't a print option that let's you choose how to handle errors. I don't use Excel 2000 as you do so the menu path to that option in Excel 2000 may be different than what I posted. Look around for it. If that option is not available... Have you tried using conditional formatting to set the text color of those cells that contain #N/A to be the same as the background color of those cells? For example, the background cell color is white, set the font to also be white. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for your answers. I understood all of them. Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Thanks a lot for the other answers and for trying to solve my last problem.
I'll reach Jon hoping he has a solution for this issue. REGARDS!!!!!! "T. Valko" wrote: Well, I'm out of ideas. Take a look around this website: http://peltiertech.com/ It's supported by Excel MVP Jon Peltier, a charting expert. He may have something on there that will work. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Incredible but true.... Unfortunately I'm not able to paste the image I get but it is something like this: Page SetUp Page - Margins - Header / Footer - Sheet On Sheet you have Print Area Print Titles Gridlines Black and White Draft Quality Row and Comumn headings Comments Page Order..... As you see, I'm not able to click on any Error function under the Print option to disable it. I have also looked for it into the Tools / Options menu and the Printer menu but I don´t have it. Talking about the other option, I did it yesterday, I changed the color for the #N/A cells into gray (as my cell) but THEY APPEAR in black, not just in gray but in black. The only way they function is if I turn the error into white and I leave the cell in white but honestly for some cases I need to use colors in order to separate the columns and to make it easier to look for when my boss is watching them. That's why I wanted the gray columns to use a special formula. "T. Valko" wrote: I can't believe there isn't a print option that let's you choose how to handle errors. I don't use Excel 2000 as you do so the menu path to that option in Excel 2000 may be different than what I posted. Look around for it. If that option is not available... Have you tried using conditional formatting to set the text color of those cells that contain #N/A to be the same as the background color of those cells? For example, the background cell color is white, set the font to also be white. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for your answers. I understood all of them. Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
He may have a technique for not having to use #N/A that won't mess up the
chart. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for the other answers and for trying to solve my last problem. I'll reach Jon hoping he has a solution for this issue. REGARDS!!!!!! "T. Valko" wrote: Well, I'm out of ideas. Take a look around this website: http://peltiertech.com/ It's supported by Excel MVP Jon Peltier, a charting expert. He may have something on there that will work. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Incredible but true.... Unfortunately I'm not able to paste the image I get but it is something like this: Page SetUp Page - Margins - Header / Footer - Sheet On Sheet you have Print Area Print Titles Gridlines Black and White Draft Quality Row and Comumn headings Comments Page Order..... As you see, I'm not able to click on any Error function under the Print option to disable it. I have also looked for it into the Tools / Options menu and the Printer menu but I don´t have it. Talking about the other option, I did it yesterday, I changed the color for the #N/A cells into gray (as my cell) but THEY APPEAR in black, not just in gray but in black. The only way they function is if I turn the error into white and I leave the cell in white but honestly for some cases I need to use colors in order to separate the columns and to make it easier to look for when my boss is watching them. That's why I wanted the gray columns to use a special formula. "T. Valko" wrote: I can't believe there isn't a print option that let's you choose how to handle errors. I don't use Excel 2000 as you do so the menu path to that option in Excel 2000 may be different than what I posted. Look around for it. If that option is not available... Have you tried using conditional formatting to set the text color of those cells that contain #N/A to be the same as the background color of those cells? For example, the background cell color is white, set the font to also be white. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for your answers. I understood all of them. Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
Biff:
I loaded the page you refered to me and found that maybe I will need to pay for any special info, but for the moment I just solved my problem adding a column and just entering a formula like: =IF(ISNA(L10),"",L10), so I hid the column containing the #N/A's values but left it for the graphic and took the new column just to view the results I got in the previous column and not to graphic it. If you, in the future, find anyway to make this issue easier, I will be grateful to receive any help about it. THANKS A LOT!!!!!! "T. Valko" wrote: He may have a technique for not having to use #N/A that won't mess up the chart. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for the other answers and for trying to solve my last problem. I'll reach Jon hoping he has a solution for this issue. REGARDS!!!!!! "T. Valko" wrote: Well, I'm out of ideas. Take a look around this website: http://peltiertech.com/ It's supported by Excel MVP Jon Peltier, a charting expert. He may have something on there that will work. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Incredible but true.... Unfortunately I'm not able to paste the image I get but it is something like this: Page SetUp Page - Margins - Header / Footer - Sheet On Sheet you have Print Area Print Titles Gridlines Black and White Draft Quality Row and Comumn headings Comments Page Order..... As you see, I'm not able to click on any Error function under the Print option to disable it. I have also looked for it into the Tools / Options menu and the Printer menu but I don´t have it. Talking about the other option, I did it yesterday, I changed the color for the #N/A cells into gray (as my cell) but THEY APPEAR in black, not just in gray but in black. The only way they function is if I turn the error into white and I leave the cell in white but honestly for some cases I need to use colors in order to separate the columns and to make it easier to look for when my boss is watching them. That's why I wanted the gray columns to use a special formula. "T. Valko" wrote: I can't believe there isn't a print option that let's you choose how to handle errors. I don't use Excel 2000 as you do so the menu path to that option in Excel 2000 may be different than what I posted. Look around for it. If that option is not available... Have you tried using conditional formatting to set the text color of those cells that contain #N/A to be the same as the background color of those cells? For example, the background cell color is white, set the font to also be white. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for your answers. I understood all of them. Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, AVERAGE and PRINT with #N/A cells
I was going to suggest doing something like that but that's like a last
resort. Sometimes you have to do whatever it takes even if that means it's not the "perfect" solution. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Biff: I loaded the page you refered to me and found that maybe I will need to pay for any special info, but for the moment I just solved my problem adding a column and just entering a formula like: =IF(ISNA(L10),"",L10), so I hid the column containing the #N/A's values but left it for the graphic and took the new column just to view the results I got in the previous column and not to graphic it. If you, in the future, find anyway to make this issue easier, I will be grateful to receive any help about it. THANKS A LOT!!!!!! "T. Valko" wrote: He may have a technique for not having to use #N/A that won't mess up the chart. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for the other answers and for trying to solve my last problem. I'll reach Jon hoping he has a solution for this issue. REGARDS!!!!!! "T. Valko" wrote: Well, I'm out of ideas. Take a look around this website: http://peltiertech.com/ It's supported by Excel MVP Jon Peltier, a charting expert. He may have something on there that will work. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Incredible but true.... Unfortunately I'm not able to paste the image I get but it is something like this: Page SetUp Page - Margins - Header / Footer - Sheet On Sheet you have Print Area Print Titles Gridlines Black and White Draft Quality Row and Comumn headings Comments Page Order..... As you see, I'm not able to click on any Error function under the option to disable it. I have also looked for it into the Tools / Options menu and the Printer menu but I don´t have it. Talking about the other option, I did it yesterday, I changed the color for the #N/A cells into gray (as my cell) but THEY APPEAR in black, not just in gray but in black. The only way they function is if I turn the error into white and I leave the cell in white but honestly for some cases I need to use colors in order to separate the columns and to make it easier to look for when my boss is watching them. That's why I wanted the gray columns to use a special formula. "T. Valko" wrote: I can't believe there isn't a print option that let's you choose how to handle errors. I don't use Excel 2000 as you do so the menu path to that option in Excel 2000 may be different than what I posted. Look around for it. If that option is not available... Have you tried using conditional formatting to set the text color of those cells that contain #N/A to be the same as the background color of those cells? For example, the background cell color is white, set the font to also be white. -- Biff Microsoft Excel MVP "PaulinaDi" wrote in message ... Thanks a lot for your answers. I understood all of them. Regarding my last question this is the issue: I'm trying to use a formula with the NA() option in order to get my chart just with those rows that have any value. For example: A L M Month $ % 6 Jan 38.0843 15.88% 7 Feb 37.8526 -0.61% 8 Mar 41.6886 10.13% 9 Abr 45.5067 9.16% 10 May 11 Jun 12 Jul 13 Aug To get the 15.88%, -0.61%, 10.13% and so on I used a formula like this: =IF(ISNA(L7/L6-1),NA(),L7/L6-1) where L7 and L6 contains a formula that brings results from another worksheet once I get some data for that month. So in row 10, 11, 12 and 13 I get an #N/A result. The formula to get the "L" column results is: =IF(SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))=0,NA(),SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16),('2009'!E$8:E$256))/SUMPRODUCT(--('2009'!$B$8:$B$256=$O15),--('2009'!$B$8:$B$256<$O16))) So what I really want is to get in my chart just the values I have and not the ones I still don´t have like May, Jun, Jul, Aug and that's why I use the NA() option but when I print my file, the NA() APPEARS in the printed sheet when I use any color for my column different from blank. In column M I decided to use a gray color so I used the conditional formatting to get those #N/A in gray but still appear in black when printed. What am I doing wrong? ___________________________________ "Ashish Mathur" wrote: Hi, 1. Difficult to guess why 2. "1E100" - This is a very large number I.e. 1 raised to the power of 100. So in effect we are saying that sum all numbers which are less than or equal to 1E100. Since an error is not a number it is ignored. You may also specify the criteria as "<=largest numbers I your range" 3. Average(IF( - b'coz there is no averageif() function. If you do not wish to use the array formula, you can use the SUM function in the numerator and count function in the denominator =SUMIF(A1:A10,"<1E100")/countif(A1:A10,"<1E100) 4. Question not clear -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "PaulinaDi" wrote in message ... Thanks Sheeloo and T. Valko but I still have questions about this issue. I'm just copying my new questions on this new post to make it easier or you may refer to my first post where I wrote them originally. The original issue is after these questions. I use Excel 2000 so I don´t have the FilePage SetupSheet tab Under Print...Cell errors asselect blank OK option available. The =SUMIF(A1:A10,"<1E100") formula helped me but would like to know what does "<1E100" means. About the AVERAGE formula =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) I would like to know why should I use an array formula (in fact it worked but just to know why) And about the =IF(ISNA(your_formula),"",your_formula) formula, maybe I get a blank cell to SUM or to AVERAGE but I get a mark on my chart just on that month I'm using it and if I substitute the "" for NA() I get the #N/A mark on my printed files. ____________________________________________ 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
zero value cells/blank cells causing error in AVERAGE? | Excel Worksheet Functions | |||
How can I set some cells to not print w/o changing print layout? | Excel Discussion (Misc queries) | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
How can I print current row in same place on paper but not print surrounding cells??? | New Users to Excel | |||
print view & print, in excel, different size cells | Excel Discussion (Misc queries) |