Dynamic Spilled Array Formula using byrow

[pto160]

I have a bank report with a lot of columns, I would like to be able to use a different formula in the same column depending on the situation. Is there a way to use a Dynamic spilled array to just change the result in the first row and it will change the rest of the rows?
In this example, I would like to choose 1or 2 and it will spill the formula down without me copying it down.

Book2
	A	B	C
1	Absolute/Net	Debit	Credit
2	10.00	10
3	-20.00		20
4	40.00	40
5	-50.00		50
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=CHOOSE(1,B2-C2,ABS(B2-C2))

 

[Fluff]

do you mean you just want to show everything as a positive value?

 

[pto160]

If I choose(1, it will column B-C, which can be a negative or positive value. If I choose(2, it will be a positive value all the way down or the absolute of the columns B-C result. So I am choosing a different formula depending on the situation. I am trying to spill the results.

 

[Fluff]

How would the formula know which you wanted?

 

[pto160]

Using A3 as an example, if I select 1 in the choose function the formula is B3-C3 (0-20=-20). If I select 2 in the choose function, then the formula is abs(b3-c3)=20.

 

[Fluff]

But how are you going to select the 1 or 2, short of actually changing the formula?

 

[pto160]

Actually, now that you brought this to my attention, I thought about this more. I think I actually made this way more complicated than it should be. I could use a cell reference and it will update the formula in all the cells.

Book2
	A	B	C
1	Absolute/Net	Debit	Credit
2	10.00	10
3	20.00		20
4	40.00	40
5	50.00		50
6
7	Reference
8	2
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=CHOOSE($A$8,B2-C2,ABS(B2-C2))

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[pto160]

Thank you for your help and clarifying this. Sometimes a simpler solution works best.