abs formula

[Pammy]

I have 4 columns of numbers and I am trying to get an ABS value. these are
based on years:
2005 2006 2007 2008

71 169 165 214
How do you do a ABS formula

The person who did this last year based it on 2006 and 2007 and he used:

=(C2-B2)/ABS(B2)

 

[Chip Pearson]

As you probably know, the ABS function returns the positive value of
the value passed to it. E.g.,

=ABS(-1) returns 1
=ABS(1) returns 1

So the question remains, of what are you trying to get the ABS of?
Perhaps

=ABS((C2-B2)/B2)

I think more detail is required for a definitive answer.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Fred Smith]

What's wrong with the formula "the person who did this last year" used? Why
not use it?

Regards,
Fred.

 

[Pammy]

His formula was using only two years 2006 and 2007 and the formula
=(C2-B2)/ABS(B2) worked, but I cannot figure out how to use this formula
based on four years: 2005, 2006, 2007 and 2008 to get a variance. He keeps
telling me to use the ABS function, but I cannot figure how to get a
percentage using this function. I may not be understanding the ABS function.

 

[Fred Smith]

What do you want to calculate the percentage of?
2005 vs 2008?
Something else?
To be honest, what you're having trouble with is percentages.
Ignore the ABS for now. Add it after you have the percentage calculation
done.

Regards,
Fred.

 

[joeu2004]

His formula was using only two years  2006 and 2007
and the formula =(C2-B2)/ABS(B2) worked, but I cannot
figure out how to use this formula based on four years:
 2005, 2006, 2007 and 2008 to get a variance.  He keeps
telling me to use the ABS function, but I cannot figure
how to get a percentage using this function.  I may not
be understanding the ABS function.

Forgive me, but I think your misunderstanding is much more fundamental
that that. And it is not clear what question you are asking.

First, the formula you have above gives you the percentage difference
of C2 over B2.

Normally, we would write simply =(C2-B2)/B2 (or an equivalent
formula). The benefit of using ABS(B2) in the denominator is that it
gives meaningful results when either C2 or B2 is negative. For
example, if B2 is -100 and C2 is 100, =(C2-B2)/B2 gives -200%, which
is nonsense, whereas =(C2-B2)/ABS(B2) gives 200%, which makes sense.

But that seems to have nothing to do with your real question, at least
not the one you describe above. But honestly, I don't really
understand your real question.

Take a step back, forgot about ABS, and ask the question you really
want to ask. It goes something like this: "I have these 4 years of
data, and I want to compute <this>. How do I do it?"

You said something about wanting a "variance". Unfortunately, that
word is ambiguous, having very different meanings for accountants and
statisticians. I suspect you don't really want the VARP function. I
suspect you want the percentage difference. Right?

But you said something about wanting whatever "based on four years".
Perhaps you want to compute VARP as a percentage of AVERAGE. That
makes sense in some contexts. is that what you want?!

Assuming not, I don't know how you want whatever it is you want "based
on four years".

Do you simply want the percentage difference of the data for 2005 over
2006 and for 2008 over 2007. (You already have it for 2007 over
2006.) If the data are in A2, B2, C2 and D2, then you want (B2-A2)/ABS
(A2) and (D2-C2)/ABS(C2).

You also said you cannot figure out "how to get a percentage". Is
this simply a formatting question?

Right-click on a cell, click on Format Cells, then Number, then
Percentage and select a desired number of decimal places.

I hope this is getting you closer to an answer. If not, please step
back and reformulate your question. Forget about ABS. That presumes
a solution to a problem we don't yet understand.

HTH.