Compare two ranges and count numbers that are greater or less by 8

[sans]

Hello,

I have these numbers from C4:I4 and I'd like to count how many of these numbers are greater or less by 8 from any of the numbers in the previous row

For instance,


A) 08 09 29 40 45 58 89 -
B) 01 15 21 29 32 67 88 - 3 (because 1 & 9 have a difference of 8, 21 and 29,32 and 40)
C) 12 13 27 31 38 54 55 - 0 (no numbers have a difference of 8 with the numbers in B)
D) 04 21 33 38 45 68 69 - 2 ( because 13&21,4&12 have a difference of 8)
E) 21 04 33 37 40 67 70 - etc...
F) 08 20 27 32 41 54 63
G) 04 21 19 20 22 39 70
H) 01 15 21 29 32 67 63

Thank you in advance,
Sans

[NBVC]

Here's one way:

Please Login or Register  to view this content.

copied down starting from J3.

Note that line C does have some differences of 8.. i.e. 21 and 13

[NBVC]

Here is a shorter version:

=SUMPRODUCT(--(ABS(C2:I2-N(OFFSET(C3,0,ROW(INDIRECT("1:7"))-1,1,1)))=8))

[sans]

Exactly what I was looking for. Thank you again for your all of your help NBVC!!! You are right, it should have been 1 in C)

[sans]

Both work great. Thanks again! Sans

[sans]

Hi NBVC,

Your formulas above work great. However when I have these numbers to check

0 0 1 2 3 4 4
1 3 3 4 4 4 5


the formula

=SUMPRODUCT(--(ABS(C5:I5-N(OFFSET(C6,0,ROW(INDIRECT("1:7"))-1,1,1)))=1))) gives out wrong results.

It works perfectly when the numbers are i.e.

08 21 23 30 34 40 63
02 09 33 35 47 49 72

Is it because the above criteria have duplicate values or because of the 0 values?

Thank you
Sans

[NBVC]

What is it giving and why is it wrong? You are looking for differences of 1 and most of those are different more than 1. Each element in row 5 is being compared to each first element in row 6, then again each row 5 element is compared to second row 6 element and so on....

[sans]

I understand now. I apologise, my mistake. Sans

[sans]

Hi NBVC,

Is it possible to help me with a small variation of your formula in case there are duplicate comparisons when some of values in the ranges are not unique? Basically, I am trying to get the amount of numbers that have a difference of 1 but ignore duplicates.

I have attached an example for better understanding.

Thank you for your time,
Sans

[NBVC]

I get 9

1. D6 with F5
2. E6 with F5
3. F6 with F5
4. G6 with E5
5. G6 with H5
6. H6 with F5
7. H6 with I5
8. I6 with I5
9. I6 with J5


=COUNT(IF(ABS(D6:J6-IF(N(OFFSET(D5,0,ROW(INDIRECT("1:7"))-1,1,1))<>N(OFFSET(E5,0,ROW(INDIRECT("1:7"))-1,1,1)), N(OFFSET(D5,0,ROW(INDIRECT("1:7"))-1,1,1)),9999))=1,1))

confirmed with CTRL+SHIFT+ENTER

[sans]

Thank you very much NBVC,

Yes the result should be 9, sorry about that - again

It works great!!! Thank you. I was wondering if it is possible to ask for one more variation, to receive the count of numbers in a range.

Again, thank you very much for all of your help,

Sans

[NBVC]

It can probably be done with a fancy MMULT or MMULT/TRANSPOSE function.. but I can't quite seem to put it together right now... so here is a bit of a brute force formula that should still work fine....

Please Login or Register  to view this content.

[sans]

Hi NBVC,

Thank you very much for your reply. I am getting a mix of correct and incorrect results.

For example the result when checking these numbers,

0 0 1 2 2 3 7 12
0 1 2 3 4 4 5 9

is 4 but the correct is 6, as there are 6 numbers out of the eight that have a difference of one with the previous range.

D6 has a difference of 1 with F5
E6 with either D5 or E5
F6 with F5
G6 with either G5 or H5
H6 with I5
I6 with I5

I tried changing the formula a little but obviously I got nowhere

Thank you for your help,
Sans

