How to multiple columns with specific number?

[gongyunqian622]

Hi,

In Sheet 2 row 1 we have company names which should match to sheet 1 company's name. For those companies show in sheet 2, we need to multiple average rate to amounts in sheet 1. For example, Company-5, average rate is 0.695224, on sheet 1 column F, we will use 0.695224*each cell in column F. The column sequence in sheet 1 cannot be change. Thank you!!

[mceleavey]

Hi @gongyunqian622 ,

I've attached the workflow for you. This was achieved using a bit of transpose and cross-tab magic:

Your data is incomplete, so there are null values and lots of un-matched records, but this should work.

Hope this helps,

M.

[gongyunqian622]

Thank you for your help! But numbers in other companies are all blank now. How to fix it?

[mceleavey · Accepted solution]

Your data is incomplete.

When you join to the other sheet, not all are populated, so therefore the multiplication will not be applied. You can change the "Calculation" field in the formula tool to read:

if isnull([Average Rate]) then [Value] else [Value]*[Average Rate] endif

This will keep the original value if there is no average figure.

I've attached that updated version.

M.