A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to have auto calc cells be blank unless entry made



 
 
Thread Tools Display Modes
  #1  
Old July 13th 08, 09:03 PM posted to microsoft.public.excel.worksheet.functions
miss sass
external usenet poster
 
Posts: 1
Default how to have auto calc cells be blank unless entry made

I am creating a travel report that will be filled out on excel and also by
hand. I want cells to auto calculate for those who complete in excel, but
need the cells to not show anything (such as $0.00) when nothing is entered
so those completing by hand can still use those same cells. Right now I have
a date range and # of miles and reimbursement amount cells that auto
calculate. When I leave the cells that provide the auto number blank, I
still get something - such as the $0.00.
Ads
  #2  
Old July 13th 08, 09:12 PM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 1,533
Default how to have auto calc cells be blank unless entry made

Hi

A formula will aways return a zero if the calculation equals 0.

To avoid this you need to test if your formula returns zero, and of so leave
the cell empty.

Look at this example:

=IF(SUM(A1:A6)=0,"",SUM(A1:A6))

Hopes it helps

Regards,
Per

"miss sass" <miss > skrev i meddelelsen
news
>I am creating a travel report that will be filled out on excel and also by
> hand. I want cells to auto calculate for those who complete in excel, but
> need the cells to not show anything (such as $0.00) when nothing is
> entered
> so those completing by hand can still use those same cells. Right now I
> have
> a date range and # of miles and reimbursement amount cells that auto
> calculate. When I leave the cells that provide the auto number blank, I
> still get something - such as the $0.00.


  #3  
Old July 13th 08, 09:41 PM posted to microsoft.public.excel.worksheet.functions
daddylonglegs
external usenet poster
 
Posts: 287
Default how to have auto calc cells be blank unless entry made

You can also test if a specific cell is empty....and if it is return a blank,
e.g.

=IF(A1="","",A1*B1)

This formula gives you the result of the calculation A1*B1....but if A1 is
blank the formula will just return a blank

"Per Jessen" wrote:

> Hi
>
> A formula will aways return a zero if the calculation equals 0.
>
> To avoid this you need to test if your formula returns zero, and of so leave
> the cell empty.
>
> Look at this example:
>
> =IF(SUM(A1:A6)=0,"",SUM(A1:A6))
>
> Hopes it helps
>
> Regards,
> Per
>
> "miss sass" <miss > skrev i meddelelsen
> news
> >I am creating a travel report that will be filled out on excel and also by
> > hand. I want cells to auto calculate for those who complete in excel, but
> > need the cells to not show anything (such as $0.00) when nothing is
> > entered
> > so those completing by hand can still use those same cells. Right now I
> > have
> > a date range and # of miles and reimbursement amount cells that auto
> > calculate. When I leave the cells that provide the auto number blank, I
> > still get something - such as the $0.00.

>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
put date and time in cells after entry made in shared workbook robzrob Excel Worksheet Functions 2 April 10th 08 09:13 PM
auto calc on, but have to edit (f2) cells to force re-calc..help! Curt Excel Worksheet Functions 3 February 13th 06 07:05 PM
After a date entry is made, only displaying the Day Alec H Excel Discussion (Misc queries) 3 February 9th 06 02:10 PM
Count blank cells since last entry Luke Excel Worksheet Functions 2 August 12th 05 08:03 PM
Want a number of cells to be auto completed based on entry in one. matt ashley Excel Worksheet Functions 1 April 14th 05 10:07 PM


All times are GMT +1. The time now is 01:08 PM.


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