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

 

[Dryver14]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD><TD>AC</TD><TD>AD</TD><TD>AE</TD><TD>AF</TD><TD>AG</TD><TD>AH</TD><TD>AI</TD><TD>AJ</TD><TD>AK</TD><TD>AL</TD><TD>AM</TD></TR><TR style="HEIGHT: 38px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 11pt" colSpan=2> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">Account Title</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=12>Trial Balance</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=12>Adjustments</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=12>Adjusted Trial Balance</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=2>Date</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt" colSpan=12> </TD><TD style="FONT-SIZE: 11pt" colSpan=12> </TD><TD style="FONT-SIZE: 11pt" colSpan=12> </TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Debit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Credit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Debit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Credit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Debit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Credit</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Cash</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$0.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$5.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$7.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$0.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$0.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$2.00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AB7</TD><TD>=IF(P7>=J7,D7,0)</TD></TR><TR><TD>AH7</TD><TD>=IF(P7>J7,P7-J7,J7)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
this base formula seems to work AB7 and AH7 perhaps you are over thinking slightly

 

[nicolette]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD><TD>AC</TD><TD>AD</TD><TD>AE</TD><TD>AF</TD><TD>AG</TD><TD>AH</TD><TD>AI</TD><TD>AJ</TD><TD>AK</TD><TD>AL</TD><TD>AM</TD></TR><TR style="HEIGHT: 38px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 11pt" colSpan=2></TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">Account Title</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=12>Trial Balance</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=12>Adjustments</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=12>Adjusted Trial Balance</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=2>Date</TD><TD style="FONT-SIZE: 11pt"></TD><TD style="FONT-SIZE: 11pt" colSpan=12></TD><TD style="FONT-SIZE: 11pt" colSpan=12></TD><TD style="FONT-SIZE: 11pt" colSpan=12></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 11pt"></TD><TD style="FONT-SIZE: 11pt"></TD><TD style="FONT-SIZE: 11pt"></TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Debit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Credit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Debit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Credit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Debit</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>Credit</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 11pt"></TD><TD style="FONT-SIZE: 11pt"></TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Cash</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$0.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$5.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$7.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$0.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$0.00</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt" colSpan=6>$2.00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AB7</TD><TD>=IF(P7>=J7,D7,0)</TD></TR><TR><TD>AH7</TD><TD>=IF(P7>J7,P7-J7,J7)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

ok now assuming I am reading the formulas correctly you are saying if Adjustments Debits (P7) is greater than or equal to Trial Balance Credits (J7) output Trial Balance Credits or 0 right?

if so that isn't right because if the Trial Balance Credits has an entry regardless of its size it must be Calculated on. If Adjustments Debit has an entry that would be added to Trial Balance Debits entry or if Adjustments Credits has an entry that would be subtracted from Trial Balance Credits

If Trial Balance Debits has an entry then Adjustments Debit woul be added and Adjustments Credits would be subtracted

Trial Balance Debits is a positive account balance while the credits are a negative account balance (yes I know it is backwards, but that is accounting for ya!)

Both Credits and Debits in the Trial Balance, Adjusted Trial Balance, Income Statement, and Balance sheet, can't have entries, Just one entry in either Credits or Debits so I need to make sure that when the calculations are done in the Adjusted Trial Balance Credits or Debits it verifyies there isn't already an entry.

I hope that helps and I hope I read the formula right.

Nicolette

 

[Dryver14]

Nicolette,


I copied this formula down columns AB7 and AH7 gave the results you have on your spreadsheet so I throw my hands up in surrender....You win.

Can someone brainy jump on this for me please

 

[nicolette]

ok now assuming I am reading the formulas correctly you are saying if Adjustments Debits (P7) is greater than or equal to Trial Balance Credits (J7) output Trial Balance Credits or 0 right?

if so than it is sort of what I need. It would need to output 2.00 (negative) to AH7 and enter 0 in AB7

Andif Adjustment Debits (P7) is greater than Trial Balance Credits, the subtract Trial Balance Credits (J7) from Adjustments Debits (P7) otherwise output (J7)

if so then i'm confused, It would need to output 2.00 (positive) to AB7 and enter 0 in AH7.


if the Trial Balance Credits has an entry regardless of its size it must be Calculated on. If Adjustments Debit has an entry that would be added to Trial Balance Debits entry or if Adjustments Credits has an entry that would be subtracted from Trial Balance Credits

If Trial Balance Debits has an entry then Adjustments Debit woul be added and Adjustments Credits would be subtracted

Trial Balance Debits is a positive account balance while the credits are a negative account balance (yes I know it is backwards, but that is accounting for ya!)

Both Credits and Debits in the Trial Balance, Adjusted Trial Balance, Income Statement, and Balance sheet, can't have entries, Just one entry in either Credits or Debits so I need to make sure that when the calculations are done in the Adjusted Trial Balance Credits or Debits it verifyies there isn't already an entry.

___Debit_______|____Credit________
|
Positive Bal. Negative Bal.
Add Subtract

I hope that helps and I hope I read the formula right.

Nicolette

 

[nicolette]

some of my entries are not correct....

 

[Dryver14]

in essence the calculations are very simple, that is the point I am trying to get accross, one number is going to be bigger than the other and either way you need to deduct the smaller number from the larger number which is what my formula does

so if credit is 5 and debit 3 that leaves 2 in credit if credit is 3 and debit is 5 that leaves 2 in the debit column aside from that I really do not fully understand what more you want I have reversed some of the numbers as you seemed to want a positive return regardless with just the columns for the return value changing.

I'm sorry I am not getting it, perhaps someone else might.

Regards,
Kev

 

[nicolette]

I'm home now to put your suggestions in and see what happens on mine, I'm sorry i'm not trying to be difficult, I hope you are right, and that it is that simple... i'll let you know!

 

[nicolette]

ok so I just copied and pasted your formulas in to my spreadsheet and it isn't quite right. When D7 is 0, J7 is 0 P7 has an entry and V7 is 0 the entry from P7 needs to be in AB7 but instead it is in AH7.... but I think with some tweeking it could be as simple as you say.... thanks for your help!

 

[nicolette]

I ended up with the following formula and so far it seems to do almost everything I need it to, it is probably the long way around but... it seems to be working, Now I just have to see if there is a way to make sure both sections don't have an entry. Thanks for your help everyone it helped me out alot!

=IF(AND($D7>0,$P7>0),$D7+$P7,(IF(AND($J7=0,$P7=0,$V7=0),$D7,0)))+(IF(AND($D7>0,$V7>0),$D7-$V7,(IF(AND($D7=0,$P7=0,$V7=0),$V7,0))))

 

[nicolette]

oh wow it looks like I got it right away here is the updated formula...

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