The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
 All Forums
 Origin Forum
 Origin Forum
 Using lookup (or maybe a better idea?)

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!

Screensize:
UserName:
Password:
Anti-Spam Code:
Format Mode:
Format: BoldItalicizedUnderlineStrikethrough Align LeftCenteredAlign Right Horizontal Rule Insert HyperlinkUpload FileInsert Image Insert CodeInsert QuoteInsert List
   
Message:

* HTML is OFF
* Forum Code is ON
Smilies
Smile [:)] Big Smile [:D] Cool [8D] Blush [:I]
Tongue [:P] Evil [):] Wink [;)] Clown [:o)]
Black Eye [B)] Eight Ball [8] Frown [:(] Shy [8)]
Shocked [:0] Angry [:(!] Dead [xx(] Sleepy [|)]
Kisses [:X] Approve [^] Disapprove [V] Question [?]

 
Check here to subscribe to this topic.
   

T O P I C    R E V I E W
Tukulti-Apil-Esarra Posted - 12/22/2015 : 8:41:18 PM
I want a column where it only sums the values of a different column that match a string in a third column. I'll explain with an example. I have the following:

Col(A)   Col(B) Col(C) Col(D)
Buy        500    500     80
Buy        500   1000     80
Exchange  1000   2000     80
Dividend    80   2080     80
Buy        500   2580     80
Exchange     0   2580     80
Dividend   100   2680     80
Dividend   300   2980     80
Buy        500   3480     80
Exchange  1500   4980     80
Dividend    30   5010     80

In Col(C), I have used Set Values to sum the values in column B: Col(C) = Sum(Col(B)). No problem there. What I want to do in Col(D) is to sum only the values listed as "Dividend".

Before I even try a sum, I thought I'd try to see if I can easily pick out the values labeled as "Dividend", so I used lookup in this fashion in Set Values:

Col(D) = lookup("Dividend",Col(A),Col(B))

However, doing this fills the entire Col(D) with only the first "Dividend" value. So Col(D) now looks like the example above. What went wrong? I assumed it would pick out the "Dividend" value in each row and (maybe) fill the rest with zeros. Something like this (note, before I'm trying a sum):

Col(A)   Col(B) Col(C) Col(D)
Buy        500    500      0
Buy        500   1000      0
Exchange  1000   2000      0
Dividend    80   2080     80
Buy        500   2580      0
Exchange     0   2580      0
Dividend   100   2680    100
Dividend   300   2980    300
Buy        500   3480      0
Exchange  1500   4980      0
Dividend    30   5010     30

Any help? Or a better idea?
3   L A T E S T    R E P L I E S    (Newest First)
Tukulti-Apil-Esarra Posted - 12/23/2015 : 11:00:55 AM
Great, thanks to both of you!

Both
Sum(Exact("Dividend",Col(A)$)?Col(B):0)
and
Sum(Col(A)=="Dividend"?Col(B):0)
did the work.

(The zero's are needed for the sum.)


EDIT: For future reference, the zero's are not needed for the sum. A single dash (-) will also allow the summation (all cells not fulfilling the condition will be filled with --). If one uses two dashes (--) in the Set Values, the summation does not work.
snowli Posted - 12/23/2015 : 09:59:26 AM
Origin supports conditional operator.
In Set Column Vauels dialog, you can choose Formula: Load Samples: Conditional Operator (?:) to see the example.

In Set Column Value dialog of Col(D), if you try
col(A)=="Dividend"?col(B):--

and click Apply, u can see coL(D) will only show values of column B if column A is Dividend.

If you change it into
sum(col(A)=="Dividend"?col(B):--)
and click Apply

You can get the sum of Dividend.

Thanks, Snow
Echo_Chu Posted - 12/23/2015 : 01:59:31 AM
Hi,

I am afraid that lookup() is not used in this way. By default it will searches vector in an increasing order and always return the first value.

For your case, I would suggest you use condition as below.

Exact("Dividend",col(a)$)?col(b):0


Echo
OriginLab Technical Service

The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000