Complex IF Formula

[jamieeee]

Hi All, 

I am looking to perform some detailed analysis for which I need a complicated IF Formula (unless anyone can suggest another way?). I am aware that I will need to use the Formula tool within ALteryx and create a new column but any help with the actual formula would be greatly appreciated. 

Aim: to show the Total Cost of the individual as long as the quantity = Current Portfolio Q. Details below.

Examples

1) Take Adama, if the (Quantity + Current Portfolio Q) = 0 I want to show the Total Cost of 310 in the new column. 

2) Similarly, using the same formula with Ben Godfrey: (200 - 200) = 0 so I would want to show the Total Cost of 166 in the new column. 

3) EXCEPTION: Here is where it gets tricker. Using the formula on the top Bruno Fernandes line, (89 - 111) does NOT equal 0. Hence, I would want the formula to then include the row beneath it such that ((89 + 22) - 111) = 0. I would then want to show the combined Total Cost across both lines i.e. 658 (440+218). 

Thank you in advance!

Jamie

[markcurry · Accepted solution]

Jamie,  have a look at the attached workflow, hopefully it will point you in the right direction.

I'm not sure what you want to do in the case of Eddie Nketiah, where he doesn't seem to fit any of the conditions.

[jamieeee]

Thanks Mark, much appreciated.