% of difference between two numbers

  • Thread starter Thread starter exeldumb
  • Start date Start date
E

exeldumb

I have read all the posts on how to get a percentage of difference betwee two
numbers. I've used the help on the spreadsheet and all I get is 00.0. Across
line 5, starting with column B have the daily closing of the stock market.
On row 7 below the second day I clicked on the cell and typed in the formula
=(C5-B5)/ABS(B5) just like it shows on the help display. It always comes up
zero. I tried the ones in the answers to the questions. Same-o, same-o. I
just want to see the daily positive and negative up and downs.
 
Probably just a formatting issue. Select the cell with the formula and:

Format > Cells... > Number > Percentage
 
Probably just a formatting issue.  Select the cell with the formula and:

Format > Cells... > Number > Percentage
Click to expand...

Hi,

I believe percent difference is different to percent change. Assuming
C5 is your new value, and B5 is the old value, percent change should
just be =(C5-B5)/C5 like you have. Not sure why you are using the
absolute value of B5, but since I have never seen a stock price below
zero, I guess it doesn't matter... if someone sees one, I'll buy 'em
all! :)

On the other hand, percent difference would be =ABS(C5-B5)/
AVERAGE(C5,B5). This will give the difference between the two numbers
expressed as a percentage of the average and is representative of how
similar the values are.

In summary, there shouldn't be anything wrong with your formula...
just check your source cells (make sure B5 doesn't equal zero) and the
formatting as Gary"s Student suggests.

Cheers,
Ivan.
 
Thanks for ur support


Hi,

I believe percent difference is different to percent change. Assuming
C5 is your new value, and B5 is the old value, percent change should
just be =(C5-B5)/C5 like you have. Not sure why you are using the
absolute value of B5, but since I have never seen a stock price below
zero, I guess it doesn't matter... if someone sees one, I'll buy 'em
all! :)

On the other hand, percent difference would be =ABS(C5-B5)/
AVERAGE(C5,B5). This will give the difference between the two numbers
expressed as a percentage of the average and is representative of how
similar the values are.

In summary, there shouldn't be anything wrong with your formula...
just check your source cells (make sure B5 doesn't equal zero) and the
formatting as Gary"s Student suggests.

Cheers,
Ivan.
Click to expand...
 
Thanks for ur support
Click to expand...
[.... elided ....]

I'm sure "ivyleaf" has been with bated breath for 4+ years just hear you say
that.

How dumb!

If you have a question, post a new thread/discussion. Don't piggyback an
old discussion, particularly one that is more than a few months old.

If you are simply trying to say that provided an answer that you need,
believe me: nobody cares!

BTW, Ivan's comments about %change v. %difference are incorrect, as is his
formula for %difference, IMHO.
 
I definitely agree about replying to old posts - not necessary.
But, since he did, and it is coming up in Google search results...
AND since I agree is solution was a non-solution....
It would have been helpful if you pwned it proper and GAVE THE CORRECT SOLUTION!
Since now peeps like me get here and say, "wow, he chastised that dude for being polite, its not like it was his (or your) original question.... He just was being polite ans saying thanks (which is most forums is a GOOD thing) your info was helpful to me, and I appreciate you helping... (although I find it a wonder he did).

Anyway, all of this was not for nothing, and I am not trying to be a jerk or troll for a fight.. I sincerely need help!

So if you know how to do (as a formula, I can do it in VBA)
I am looking to show the difference (as a percentage) between two other numbers that is accurate in increases as well as decreases.

Actually its specifically the percentage CHANGE between two other numeric values.

Perfect example: SALES TAX
if you have 5% sales tax, to spend exactly $100 you have to buy something for $95
(ie. 1.00 - 5% = .95) simple right?
but if you take $95 and ADD 5% you only get $99.75

so that is what I need, something that works in either case.

TIA

p.s. if you really dont have anything constructive to add, then please don't was YOUR time responding.
 
I am looking to show the difference (as a percentage)between
two other numbers that is accurate in increases as well as
decreases. Actually its specifically the percentage CHANGE
between two other numeric values. Perfect example: SALES TAX
if you have 5% sales tax, to spend exactly $100 you have to
buy something for $95 (ie. 1.00 - 5% = .95) simple right?
but if you take $95 and ADD 5% you only get $99.75
so that is what I need, something that works in either case.
Click to expand...

As I wrote to "sathish...", if you have a new question, start a new thread.
In fact, your question is unrelated to "% of difference" as it is meant by
the "exeldumb" [sic], the OP.

But in the interest of time, I'll answer your question.

Your algebra is incorrect. If the total must not exceed $100 and 5% is
added to the sales price, the most that you can purchase is 100 = x*(1+5%).
So x = 100/(1+5%) = 95.24.

In Excel: =ROUND(A1/(1+A2),2)

where A1 is the total, and A2 is the percentage added (sales tax) in the
form 5% or 0.05.

Double-check: 95.24 + 95.24*5% = 95.24 + 4.76 = 100.00.

Note: With these numbers, ROUND does work. I would choose ROUNDDOWN to
avoid going over 100 by 1 cent due to rounding. However, I cannot think of
an example quickly that demonstrates the problem. Caveat lector!
 
Back
Top