Assigning values to constants and using them when a certain condition is met

Question

Hi,

I have a multi-part problem that needs addressing. I have a set of data in excel that looks like this:

NamemnoRestrictedx32 Noy2 1Yesz 34Yes

What I would like to do is to figure out the total weight of the restricted names in each of the columns, and using each column's "restricted weight" to determine a new value for each column. The final output should look something like this:

Namemm-newnn-newoo-newRestrictedx3322  Noy24  15Yesz  39420Yes

where:

restricted weight of m = sumif(restricted = yes, m) = 2

restricted weight of n = sumif(restricted = yes, n) = 3

restricted weight of o = sumif(restricted = yes, o) = 5

What I would have done in excel:

1) Create new row at the top and figure out the "restricted values" of each column by using the sumif formula and storing the value in the cells above each of the column headers.

2) Create 3 new columns: one after m, one after n, one after o

3) Using the "restricted values", use an if formula to figure out the new weights --> new value for column m = if(restricted = yes, restricted weight of m * m, m)

KaneG · Accepted Answer

Hi @sloke,

I can't get to my machine to build the model, but use 2 streams

A Summarize tool to Group By restricted and Sum the other columns, then filter out Restricted=No

A transpose tool to transpose the m,n,o columns

Then Join those on Name and multiply accordingly

Cross-tab if you desire.

BenMoss · Accepted Answer

Hi!

I've took @KaneG's method and adapted it slightly to build out the following workflow, this should give you what you need.

Ben