Formula to find an amount within a dollar range

[ltomlinson]

I am in need of a formula that will allow me to flag if they have a balance within a +-$1.00 range:

For instance-

Month: Client: Account Balance:
August Bob 10.01
August Lucy 11.27
August Sam 9.56
Sept Bob -10.56
Sept Lucy -12.01
Sept Sam -9.00

I would want the formula to flag all three of these as the account balances are within +-$1.00 and we can combine invoices.


I have roughly 10,000 rows of data I am working with. I know this should be simple, but my brain is not clicking today.

Please help!

 

[KolGuyXcel]

=if(abs(c2)<=1.00,true, false)

 

[ltomlinson]

This wont work as it doesn't compare each row to the other rows.

 

[ltomlinson]

Basically, I need each unique row to compare to the other rows. For instance, I need a concatenate of Bob and the amount to flag because there is a Bob with a different amount within +-$1.00

 

[KolGuyXcel]

Oh, my bad! I misunderstood the problem!

Check if it works! I'm not sure how the data is arranged. It might work only if the data is sorted with column A first and then with Column B. And also, I've assumed that the last row in your data is less than row 10!

=IF(ABS(ABS(INDEX(B3:$C$10,MATCH(B2,B3:$B$10,0),2))-ABS(C2))<=1,TRUE,FALSE)

 

[ltomlinson]

This worked perfectly after changing the ranges. I may have ran into another issue where I need to flag both invoices when I filter. I am working on this now and will update if I need some help with that. Thank you for your help with this one!

 

[ltomlinson]

Also, the formula referenced above will also show true if the dollar amounts are the same and both positive or negative. Example: 967.01 and 966.56 will both show true as they are within the $1.00 threshold once ABS is applied. I only need it to flag if one is positive and one is negative.