Combining IF and ROUND

PHnew

New Member
Joined
Oct 28, 2010
Messages
22
I am comparing two sets of data to arrive at a variance for further review. In the formula below "C4" actually holds numbers with several decimal points, but displays only 2


I am wanting the result of the formula below to show a dash if the result is zero, and show the actual number, rounded to 2, if other than zero.


Currently, because the number in C4 has a string of decimals, the true number is not zero; but for my purposes it is. But the result I displaying (0.00) rather than "-"


=IF(C4-H4=0,"-",C4-H4)


How do I add the Round function to this equation?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,813
Office Version
  1. 2010
Platform
  1. Windows
I am comparing two sets of data to arrive at a variance for further review. In the formula below "C4" actually holds numbers with several decimal points, but displays only 2
I am wanting the result of the formula below to show a dash if the result is zero, and show the actual number, rounded to 2, if other than zero.
Currently, because the number in C4 has a string of decimals, the true number is not zero; but for my purposes it is. But the result I displaying (0.00) rather than "-"
=IF(C4-H4=0,"-",C4-H4)

You might want:

=IF(ROUND(C4-H4,2)=0,"-",ROUND(C4-H4,2))

But you might consider simply:

=ROUND(C4-H4,2)

formatted as Accounting (with None for Symbol) or a similar Custom format which displays "-" while the actual value is numeric zero. For example, Custom 0.00;(0.00);"-" .

If the result is the string "-", that might cause errors in some dependent formulas or complicate their design.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,430
Members
409,876
Latest member
Akash Yadav
Top