Formula to find correct input and output

[truxor]

Morning,

I have a range of cells that show the inputs and outputs by steps.


What I want the formula to do is grab the first input given and then grab the last output from the range.


The problem I have is that the process of the output may vary depending on which process it went through, that's where I am having trouble automating it.


An example:


[TABLE="width: 270"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Input[/TD]
[TD]Output A[/TD]
[TD] Output B[/TD]
[/TR]
[TR]
[TD]Step A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Step B[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Step C[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]



I don't want to put a total at the bottom for all since the output, Step C, was the actual
amount in the process. What I want is Step C Output (30) and Step A (100)
divided into each other - 30%.


How can I logically make the formula work? I have tried IFS, but getting stuck.


Also not all processes will finish with Step C, it may finish with Step A or B. I want it
to logically find the correct Input/Output.



[TABLE="width: 270"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Input[/TD]
[TD]Output A[/TD]
[TD]Output B[/TD]
[/TR]
[TR]
[TD]Step A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Step B[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]