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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,284
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top