Excel functions

Discussion in 'Business Applications' started by tim150891, Aug 8, 2010.

Not open for further replies.

Joined:
Aug 8, 2010
Messages:
1
I have a large list of values and I need to find the number of values that have a absolute difference of 3 from the value above them in the list. I do not know which fuction to use. Please help.

Joined:
Jul 28, 2010
Messages:
413

3. edhicks

Joined:
Jun 30, 2008
Messages:
248
Tim
Try this
In column next to list of numbers, beside 2nd number
Assuming column A contains list to be tested and list starts in A2 then enter into B3
=if(A2-A1=3,"YES","-")
COPY DOWN TO END OF LIST

4. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Actually, since the poster wants the absolute difference, alter edhicks formula to be:
=IF(ABS(A2-A1)=3,"YES","-")
or you could do it another way and have:
=IF(OR(A2-A1=3,A2-A1=-3),"Yes","")

5. edhicks

Joined:
Jun 30, 2008
Messages:
248
slurpee55
You are probably correct but consider this.
Does absolute difference of 3 mean difference must be positive three or did poster really mean and want the absolute value of difference to be three?
To me absolute difference and absolute value of difference are two different concepts.
Maybe original poster will clarify this for us.

6. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Actually, I could interpret the first post to mean "count how many times in a list the value in a cell has a difference with an absolute value of 3 from the value in the cell above it."

But ed, I think someone who doesn't quite use "absolute value" correctly , if they use the terminology "absolute" probably means "absolute value." Still, that could have been avoided by saying +3 or -3.

As Seen On