Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cell to show ' 0 ' , or nothing at all, if the sum is a negative number
I'm making a worksheet for my company. I want to subtract one number number from another number. I use the 'sum' format for this '=sum(BL4-BP4) '. This format works fine for me in most cases, if BL4 BP4. However, if BL4<BP4, the sum that is shown is a negative number. I would like for the worksheet to show only positive numbers. I would like for the cell to show ' 0 ' , or nothing at all, if the sum is a negative number. For instance, 10-8=12 is ok, but 8-10=-2 is not. How do I do this? Thanks -- rdmdale ------------------------------------------------------------------------ rdmdale's Profile: http://www.excelforum.com/member.php...o&userid=27339 View this thread: http://www.excelforum.com/showthread...hreadid=468481 |
#2
|
|||
|
|||
Hi rdmdale,
Pls try this =IF(B2A2,B2-A2,A2-B2) Anirudh "rdmdale" wrote: I'm making a worksheet for my company. I want to subtract one number number from another number. I use the 'sum' format for this '=sum(BL4-BP4) '. This format works fine for me in most cases, if BL4 BP4. However, if BL4<BP4, the sum that is shown is a negative number. I would like for the worksheet to show only positive numbers. I would like for the cell to show ' 0 ' , or nothing at all, if the sum is a negative number. For instance, 10-8=12 is ok, but 8-10=-2 is not. How do I do this? Thanks -- rdmdale ------------------------------------------------------------------------ rdmdale's Profile: http://www.excelforum.com/member.php...o&userid=27339 View this thread: http://www.excelforum.com/showthread...hreadid=468481 |
#3
|
|||
|
|||
for '0' pls try this
=IF(B2A2,B2-A2,0) "Anirudh" wrote: Hi rdmdale, Pls try this =IF(B2A2,B2-A2,A2-B2) Anirudh "rdmdale" wrote: I'm making a worksheet for my company. I want to subtract one number number from another number. I use the 'sum' format for this '=sum(BL4-BP4) '. This format works fine for me in most cases, if BL4 BP4. However, if BL4<BP4, the sum that is shown is a negative number. I would like for the worksheet to show only positive numbers. I would like for the cell to show ' 0 ' , or nothing at all, if the sum is a negative number. For instance, 10-8=12 is ok, but 8-10=-2 is not. How do I do this? Thanks -- rdmdale ------------------------------------------------------------------------ rdmdale's Profile: http://www.excelforum.com/member.php...o&userid=27339 View this thread: http://www.excelforum.com/showthread...hreadid=468481 |
#4
|
|||
|
|||
Do you want the result to actually be zero or nothing if its negative or
want the figure there for calculations but dot to display it in the cell? To make the result zero, =IF(BL4-BL9=0,BL4-BL9,0) or for nothing, =IF(BL4-BL9=0,BL4-BL9,"") To have the result there but not displayed, Custom Format the cell(s) as 0.00_ ; Note that there's a space between the underscore and the semicolon, Regards, Alan "rdmdale" wrote in message ... I'm making a worksheet for my company. I want to subtract one number number from another number. I use the 'sum' format for this '=sum(BL4-BP4) '. This format works fine for me in most cases, if BL4 BP4. However, if BL4<BP4, the sum that is shown is a negative number. I would like for the worksheet to show only positive numbers. I would like for the cell to show ' 0 ' , or nothing at all, if the sum is a negative number. For instance, 10-8=12 is ok, but 8-10=-2 is not. How do I do this? Thanks -- rdmdale ------------------------------------------------------------------------ rdmdale's Profile: http://www.excelforum.com/member.php...o&userid=27339 View this thread: http://www.excelforum.com/showthread...hreadid=468481 |
#5
|
|||
|
|||
First, =sum(BL4-BP4) doesn't make much sense. It needs only to be =BL4-BP4 Next, to return a zero instead of negative, do this: =IF(BL4<BP4,0,BL4-BP4) OR to return a "nothing at all" instead of negative, do this: =IF(BL4<BP4,"",BL4-BP4) (use 2 double quotations in place of the zero) -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=468481 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
How to show a leading 0 in a cell. | Excel Discussion (Misc queries) | |||
Problem with IF condition or vector lookup? | Excel Worksheet Functions | |||
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? | Excel Worksheet Functions | |||
cell reference show cell name (ie. D45) and not cell value | Excel Worksheet Functions |