[NBVC]

The way my formula was designed is how I interpreted the question initially in the attachment of Post # 11.

That is to compare each cell in row 6 with the cells in row 5 beginning always at D5 and only to the cell above the currently checked cell in row 6 and if at least 1 difference of 1 appears, then count it...

If that is indeed the interpretation, then I do get 4 with your sample...

E6 with D5 and E5
F6 with F5
G6 with G5
I6 with I5

So D6, H6, J6 and K6 don't have that condition...

[sans]

I apologise for the misunderstanding.

What I meant was to be able to count if any value value has a difference of 1 with any of the values in the previous range. Each cell in row 6 is compared with all the cells in row 5 to see if it has a difference one.

For example, if the numbers are

0 0 1 2
0 1 2 3

the result should be 4 as all 4 numbers in the second row have the difference of 1 with any of the cells in the first row.
The zero in the second row with the 1 in the first row
The 1 in the second row with the 2 in the first row
The 2 in the second row with the 1 in the first row
The 3 in the second row with the 2 in the first row.

Again, I apologise for the misunderstanding and thank you for your help and replies,

Sans

[NBVC]

Then try:

Please Login or Register  to view this content.

[sans]

That's amazing, thank you NBVC, I apologise for my vague description in Post 11#. This did the trick.
Thank you for all your help and understanding,
Sans

[sans]

Hi NBVC,

I wanted to ask if there is somehing I need to change in the formula (which works great)

=SUMPRODUCT(--(ABS(C58:I58-N(OFFSET(C59,0,ROW(INDIRECT("1:7"))-1,1,1)))=1))

if I want to receive the count of numbers that have a difference of 2. I have changed number 1 at the end of the formula to 2 ,i.e.

=SUMPRODUCT(--(ABS(C58:I58-N(OFFSET(C59,0,ROW(INDIRECT("1:7"))-1,1,1)))=2))

but I am receiving a mixture of correct and incorrect results. Is the formula specific to counting the numbers with a difference of 1 or do I need to change something else to make it work for receiving the count of numbers with a difference of 2?

The numbers I am trying to apply the formula with a difference of 2 are formatted as custom 00. I tried formatting them as text or general but the results are still incorrect.

Thank you,
Sans

[NBVC]

Can I see a sample workbook showing where it is giving incorrect results?

[sans]

Hi NBVC,

Thank you very much for your reply. I have attached a workbook with two examples. Most likely the two comments in the sheet are totally wrong

Thank you for your help,
Sans

[NBVC]

A couple of things...

you misarranged the -- in the formula...

It should be:

=SUMPRODUCT(--(ABS((C4:I4-N(OFFSET(C5,0,ROW(INDIRECT("1:7"))-1,-1,-1))))=1))

=SUMPRODUCT(--(ABS((C16:I16-N(OFFSET(C17,0,ROW(INDIRECT("1:7"))-1,-1,-1))))=2))

the formula was, as per the initial phases of this thread to count how many differences of a specific number there were, not specifically stating that you want to use each number once only... that would be a bit of a different animal.

[NBVC]

If you want that, then try:

In N5,

Please Login or Register  to view this content.

and in N17

Please Login or Register  to view this content.

[sans]

Sorry about that.

I messed around with the formula to see if I could change the results I was receiving. The reason I did that is because I remembered when you replied in another thread of mine that placing the -- in front of the formula

=--LEFT(TEXT(F24,"00"),1)

forces the values as numbers, so I thought to give that a shot by placing two -- in each side of the formula

I apologise for not mentioning it in the original post, but would it be possible for the formula to count each number only once? Also, I just noticed this and I didn't realise it before that a number could count twice to the previous range - it doesn't happen often and most of the times the results are correct that's why it skipped by me.


Thank you very much for your help,
Sans

[NBVC]

See my last formula above.

[sans]

"so I thought to give that a shot by placing two -- in each side of the formula"

I tried that before I realised the duplicate counting.

Again, I apologise for being vague in my original post. The formulas work great. Thank you very very much NBVC. You have been helping me so much!!!

[sans]

And congratulations on your 30,000 posts!

[NBVC]

Thank you, sans and I am glad to help as much as I can...