Hi,
When I use SUM on a range of cells that may or may not be numeric, I don't get
a value error. Blank cells seem to be treated as zeros, and text cells are
ignored. I use xl2003. But perhaps I'm not understanding correctly. Could you
attach a sample workbook?
Regards - Dave
> Date: Sat, 31 Jul 2010 01:32:16 -0700
> Subject: $$Excel-Macros$$ Arithmetic with blank cells
> From: irvinep...@gmail.com
> To: excel-macros@googlegroups.com
>
> I have a bunch of cells that may or may not have numbers in them.
>
> Kind of like this: Sum(e35:e44) , but with no number in e44, I get the
> #Value error. What should be in a "empty" cell that would allow excel
> to treat it as zero but not have a bunch of zeroes all over the
> worksheet? Isnumber() works but I would have to have a test on each
> individual cell.
>
> I normally use things like: if(isnumber(e35),e35+e40,"") so the
> cell is blank until someone enters something numeric in the test
> cell. I have also used if(isnumber(e35),e35+e40,) but this puts a
> zero in the cell. Sometimes I have used Count in a range. Using
> 'Clear Contents" doesn't seem to work either, but putting the cursor
> in a cell and using delete seems to work OK, not sure why but I guess
> I could write a macro to clear the worksheet ready for entry by
> 'deleting' every entry.
>
> I did write a macro which clears the data entry area, but all formulas
> are not functioning without testing incoming cells:
>
> Range("F6:H6,D11:F12,D17:F18,D23:F24,D29:F30").Select ' the four
> pumps
> Selection.ClearContents
>
> Range("D35:G37").Select
> Selection.ClearContents
>
> etc
>
> This has plagued me for years and I know there must be a simple
> answer. In the past I made some kind of workaround for a specific
> spreadsheet and went on. I would really like to know what is the
> accepted way to fill a cell so it shows blank but does not cause a
> Value errror when used in a formula somewhere else.
>
>
> Anyhow, I would appreciate any insight from anyone.
>
> TIA, 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
