REVscene - Vancouver Automotive Forum


Welcome to the REVscene Automotive Forum forums.

Registration is Free!You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today! The banners on the left side and below do not show for registered users!

If you have any problems with the registration process or your account login, please contact contact us.


Go Back   REVscene Automotive Forum > Automotive Chat > Vancouver Off-Topic / Current Events

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.

Reply
 
Thread Tools
Old 04-30-2012, 10:30 AM   #1
MiX iT Up!
 
tiger_handheld's Avatar
 
Join Date: May 2006
Location: vancouver
Posts: 8,092
Thanked 2,057 Times in 857 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!
Advertisement
__________________

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
tiger_handheld is offline   Reply With Quote
Old 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
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
wouwou is offline   Reply With Quote
Old 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
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 is offline   Reply With Quote
Old 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
=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
Jeremy617 is offline   Reply With Quote
Old 04-30-2012, 01:57 PM   #5
SFICC-05*
 
chun's Avatar
 
Join Date: Mar 2002
Posts: 7,135
Thanked 545 Times in 127 Posts
Quote:
Originally Posted by tiger_handheld View Post
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 View Post
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.
chun is offline   Reply With Quote
Old 04-30-2012, 02:29 PM   #6
My homepage has been set to RS
 
drunkrussian's Avatar
 
Join Date: Dec 2008
Location: Vancouver
Posts: 2,308
Thanked 825 Times in 341 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"
drunkrussian is offline   Reply With Quote
This post thanked by:
Old 04-30-2012, 02:59 PM   #7
MiX iT Up!
 
tiger_handheld's Avatar
 
Join Date: May 2006
Location: vancouver
Posts: 8,092
Thanked 2,057 Times in 857 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
tiger_handheld is offline   Reply With Quote
Old 04-30-2012, 03:08 PM   #8
Rs has made me the man i am today!
 
Roach's Avatar
 
Join Date: Feb 2002
Location: W.R.
Posts: 3,374
Thanked 2,021 Times in 342 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
__________________
DealerShift
Roach is offline   Reply With Quote
Old 04-30-2012, 03:18 PM   #9
My homepage has been set to RS
 
drunkrussian's Avatar
 
Join Date: Dec 2008
Location: Vancouver
Posts: 2,308
Thanked 825 Times in 341 Posts
Quote:
Originally Posted by tiger_handheld View Post
^ for a drunk russian, you might be onto something. Will try and report back.
excel no match for vodka. i smash excel vith fist
drunkrussian is offline   Reply With Quote
This post thanked by:
Old 04-30-2012, 03:45 PM   #10
MiX iT Up!
 
tiger_handheld's Avatar
 
Join Date: May 2006
Location: vancouver
Posts: 8,092
Thanked 2,057 Times in 857 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.
tiger_handheld is offline   Reply With Quote
Old 04-30-2012, 05:53 PM   #11
I subscribe to Revscene
 
dat_steve's Avatar
 
Join Date: Jun 2006
Location: Vancouver
Posts: 1,906
Thanked 842 Times in 364 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?!

dat_steve is offline   Reply With Quote
This post thanked by:
Old 04-30-2012, 09:01 PM   #12
The "You'd Know" Moderator
 
impactX's Avatar
 
Join Date: Apr 2001
Location: Home
Posts: 20,905
Thanked 232 Times in 124 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.
impactX is offline   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -8. The time now is 08:16 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
SEO by vBSEO ©2011, Crawlability, Inc.
Revscene.net cannot be held accountable for the actions of its members nor does the opinions of the members represent that of Revscene.net