Blank cells used in formula giving #value error

PFL

New Member
Joined
Jun 4, 2012
Messages
6
I am using a simple formula of =b1-b2 which works fine except where b2 in blank and I get a #value! error. I want to allow blanks in my worksheet. I'd be grateful for advice on how to treat blank cells.

In effect I want to treat a blank cell as zero


Thanks in advance PFL
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hallo and welcome.
How did you get the value error?
Are you cells return from formula?

Excel Workbook
ABC
130
Sheet1
 
Upvote 0
Hi PFL

It might have your cell b2 contains text or text formatting thats why you are getting #value error.

you can check with istext function.
 
Upvote 0
=IF(B2="",B1,B1-B2)

That formula should work - although Im not understanding why you are getting a #value error...

Regards
Patrick
 
Upvote 0
Barry,
Thank you very much. The =N(G11)-N(G10) worked perfectly.

Thanks to all who replied.

PFL
 
Upvote 0

Forum statistics

Threads
1,203,082
Messages
6,053,418
Members
444,662
Latest member
AaronPMH

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
Back
Top