# Getting Rid of the #DIV/0! Cell Value

#### atvsource

##### Board Regular
I'm getting the dreaded #DIV/0! value. Here's the formula I'm using:

Code:
``=IF(\$B\$20>=E6,"Budget",ROUNDUP((E6-\$B\$20)/\$B\$21,0))``

I've tried to fix this, but I only seem to get the VALUE text to show up for all the things I've tried.

Anyone have a suggestion?

Here's what the different cells represent:
B20 = Investible Amount
E6 = Actual Price
B21 = Passive Income

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### c_m

##### Well-known Member
Try:
=IF(\$B\$21=0,"",IF(\$B\$20>=E6,"Budget",ROUNDUP((E6-\$B\$20)/\$B\$21,0)))

#### VoG

##### Legend
Try

=IF(\$B\$21="","",IF(\$B\$20>=E6,"Budget",ROUNDUP((E6-\$B\$20)/\$B\$21,0)))

#### atvsource

##### Board Regular
That didn't seem to work.. the #DIV/0! is still returning.

#### c_m

##### Well-known Member
do you have div!0 error in B20 or B21?

#### atvsource

##### Board Regular
Nope, however, B21 has a value of \$0.00 sometimes so would that \$0.00 in B21 cause teh #DIV/0! issue?

#### c_m

##### Well-known Member
yes B21 will cause this issue...did you try the suggested formula:
=IF(\$B\$21=0,"",IF(\$B\$20>=E6,"Budget",ROUNDUP((E6-\$B\$20)/\$B\$21,0)))

#### atvsource

##### Board Regular
yes B21 will cause this issue...did you try the suggested formula:
=IF(\$B\$21=0,"",IF(\$B\$20>=E6,"Budget",ROUNDUP((E6-\$B\$20)/\$B\$21,0)))

Tried this formula, but now when B20 is greater than E6, Budget does not show up, but instead a blank cell. So something else is also wrong with the formula.

#### atvsource

##### Board Regular
Tried this formula, but now when B20 is greater than E6, Budget does not show up, but instead a blank cell. So something else is also wrong with the formula.

After some fig'n with the formula, I was able to get it to work.. there was another issue with another formula that this also had to work with. After fixing the other formula, it all works great now.

Thanks for all the help!! Saved me a lot of time!

Replies
3
Views
272
Replies
3
Views
174
Replies
8
Views
521
Replies
3
Views
838
Replies
7
Views
343

1,195,673
Messages
6,011,087
Members
441,582
Latest member
Topkapi

### 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.

### Which adblocker are you using?

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

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