Subtracting total of duplicate rows from a different field

[johneodell]

I'm working on converting some Excel data to Alteryx and have been having trouble with one of the formulas. I have two columns of values and the excel file is is using a sumif to compare the TOTAL of values in Value 2 to the value listed in Value 1 for two possible actions:

New Value Action #1: if value 1<value 2 then use value 1

New Value Action #2: if value 1<the total of multiple value 2 keys, the subtract total value 2 from value 1

I know my explanation is a bit confusing so let me know if you have any clarifying questions. Any help is greatly appreciated.

[GaurVitul]

Can you please explain how you are getting 50 in column D for row 5?

[johneodell]

What the original formula is doing is subtracting Value 1 from the total of Value 2 and replacing the second entry with the result:

(Value 2 + Value 2) - Value 1

(200 + 100) - 250 = 50

[GaurVitul]

thanks!

Are you trying to execute below condition in the same column?

If value 1 < value 2 and value 1 < (value 1 + value 2), then (value 1 + value 2) - value 1 

elseif If value 1 < value 2 and value 1 > (value 1 + value 2), value 1

else null?

[echuong1 · Accepted solution]

Do you want the two "actions" to create separate columns or should both sets of rules be consolidated for a single new column. 

If I understand your criteria correctly, the attached workflow should work for your purposes. I demonstrated both separate new columns and a consolidated one. Essentially, you want to find the total of column 2 and then add these values to each record for the comparison. 

Let me know if that helps or if you need further adjustments/clarification. 

[johneodell]

@echuong1 So simple and it works!! Thank you! It makes me feel better that I was close to that but you pushed it over the finish line.

Thanks!!!