starchyfind
New Member
- Joined
- Jan 6, 2010
- Messages
- 7
I'm trying to incorporate a new feature into an already existing spreadsheet I maintain, but I'm having trouble finding the right functions and/or logic. Basically, I'm trying to give the user a column (in this case, B, in the table below), which will allow the value in Column C to be modified depending on the value and operator entered in Column B. I'd like this functionality to be limited to a single column (or single cell per row, as the case may be).
How can I make my existing formula work or what is a better method?
This is how I'd like the logic function, but it only works with the "*" operator:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=IF(SEARCH("~*",B2,1),C2*RIGHT(B2,1),IF(SEARCH("/",B2,1),C2/RIGHT(B2,1),IF(OR(B2<0,B2>0),SUM(C2,B2))))
[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=IF(SEARCH("~*",B3,1),C3*RIGHT(B3,1),IF(SEARCH("/",B3,1),C3/RIGHT(B3,1),IF(OR(B3<0,B3>0),SUM(C3,B3))))
[/TD]
[TD]-5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=IF(SEARCH("~*",B4,1),C4*RIGHT(B4,1),IF(SEARCH("/",B4,1),C4/RIGHT(B4,1),IF(OR(B4<0,B4>0),SUM(C4,B4))))
[/TD]
[TD]*5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=IF(SEARCH("~*",B5,1),C5*RIGHT(B5,1),IF(SEARCH("/",B5,1),C5/RIGHT(B5,1),IF(OR(B5<0,B5>0),SUM(C5,B5))))[/TD]
[TD]/5
[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
Thanks.
How can I make my existing formula work or what is a better method?
This is how I'd like the logic function, but it only works with the "*" operator:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=IF(SEARCH("~*",B2,1),C2*RIGHT(B2,1),IF(SEARCH("/",B2,1),C2/RIGHT(B2,1),IF(OR(B2<0,B2>0),SUM(C2,B2))))
[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=IF(SEARCH("~*",B3,1),C3*RIGHT(B3,1),IF(SEARCH("/",B3,1),C3/RIGHT(B3,1),IF(OR(B3<0,B3>0),SUM(C3,B3))))
[/TD]
[TD]-5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=IF(SEARCH("~*",B4,1),C4*RIGHT(B4,1),IF(SEARCH("/",B4,1),C4/RIGHT(B4,1),IF(OR(B4<0,B4>0),SUM(C4,B4))))
[/TD]
[TD]*5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=IF(SEARCH("~*",B5,1),C5*RIGHT(B5,1),IF(SEARCH("/",B5,1),C5/RIGHT(B5,1),IF(OR(B5<0,B5>0),SUM(C5,B5))))[/TD]
[TD]/5
[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
Thanks.