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

[sloke]

Hi,

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

Name	m	n	o	Restricted
x	3	2		No
y	2		1	Yes
z		3	4	Yes

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:

Name	m	m-new	n	n-new	o	o-new	Restricted
x	3	3	2	2			No
y	2	4			1	5	Yes
z			3	9	4	20	Yes

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 solution]

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 solution]

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