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

 

[Ganjin]

welcome to the forum

now lets see, would the following be true:
if AH7 = -2
then AB7 becomes 2, and AH7 becomes 0?

incidently, you can simplify the formulas into:
Cell AB7:
=IF(D7>0,IF(P7<>0,D7+P7,IF(V7<>0,D7-V7,D7)),0)
Cell AH7:
=IF(AB7>=0,IF(J7>0,IF(P7<>0,J7-P7,IF(V7<>0,J7+V7,J7)),0),0)

 

[nicolette]

..... That seems too easy LOL. I'm attempting to make my life easier when it comes to my bookkeeping (accounting) for my business. Currently I do all accounting by hand cause well I have been in business too long and have way too much inventory to now implament an accounting software package quickly. and honestly I don't have the time to learn... So I decided I was going to put my financial statements and my worksheet into excel and let it do the time consuming part for me (calculating, some entries etc.) I got everything but the worksheet set up.

I don't know how familiar you are with accounting and the worksheet but at the risk of explaining what you already know, essentialy you do the posting calculate the balance and prepare a trial balance (I would enter this information) this is section 1. then you make adjusting entries (again I would enter this info). Now here I need to have it add or subtract depending on they type of adjustment and have it calculate the new balance hence the formula I posted, and my desire to have it change a negative number to a positive number and change the cell it is posted in. now if the trial balance section has a starting balance of 0.00 I need it to post the adjusting entry into the proper cell in the adjusted trial balance section.

My thought were (and I am very good at over thinking things) that I need the formula to check if the entry was 0 in the beginning and then determine if it enters the info or not and if there is no entry enter 0.

 

[Ganjin]

hmm, color me confused
could you post a sample?

truth be told, prior to the second post, my thinking was along these lines:

you had something like this originally:
<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>D7</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>J7</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>P7</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>V7</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>AB7</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>AH7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">7</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">20</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-2</TD></TR></TBODY></TABLE>
AB7:
=IF(D7>0,IF(P7<>0,D7+P7,IF(V7<>0,D7-V7,D7)),0)
AH7:
=IF(AB7>=0,IF(J7>0,IF(P7<>0,J7-P7,IF(V7<>0,J7+V7,J7)),0),0)

and now you want something like this:
<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>D7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>J7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>P7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>V7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>AB7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>AH7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">20</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR></TBODY></TABLE>
AB7:
=IF(IF(J7>0,IF(P7<>0,J7-P7,IF(V7<>0,J7+V7,J7)),0)<0,-IF(J7>0,IF(P7<>0,J7-P7,IF(V7<>0,J7+V7,J7)),0),IF(D7>0,IF(P7<>0,D7+P7,IF(V7<>0,D7-V7,D7)),0))
AH7:
=IF(IF(J7>0,IF(P7<>0,J7-P7,IF(V7<>0,J7+V7,J7)),0)<0,0,IF(J7>0,IF(P7<>0,J7-P7,IF(V7<>0,J7+V7,J7)),0))

 

[Dryver14]

is this what you re looking for?

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" 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></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</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>C1</TD><TD>=IF(A1>B1,A1-B1,0)</TD></TR><TR><TD>D1</TD><TD>=IF(B1>A1,B1-A1,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[nicolette]

I'm so sorry confusion is not my goal, although for this project it seems to be my fate. LOL

I believe so I have a hard time "reading" formulas

I uploaded it to googledocs maybe that will help. https://spreadsheets.google.com/ccc...E1PUGFHblRoOVZjOFZZRXc&hl=en&authkey=CMn6iIoB

and I'm sure my formula is all wrong and has things that are not needed.

 

[nicolette]

is this what you re looking for?

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" 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></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</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>C1</TD><TD>=IF(A1>B1,A1-B1,0)</TD></TR><TR><TD>D1</TD><TD>=IF(B1>A1,B1-A1,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

sort of I need to to also do calculations too, so just comparing isn't quite enough I don't think

 

[Dryver14]

the above is doing calculations but reversing the sum to give positive numbers, I dont quite understand your spreadsheet as you seem to be doing evreything twice well certainly some of it

 

[nicolette]

oh ok, I'm learning as I go I Know I am and some of it I think is just me getting lost in the formula

 

[nicolette]

Ok so playing around I came up with

=IF(D33=0,(IF(D33+P33>0,D33+P33,(IF(D33-V33>0,D33-V33,(IF(J33-P33>0,J33-P33,(IF(J33+V33>0,J33+V33,(IF(AB33=0,(IF(P33>0,P33,(IF(V33>0,V33,0)))))))))))))),0)

and i'm still testing but this seems to do part of what I need/want.

OK Scratch That... It isn't giving 0 if there is already an entry in that section. the bolded section is what seems to be not working

So I played with this

=IF(AB32>=0,(IF(P32>0,P32,0)*OR((IF(V32>0,V32,0)))),0)

Now that seems to work so I think I just have to put it together... I hope!