#DIV/0!

M

meppwc

Well-known Member
Joined
May 16, 2003
Messages
623
Office Version
  1. 365
Platform
  1. Windows
I have a formula included that I use to get a % increase/decrease on stock value performance
=IF(B7>B6,(((B7-B6)/ABS(B6))*100,
IF(B7>B6,(((B7-B6)/ABS(B6))*100),IF(B7< B6,(((B6-B7)/ABS(B7))*100),0))

Not sure if this is the same thing that others would use but it does work

Here is my problem......I get the error #DIV/0! when cell B7 is blank. And yes, the error I am getting is legitimate.

I understand a workaround for this error would be to add the following string to my existing formula ,IF(B7,B6/B7," ") so that you do not see the DIV error when cell B7 is blank.

My problem is making it fit into my equation.....I was trying to add it in place of the last "0" at the end of the formula, but I cannot get it to work
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Try conditional formatting if you just want to hide it.

If formula: =iserror (I think)
then format the background color to white.

DaK
 
Upvote 0
meppwc said:
I have a formula included that I use to get a % increase/decrease on stock value performance
=IF(B7>B6,(((B7-B6)/ABS(B6))*100,
IF(B7< B6,(((B6-B7)/ABS(B7))*100),0))

Not sure if this is the same thing that others would use but it does work

Here is my problem......I get the error #DIV/0! when cell B7 is blank. And yes, the error I am getting is legitimate.

I understand a workaround for this error would be to add the following string to my existing formula ,IF(B7,B6/B7," ") so that you do not see the DIV error when cell B7 is blank.

My problem is making it fit into my equation.....I was trying to add it in place of the last "0" at the end of the formula, but I cannot get it to work
Click to expand...

How about this ?

=IF(B7>B6,IF(B6,(B7-B6)/ABS(B6)*100,""), IF(B7,(B6-B7)/ABS(B7)*100,""))
 
Upvote 0
Not quite sure what your formula is or does; it seems to be truncated, but whatever it is, if you want to avoid the #DIV/0! error, test for either a zero or a blank in your divisor (cell B6) by tacking on the IF/OR segment to the front of your formula.
Book1
ABCD
4
5100
650
7100
8
Sheet1
 
Upvote 0
Juan Pablo, you have certainly provided meppwc with a very improved formula that should cure his problem! Maybe, by studying your proposal, he will learn a few things, too.

Barry, I like your no-nonsense, direct solution. It doesn't upset the user in having to use something new, instead of the present formula that "works for him"! Very good!
 
Upvote 0
Thanks for the kudos, Ralph. And now that the OP has edited his original formula and I can see it, and if he wants to keep it, instead of Juan Pablo's, here it is in blue. Couldn't resist deleting the redundant second argument and some of the unneccessary brackets.
Book1
ABCD
1
2100
3
4
5100
650
7100
8
9
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
VBA sum pairs of cells from dynamic input list
Replies
3
Views
121
myall_blues
myall_blues
S
  • Locked
  • Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
215
Fluff
Fluff
S
  • Solved
A Different way to Calculate Ratios using GDC & or MIN ?
2
Replies
12
Views
491
StrawberryDreams
S
A
  • Solved
Xlookup with Multiple Criteria
Replies
5
Views
812
acool
A
S
  • Solved
Trying to add an "IF, THEN" to an existing formula
Replies
7
Views
468
sacajamo
S

Forum statistics

Threads
1,221,701
Messages
6,161,381
Members
451,700
Latest member
Eccymarge

Share this page

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