[tiger_handheld]

Is there a way to find offsetting values in excel?

I have +3100 in cell AL7. Somewhere between AL8 and AL4077 there is a -3100.

In AM7 , I need a formula that will search from AL 8 to AL4077 to see if there is a -3100 and drop it in AM7 so on and so on. THe amount in AL7 is + or - , so the formula must find the offsetting value.

I hope that makes sense. Going through 5000 rows of data one at a time is ridonculous!

[wouwou]

Can you vlookup for a cell that will have a sum value of 0? Say vlookup in that 4000 cells that when AL7 x equals 0, output that value?

Just posting on top of my head on the go train, without trying to do it in excel

[Jeremy617]

sounds like something that could be done easily in VBA, but without looking at it i'm having trouble understanding what you're actually trying to do.

-3100 is in AL7, so you want to search and see if +3100 is somewhere else, and if it is, just essentially copy/paste the +3100 into the cell next to AL7?

seems like it could also be done with a vlookup like wouwou suggested.

If -3100 is in AL7, i would do a vlookup with a reference of AL7*-1 on the entire AL column with a column index of 1.

if you want to host the spreadsheet somewhere i can whip it up.

[Jeremy617]

=IFERROR(VLOOKUP(AL4*-1,$AL$3:$AL$13,1,0),"")

put that in column AM and fill down, adjusting the table size

gave me this:

50
100 -100
200
40 -40
800
-40 40
-100 100
25 -25
65
-25 25

[chun]

Is there a way to find offsetting values in excel?

I have +3100 in cell AL7. Somewhere between AL8 and AL4077 there is a -3100.

In AM7 , I need a formula that will search from AL 8 to AL4077 to see if there is a -3100 and drop it in AM7 so on and so on. THe amount in AL7 is + or - , so the formula must find the offsetting value.

I hope that makes sense. Going through 5000 rows of data one at a time is ridonculous!

Use this formula:
=lookup(lookup_value, array)

Then input:
=lookup(-(AL7), AL8:AL4077)

The lookup-value will be the +/- value of AL7, it'll look through AL8:AL4077 and spit out the value if there is one.

[drunkrussian]

type this into AM7 and then pull down, adjusting the table size

=IF(ISNA(VLOOKUP(-AL7,AL8:AL4077,1,FALSE))=FALSE,VLOOKUP(-AL7,AL8:AL4077,1,FALSE),"No Offset Value found")

here's what it means:

1. =IF(ISNA(VLOOKUP(-AL7,AL8:AL4077,1,FALSE))=FALSE <--if between al8 and al4077, the negative of al7 exists, then:
2. VLOOKUP(-AL7,AL8:AL4077,1,FALSE) <-- paste the negative of al7
3. ,"No Offset Value found") <--otherwise paste "No Offset Value found"

[tiger_handheld]

^ for a drunk russian, you might be onto something. Will try and report back.

[Roach]

^ Do you actually want it to sum in AM7 the total of the 3100's? Or count the number of instances?

I'm not quite sure I understand your purpose exactly but I came up with this:

To sum:

=SUMIF($AL$8:$AL$4077,(-$AL$7),($AL$8:$AL$4077))

To count

=COUNTIF($AL$8:$AL$4077,(-$AL$7))

Kev

[drunkrussian]

^ for a drunk russian, you might be onto something. Will try and report back.

excel no match for vodka. i smash excel vith fist

[tiger_handheld]

Russians formula works, but does not pick up the offset when the positive amount is above the range.

IE.

A | B
1 -644| =formula > 644 -- works good
2 644 | = formula > "no offset found" -- wrong


Cell B2, should say -644 because the offset is in B1.

important thing to note, some amounts repeat. for example, there maybe 4 instances of +25 , but there maybe only 2 instances of -25. so I need the formula to pick up only 2x +25 to offset the 2x -25. the remaining 2 +25's should be "no offset found"

[dat_steve]

spend all day in front of excel sheets at work
go home expecting to let brain rest while on VOT
...
motherf*ing excel thread on rs?!

http://t.qkme.me/35onan.jpg

[impactX]

Just curious, why don't you just do it the easy way?

Fill down a column with AL(x+1) - AL(x). Put an auto filter on the spreadsheet and then you can see ALL the offsets with the pulldown menu.