How to stop 0% being a #DIV/0

RichardL4Y

New Member
Joined
Feb 22, 2019
Messages
3
So I'm just doing a simple formula of B1/A1 as a percentage. If B1 is 0 then the answer reverts to an error (#DIV/0). How do I get it to be BLANK if answer is zero?

Please help - thanks in advance
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
So I'm just doing a simple formula of B1/A1 as a percentage. If B1 is 0 then the answer reverts to an error (#DIV/0). How do I get it to be BLANK if answer is zero?

Please help - thanks in advance
You could always test for that condition...

=IF(A1=0,"",B1/A1)
 

RichardL4Y

New Member
Joined
Feb 22, 2019
Messages
3
You could always test for that condition...

=IF(A1=0,"",B1/A1)

Thanks guys - sorry my question wasn't quite right. It's a calculation for a Gross Profit %. As example formula is this =SUM(O6-S6)/O6 - how do I stop the DIV error if the answer is zero. I'd love it just to be Blank if Zero.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

Then

=iferror(SUM(O6-S6)/O6,"")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
Thanks guys - sorry my question wasn't quite right. It's a calculation for a Gross Profit %. As example formula is this =SUM(O6-S6)/O6 - how do I stop the DIV error if the answer is zero. I'd love it just to be Blank if Zero.

Your #DIV/0! error occurs because cell O6 is either 0 or blank, so you would do it the same way I showed in Message #3 ... test to see if O6 equals 0.

=IF(O6=0,"",SUM(O6-S6)/O6)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,472
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Can we please drop the SUM:

=IF(O6=0,"",(O6-S6)/O6)

=IFERROR((O6-S6)/O6,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

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