How do I calculate the DIFFERENCE between 2 cells?

Hi Ron Herrmann,
Welcome to Numbers discussions.

Of course WWJD is correct.

Here's another thought: many times to find a difference I'll use an IF statement. Why? I want a positive number difference.

Example: Picture you want the difference between A2 and B2. If A2 is always larger then =A2-B2 is appropriate ( A2 = 10, B2 = 5; answer 5). However, if there's a possibility of B2 sometimes being larger than A2 then the answer is preceded with a minus sign (B2 = 5, A2 = 10; answer -5) which may or may not be your desired results.

IF statement reads: =IF(A2>B2,A2-B2,B2-A2). In the above example the answer is always 5, never a minus five, -5. The question now is will the result stand as is or be used in a formula or formulae for further calculation? If used further what's the expected results? Yes always questions questions questions.

Hope this helps you. Have fun here.

Sincerely,
RicD

I'm sorry, WWJD really isn't correct. If you read the OP's questions very carefully, the equation he wants is: =B2-A2

He specifically says that he wants a positive result if the second number is larger and a negative result if the first number is larger.

Second, why would you use an IF statement (for your example) when ABS() is clearly the function you want?

I'm generally impressed with the questions here, but these really look like 1st grade problems. Am I missing something?

I was only giving an example of the function to use.

=A2-B2 <value in A2 minus values in B2> or =B2-A2 <value in B2 minus value in A2> depending on what the user wants to do (first number can be in cell A2 or B2, etc.) Maybe ABS(A2-B2) is correct (the absolute difference) then order does not matter.

While this is a "strange" question, we may not have a good understanding what the OP is looking for.

Regards,

Ron Herrmann wrote:
My spreadsheet experience is rudimentary - but it seems to me that I ought to be able to calculate a positive or negative result between the values of 2 cells whether their contents are positive or negative.
I think I'm attempting to calculate the difference, but am not certain...
e.g. if the second cell value is higher than the first, the result would be a positive number
if the second cell value is lower than the first, the result would be a negative number.

As far as I know, if mathematical rules are always the same, and if your question is correctly described,
if what you call first value is stored in A2
and what you called second value is stored in B2

the result would be given by

=B-A if the formula is in row 2

=B2-A2 if the formula is not guaranteed to be in row 2.

You may check that they works even if one or two of the given values is(are) negative.

It's the beauty of mathematics 😉

Yvan KOENIG (from FRANCE mercredi 14 mai 2008 10:46:57)

[http://manuals.info.apple.com/en/Numbers08_UserGuide.pdf]
Page 94, chapter 5 has a nice little table with examples of the basics of formulas. Once you get that part, I strongly suggest reading the whole book. Its not a hard, or even boring read, if your interested in learning the program.

I actually use its Formula section (with a few markups) instead of M$'s formula help files for others in my office.

Jason

Message was edited by: jaxjason oops, it was page 94.

Why have you downloaded the PDF?
As far as I know, it's delivered on the iWork '08 CD and it is also in the image disk for those who got the trial version.

Yvan KOENIG (from FRANCE mercredi 14 mai 2008 19:13:44)

Hi Kyd,
Welcome to Numbers discussions.

Allow me to ask you the question slightly different than how you asked me: "Second, why would you use an ABS() statement when IF() is clearly the function you want?"

You say Toe-mato, I say Toe-Mahto. Using the IF() or ABS() obtains the same results. The choice of which formulae to use is a judgement call nothing more. 🙂

In part, the reason I used the IF() statement is many folks don't understand IF() statements; nor nested IF(). Though the OP question didn't require nested IF() I had a opportunity show how IF() works plus can be applied. In my opinion it's more important to know the use of IF() than to know the use of ABS() function. So I took it as a teaching thingy.

Of course I could have also included the ABS() as another way to obtain the exact same results, maybe should have, I chose not to. All that being said you're correct the ABS() would have worked as well, less typing for a user.

Again welcome to Numbers discussions, have fun here.

Sincerely,
RicD

For my part, its easier to post a link to the PDF than to tell them to look on the CD. If they're at work like i am, its not available.

Jason

p.s. Yvan - hows it going? nice t see ya' still around here.

Hi Ric,

In the example you gave, you clearly wanted the absolute value of the difference. I believe in using the correct tool for the job and showing the most straight forward method in an example. Your example of IF() was a convoluted way to do a very simple function. There are many great reasons to use IF(), but that wasn't one of them. One great use is to check to see if a field is blank, if it is, do nothing, else perform the desired calculation. At the very least that reduces the number of 0's cluttering the sheet and at best avoids little red triangle warning boxes.

Thanks for welcoming me. I have been reading for a while and only post on rare occasion. Sadly, my nick is a bit of a misnomer as I've been using it for 30 years of being online.

Hello jason

A HD failed - not really sure because,
Sata controller chip failed
Sonnet doesn't deliver their Sata-PCI card

but all in one, it's OK 😉

Look at my iDisk

<http://idisk.mac.com/koenigyvan-Public?view=web>

in the folder "For_iWork" to check that I tell the truth 😉

Yvan KOENIG (from FRANCE mercredi 14 mai 2008 20:48:00)

Hi Kyd,
About 30 years for me as well. Didn't I graduate high school late last night, oh but my mirror tells a completely different story.

Your reasoning for ABS() vs. IF() is well explained, I understand your reasoning well. ABS() would have been a better choice. Being as I had my reasons quite different than yours plus we both obtained the same results let's agree we're both correct. If not then about function choice we certainly can politely agree to disagree.

Obviously you have much to share, I look forward to more of your input in theses forums.

Sincerely,

RicD

Value 1 in A2, Value 2 in B2

Equation for difference: =A2-B2

Regards,

never a problem, nobody here minds helping those that are at least trying.

Have fun, I love the whole suite of programs they made, (keynote is easily the best slide program around.)
Jason