The Origin Forum - Using lookup (or maybe a better idea?)

The Origin Forum

Username:	Password:
Save Password
Forgot your Password? \| Admin Options

All Forums

Origin Forum

Using lookup (or maybe a better idea?)

New Topic

Reply to Topic

Printer Friendly

Author

Topic

Tukulti-Apil-Esarra

USA
5 Posts

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?

Edited by - Tukulti-Apil-Esarra on 12/22/2015 8:43:01 PM

Echo_Chu

China
Posts

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

snowli

USA
1398 Posts

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

Edited by - snowli on 12/23/2015 10:26:15 AM

Tukulti-Apil-Esarra

USA
5 Posts

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.

Edited by - Tukulti-Apil-Esarra on 12/23/2015 10:15:11 PM

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

The Origin Forum

Snitz Forums 2000