#1   Report Post  
Old May 18th 05, 11:32 AM
Penfold
 
Posts: n/a
Default formulas

I need to create a formula that says if the value is a minus or 0 show 0.00
if there is a positive value show the value.

  #2   Report Post  
Old May 18th 05, 11:35 AM
 
Posts: n/a
Default

Hi
You can do that with formatting or you could use something like:
=IF(yourformula<0,0,yourformula)

--
Andy.


"Penfold" wrote in message
...
I need to create a formula that says if the value is a minus or 0 show 0.00
if there is a positive value show the value.



  #3   Report Post  
Old May 18th 05, 11:58 AM
Bob Phillips
 
Posts: n/a
Default

=IF(yourformula<=0,"0.00",yourformula)


--
HTH

Bob Phillips

<Andy B wrote in message ...
Hi
You can do that with formatting or you could use something like:
=IF(yourformula<0,0,yourformula)

--
Andy.


"Penfold" wrote in message
...
I need to create a formula that says if the value is a minus or 0 show

0.00
if there is a positive value show the value.





  #4   Report Post  
Old May 18th 05, 12:04 PM
 
Posts: n/a
Default

Bob
I was working on the theory that the cell would be formatted as 0.00, and
therefore the formula wouldn't need to check for being zero itself!

--
Andy.


"Bob Phillips" wrote in message
...
=IF(yourformula<=0,"0.00",yourformula)


--
HTH

Bob Phillips

<Andy B wrote in message ...
Hi
You can do that with formatting or you could use something like:
=IF(yourformula<0,0,yourformula)

--
Andy.


"Penfold" wrote in message
...
I need to create a formula that says if the value is a minus or 0 show

0.00
if there is a positive value show the value.







  #5   Report Post  
Old May 18th 05, 12:11 PM
DonCam65
 
Posts: n/a
Default

Suggest that it would be better to consider (to cover result = 0)
=if(resultofyour formula0,resultofyourformula,0)
and before copying format to 2 decimal places.

If it was my spreadsheet consider using conditional format so that
If value < 0 then fontcolour = white
If value = 0 then fontvolour = white

This means that only results with positive values have any figures showing
--
Don C


"Andy B" wrote:

Hi
You can do that with formatting or you could use something like:
=IF(yourformula<0,0,yourformula)
--
Andy.


"Penfold" wrote in message
...
I need to create a formula that says if the value is a minus or 0 show 0.00
if there is a positive value show the value.






  #6   Report Post  
Old May 18th 05, 12:47 PM
Duke Carey
 
Posts: n/a
Default

And, just to toss in one more way:

=MAX(0,your formula)

"Penfold" wrote:

I need to create a formula that says if the value is a minus or 0 show 0.00
if there is a positive value show the value.

  #7   Report Post  
Old May 18th 05, 12:53 PM
 
Posts: n/a
Default

One caveat with using CF and font colour of white is that the value still
exists in the cell and would be summed along with the others.

--
Andy.


"DonCam65" wrote in message
...
Suggest that it would be better to consider (to cover result = 0)
=if(resultofyour formula0,resultofyourformula,0)
and before copying format to 2 decimal places.

If it was my spreadsheet consider using conditional format so that
If value < 0 then fontcolour = white
If value = 0 then fontvolour = white

This means that only results with positive values have any figures showing
--
Don C


"Andy B" wrote:

Hi
You can do that with formatting or you could use something like:
=IF(yourformula<0,0,yourformula)
--
Andy.


"Penfold" wrote in message
...
I need to create a formula that says if the value is a minus or 0 show
0.00
if there is a positive value show the value.






  #8   Report Post  
Old May 18th 05, 01:09 PM
Bob Phillips
 
Posts: n/a
Default

Hi Andy,

I feared he wanted different format for 0 and non-zero :-)

Regards

Bob

<Andy B wrote in message ...
Bob
I was working on the theory that the cell would be formatted as 0.00, and
therefore the formula wouldn't need to check for being zero itself!

--
Andy.



  #9   Report Post  
Old May 18th 05, 01:28 PM
bj
 
Posts: n/a
Default

one question is whether just the display needs to be 0.00 or does a cell
referencing the cell need to read 0.00 or the actual value?
Formating the cell does not change the value other cells would read as this
cells value.
the If or max equaitions would be a real zero value

"Penfold" wrote:

I need to create a formula that says if the value is a minus or 0 show 0.00
if there is a positive value show the value.

  #10   Report Post  
Old May 18th 05, 09:59 PM
redinBR
 
Posts: n/a
Default

With A1 representing the cell location, the formula is =IF(A10,A1,0)

"Penfold" wrote:

I need to create a formula that says if the value is a minus or 0 show 0.00
if there is a positive value show the value.



Reply
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
paste formulas between workbooks without workbook link ron Excel Discussion (Misc queries) 2 May 12th 05 05:10 PM
Help, Urgent Excel Formulas are not calculating maashoff Excel Discussion (Misc queries) 1 May 3rd 05 12:25 AM
Problem with named formula's nathan Excel Worksheet Functions 0 January 21st 05 04:07 PM
How to make Excel run limited number of formulas on a given worksh John Excel Discussion (Misc queries) 0 January 12th 05 04:29 PM
calculating formulas for all workbooks in a folder Chad Excel Worksheet Functions 3 November 13th 04 05:22 PM


All times are GMT +1. The time now is 08:31 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017