How to calculate the difference between numbers

[faizzsheikh]

What formula should I use to calculate the difference between the two values.

Eg.

Note - By applying the formula =A1-B1 or =B1-A1 I get wrong ans for some instances

1st May____8th May____Difference
20_________25________-5
25_________20________5
0__________20________20
20_________0_________-20

Must return the above value under 'Difference'

I have a large amount of similar data

 

[Dimli]

try
=MAX(A1:B1)-MIN(A1:B1)
does that give you what you are looking for?

 

[faizzsheikh]

No this does not work...

The below formula worked for me. Someone helped me out with it. It was very complicated.
=IF(OR(A1=0,B1=0),B1-A1,A1-B1)


Keyword Rank__________Person Name
1_____________John
5_____________Agnes
8_____________Agnes
12____________John
20____________Agnes
13____________Agnes
3_____________John
40____________John
8_____________John
15____________Agnes
10____________John


I want to count the the numbers that John has between 1 to 10 only. In the above case John must have 4 Keyword Ran between 1 to 10

Please help!


Thanks in advance
Faiz

 

[BenMiller]

With numbers in A and names in B, here's one formula you could try:

=SUMPRODUCT((A1:A10>=1)*(A1:A10<=10),--(B1:B10="John")

If you're using Excel 2007 or newer, look at the COUNTIFS function.

 

[faizzsheikh]

Thanks a lot Ben! It worked
I will have to improve my mathematical logic to be able to do it myself.

Thanks again!
Faiz

 

[BenMiller]

No problem ... Glad I could help Appreciate the feedback!