LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default 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?

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
zero value cells/blank cells causing error in AVERAGE? LilBeanie1033 Excel Worksheet Functions 5 March 19th 09 06:39 PM
How can I set some cells to not print w/o changing print layout? HMS Excel Discussion (Misc queries) 1 January 9th 09 04:44 PM
average cells, show 0 if nothing to average Kycajun Excel Discussion (Misc queries) 8 June 21st 06 07:36 PM
How can I print current row in same place on paper but not print surrounding cells??? Andy Ford New Users to Excel 1 September 2nd 05 05:50 AM
print view & print, in excel, different size cells buddyorliz Excel Discussion (Misc queries) 3 September 1st 05 01:20 PM


All times are GMT +1. The time now is 05:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"