Vancouver Off-Topic / Current Events The off-topic forum for Vancouver, funnies, non-auto centered discussions, WORK SAFE. While the rules are more relaxed here, there are still rules. Please refer to sticky thread in this forum. |  |
04-30-2012, 10:30 AM
|
#1 | MiX iT Up!
Join Date: May 2006 Location: vancouver
Posts: 8,150
Thanked 2,074 Times in 870 Posts
Failed 642 Times in 183 Posts
| 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!
__________________ Sometimes we tend to be in despair when the person we love leaves us, but the truth is, it's not our loss, but theirs, for they left the only person who couldn't give up on them.
Make the effort and take the risk.. "Do what you feel in your heart to be right- for you'll be criticized anyway. You'll be damned if you do, and damned if you don't." - Eleanor Roosevelt |
| |
04-30-2012, 12:19 PM
|
#2 | OMGWTFBBQ is a common word I say everyday
Join Date: Nov 2005 Location: Vancouver
Posts: 5,146
Thanked 160 Times in 74 Posts
Failed 57 Times in 18 Posts
|
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
|
| |
04-30-2012, 01:17 PM
|
#3 | Need my Daily Fix of RS
Join Date: Mar 2010 Location: Burnaby
Posts: 287
Thanked 46 Times in 38 Posts
Failed 8 Times in 5 Posts
|
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.
|
| |
04-30-2012, 01:23 PM
|
#4 | Need my Daily Fix of RS
Join Date: Mar 2010 Location: Burnaby
Posts: 287
Thanked 46 Times in 38 Posts
Failed 8 Times in 5 Posts
|
=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
|
| |
04-30-2012, 01:57 PM
|
#5 | SFICC-05*
Join Date: Mar 2002
Posts: 7,135
Thanked 545 Times in 127 Posts
Failed 107 Times in 33 Posts
| Quote:
Originally Posted by 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! | 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.
__________________ Quote:
Originally Posted by Hyde nammer don't listen to me, they listen to money. do you know how cheap it is to have someone killed in vancouver? let alone a beat down |
Last edited by chun; 04-30-2012 at 02:36 PM.
|
| |
04-30-2012, 02:29 PM
|
#6 | My homepage has been set to RS
Join Date: Dec 2008 Location: Vancouver
Posts: 2,308
Thanked 825 Times in 341 Posts
Failed 203 Times in 77 Posts
|
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"
|
| |
04-30-2012, 02:59 PM
|
#7 | MiX iT Up!
Join Date: May 2006 Location: vancouver
Posts: 8,150
Thanked 2,074 Times in 870 Posts
Failed 642 Times in 183 Posts
|
^ for a drunk russian, you might be onto something. Will try and report back.
__________________ Sometimes we tend to be in despair when the person we love leaves us, but the truth is, it's not our loss, but theirs, for they left the only person who couldn't give up on them.
Make the effort and take the risk.. "Do what you feel in your heart to be right- for you'll be criticized anyway. You'll be damned if you do, and damned if you don't." - Eleanor Roosevelt |
| |
04-30-2012, 03:08 PM
|
#8 | Rs has made me the man i am today!
Join Date: Feb 2002 Location: W.R.
Posts: 3,385
Thanked 2,101 Times in 351 Posts
Failed 29 Times in 18 Posts
|
^ 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
|
| |
04-30-2012, 03:18 PM
|
#9 | My homepage has been set to RS
Join Date: Dec 2008 Location: Vancouver
Posts: 2,308
Thanked 825 Times in 341 Posts
Failed 203 Times in 77 Posts
| Quote:
Originally Posted by tiger_handheld ^ for a drunk russian, you might be onto something. Will try and report back. | excel no match for vodka. i smash excel vith fist
|
| |
04-30-2012, 03:45 PM
|
#10 | MiX iT Up!
Join Date: May 2006 Location: vancouver
Posts: 8,150
Thanked 2,074 Times in 870 Posts
Failed 642 Times in 183 Posts
|
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"
__________________ Sometimes we tend to be in despair when the person we love leaves us, but the truth is, it's not our loss, but theirs, for they left the only person who couldn't give up on them.
Make the effort and take the risk.. "Do what you feel in your heart to be right- for you'll be criticized anyway. You'll be damned if you do, and damned if you don't." - Eleanor Roosevelt
Last edited by tiger_handheld; 04-30-2012 at 03:52 PM.
|
| |
04-30-2012, 05:53 PM
|
#11 | I subscribe to Revscene
Join Date: Jun 2006 Location: Vancouver
Posts: 1,911
Thanked 849 Times in 367 Posts
Failed 12 Times in 9 Posts
|
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?! |
| |
04-30-2012, 09:01 PM
|
#12 | The "You'd Know" Moderator
Join Date: Apr 2001 Location: Home
Posts: 20,931
Thanked 276 Times in 140 Posts
Failed 11 Times in 9 Posts
|
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.
__________________ 08 CBR600RR 03 IS300 Ezekiel 25:17. The path of the righteous man is beset on all sides by the inequities of the selfish and the tyranny of evil men. Blessed is he who, in the name of charity and good will, shepherds the weak through the valley of the darkness. For he is truly his brother's keeper and the finder of lost children. And I will strike down upon thee with great vengeance and furious anger those who attempt to poison and destroy my brothers. And you will know I am the Lord when I lay my vengeance upon you. |
| |  |
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts HTML code is Off | | | All times are GMT -8. The time now is 11:32 PM. |