is this possible with a formula?

[nicolette]

hi, ok so I have made the following formula and everything works. So i decided that I wanted to also have the formula get the results of 2 cells and if the result is -1 or less post the opposite to a different cell.

Say, B1 contains 5.00 and B2 contains 7.00 (5.00 - 7.00) resulting in
-2.00 in cell B3 is there a way to put 2.00 is cell B4 and 0 in cell B3?

Here is my formula, in this formula it would be the results of J7 & P7 and I want the results in cell AB7. Cell AH7 contains the fromula

=IF(AB7>=0,(IF(J7>0,(IF(P7<>0,J7-P7,(IF(V7<>0,J7+V7,(IF((AND(P7=0,V7=0)),J7,0)))))),0)),0)

Now, Cell AB7 contains this formula
=IF(D7>0,(IF(P7<>0,D7+P7,(IF(V7<>0,D7-V7,(IF((AND(P7=0,V7=0)),D7,0)))))),0)

I'm thinking that because cell AB7 contains a formula my idea won't work but it never hurts to ask...

I hope I explained that in an understandable manner.
Nicolette

 

[nicolette]

ok so now I have to formulas all working and calculating as needed except I still have not been able to figure out if there is a way to transfer the negative result to a different cell...

Any one have any ideas? Here is my formula.

Positive cell Formula:
=IF(AND($D34>0,$P34>0),$D34+$P34,(IF(AND($J34=0,$P34=0,$V34=0),$D34,0)))+(IF(AND($D34>0,$V34>0),$D34-$V34,(IF(AND($D34=0,$J34=0),IF($P34>0,$P34)))))

Negative cell Formula:
=IF(AND($J34>0,$P34>0),$J34-$P34,(IF(AND($D34=0,$P34=0,$V34=0),$J34,0)))+(IF(AND($J34>0,$V34>0),$J34+$V34,(IF(AND($D34=0,$J34=0),IF($V34>0,$V34)))))

So basically if the calculations in the positive cell result in a negative I need that result to display in the negative cell and have the formula ignored. and vice versa.

 

[Dryver14]

it will be long but

IF(your formula)<0,"",(your formula)

or if (your formula)>0,"",(your formula)

as in

=IF(IF(AND($D34>0,$P34>0),$D34+$P34,(IF(AND($J34=0,$P34=0,$V34=0),$D34,0)))+(IF(AND($D34>0,$V34>0),$D34-$V34,(IF(AND($D34=0,$J34=0),IF($P34>0,$P34)))))<0,"",IF(AND($D34>0,$P34>0),$D34+$P34,(IF(AND($J34=0,$P34=0,$V34=0),$D34,0)))+(IF(AND($D34>0,$V34>0),$D34-$V34,(IF(AND($D34=0,$J34=0),IF($P34>0,$P34))))


I stand by the fact your formuls is overcomplicated though haha

 

[nicolette]

I stand by the fact your formuls is overcomplicated though haha

LOL I have no doubt about that but I tried and played with all suggestions I received from everyone and well that is what I ended up with to get the results I wanted I think the fact that it is essentially divided up into 4 section Positive negative positive negative and that I have to be able to check which of those have entries and do the calculations on that etc. It is the combinations that are possible that made the whole thing complicated and difficult... I will definetly try your suggestion and let you know how it works, my fingers are crossed cause I need the number to come out right so they can be used in the financial statement sheets to calculate capital, net profit or loss, expenses, cash flow, etc.

 

[Dryver14]

Good luck with it.... by the time its sorted you could have counted it on yer fingers................