Expression with operator from table

[azimabadi]

Hi all,

my data model is something like this :

Name        O        V2

----------+------+--------------

Jack     |    *   |     2

John     |    /   |     4

Name       V1

----------+--------

Jack     |  100

Jack     |  200

John     |    50

John     |  230

i need an expression that calculate the value of ( sum(V1) O V2 ) for each row. This means that i need :

Jack : 300 * 2 = 600

John : 280 / 4 = 70

i tested sum(v1) $(O) $(V2) , but it dosen't work.

i will appreciate any idea.

Best regards

Peyman

[rwunderlich · Accepted solution]

Here's one approach.

=pick(

match(O, '*', '/')

,sum(V1) * V2

,sum(V1) / V2

)

I'm not sure how many operators you have. But if you have just * and /, another approach would be:

=sum(V1) * if(O='/', 1/V2, V2)

-Rob

http://robwunderlich.com

[azimabadi]

Thank you Rob for answer. It solved my problem.