Hi,
Try changing the formula in M3 to:
IF(COUNT(M1,M2)>0,SUM(M1:M2),"")
I think M1+M2 is the problem. The '+' operand can't seem to handle non-numeric
inputs, whereas the SUM function has a built-in ability to ignore them.
Regards - Dave.
Date: Sat, 31 Jul 2010 22:42:34 -0700
Subject: Re: $$Excel-Macros$$ Arithmetic with blank cells
From: irvinep...@gmail.com
To: excel-macros@googlegroups.com
OK, here is an example: The whole idea is to prevent a data entry spreadsheet
having a bunch of zeroes in all the totalizing columns. Cells K1,L1 and K2,L2
have been "Clear Contents", Cell M3 the totalizer cell is blank. Put a 1 in K1
and the #Value error appears in M3
Cell M1: =IF(ISNUMBER(K1),K1*L1,"")
Cell M2: =IF(ISNUMBER(K2),K2*L2,"")
Cell M3 =IF(COUNT(M1,M2)>0,M1+M2,"")
Type(ing) all the cells shows all numeric except M2(2) and M3(16)
Clearly it is the "" in M2 that is causing the value error and excel is ok with
multiplying the blank value in L1 to the 1 in K1 even though the result is
blank.
This is really what I am trying to accomplish, make the blank worksheet look
blank where arithmetic is taking place. Thanks to a post, I found a new option
in Excel Options-Advanced-Display Options for This Worksheet-Show a zero in
cells that have a zero value
I unchecked that guy and at least the spreadsheet does not show the zeroes but
of course there are times when zero is a legitimate value to display.
Thanks for all the interest and the answers.
zp18
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
