5 Replies Latest reply: Oct 30, 2015 9:19 AM by Massimo Grossi

# Need to have a formula to calculate the result based upon two different cell and conditions

Dear All,

I have a sheet as below:

 Invoice No. Product Type Shipment Type A1 Cold Chain A1 Non Cold A1 Cold Chain A1 Non Cold A1 Non Cold B1 Cold Chain B1 Cold Chain B1 Cold Chain B1 Cold Chain B1 Cold Chain C2 Non Cold C2 Non Cold C2 Non Cold C2 Non Cold C2 Non Cold C2 Non Cold C2 Non Cold D2 Non Cold D2 Non Cold D2 Non Cold D2 Non Cold D2 Cold Chain

In the table above, for any invoice, if it contains even one "Cold Chain" product type, the shipment type should be "Cold Chain" for all entries of that invoice.

For Example: I have 5 items in invoice A1, of which 2 are cold chain and 3 are non cold. Need a formula to ensure, against each entry of invoice no. A1, it should define Shipment type as Cold Chain.

Please help me to get the solution of. You can also mark me on my personal mail id - saurabh_kumar09@yahoo.com

• ###### Re: Need to have a formula to calculate the result based upon two different cell and conditions

see tha attchment

• ###### Re: Need to have a formula to calculate the result based upon two different cell and conditions

Thank you very much for your prompt response.

I request you to upload a excel file as i am unable to open the .qvw file extension.

Cheers

• ###### Re: Need to have a formula to calculate the result based upon two different cell and conditions

CHART

straight table chart with

dimensions:

InvoiceNo

id

expressions:

ProductType

if(count({\$ <ProductType={'Cold Chain'}>} TOTAL <InvoiceNo> ProductType) >0, 'Cold Chain', ProductType)

In presentation tab, Hide Column for id dimension

SCRIPT

I just added the id field

x:

load rowno() as id, * inline [

InvoiceNo, ProductType

A1, Cold Chain

A1, Non Cold

A1, Cold Chain

A1, Non Cold

A1, Non Cold

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1 ,Cold Chain

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Cold Chain

] ;

• ###### Re: Need to have a formula to calculate the result based upon two different cell and conditions

Try this:

Table:

RowNo() as Key

Inline [

Invoice No., Product Type

A1, Non Cold

A1, Cold Chain

A1, Non Cold

A1, Non Cold

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

B1, Cold Chain

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

C2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Non Cold

D2, Cold Chain

];

Left Join (Table)

LOAD Distinct [Invoice No.],

[Product Type] as [Shipment Type]

Resident Table

Where [Product Type] = 'Cold Chain';

• ###### Re: Need to have a formula to calculate the result based upon two different cell and conditions

Thank you for your response. This is to be added as Macro i suppose, i mean adding a module and add this code there. Correct me if i am wrong.

Also, it would be great to have a formula directly as i need to have this used at many different locations and this is making it complicated as i also need to train my guys down the line for these formulas that i use in my sheet to ensure correctness of data. however, i have the option of adding protection and i have done that to save the formulated columns from being changed.Also, whatever you are attaching, please attach in excel format as .qvw format is not recognised by my system and i have no option to open this extension file.

Thank you

cheers