Excel Help Needed.
 

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!

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

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.

=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

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.

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"

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

^ 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

excel no match for vodka. i smash excel vith fist

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"

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

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.