OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] New formula help for a personal budget

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
DriverJim
General User
General User


Joined: 23 Jun 2012
Posts: 10

PostPosted: Sun Jul 01, 2012 10:26 pm    Post subject: [SOLVED] New formula help for a personal budget Reply with quote

I have this formula:

=SUMPRODUCT( $B$23:$B$215="cash" ; $G$23:$G$215-$F$23:$F$215)

It works aight for some of what I want to do, but would like to expand it's functions. First, I'd like the result to NOT show a negative number -meaning, stopping at zero. Second, I'd like the instance of Cash to be EXCLUDED from all other transaction types (C16).

I thank you in advance.


Last edited by DriverJim on Thu Jul 05, 2012 11:00 pm; edited 2 times in total
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sun Jul 01, 2012 10:53 pm    Post subject: Reply with quote

Hallo

Quote:
First, I'd like the result to NOT show a negative number -meaning, stopping at zero.


Code:
=SUMPRODUCT($B$23:$B$215="cash";$G$23:$G$215-$F$23:$F$215;$G$23:$G$215>$F$23:$F$215)


Quote:
Second, I'd like the instance of Cash to be EXCLUDED from all other transaction types (C16)


??
Eventually you want:
...$B$23:$B$215<>"cash"...

Karo
Back to top
View user's profile Send private message
DriverJim
General User
General User


Joined: 23 Jun 2012
Posts: 10

PostPosted: Sun Jul 01, 2012 11:03 pm    Post subject: Reply with quote

karolus wrote:
Hallo

Quote:
First, I'd like the result to NOT show a negative number -meaning, stopping at zero.


Code:
=SUMPRODUCT($B$23:$B$215="cash";$G$23:$G$215-$F$23:$F$215;$G$23:$G$215>$F$23:$F$215)


Quote:
Second, I'd like the instance of Cash to be EXCLUDED from all other transaction types (C16)


??
Eventually you want:
...$B$23:$B$215<>"cash"...

Karo


Right, I see where I goofed. Here's the formula for the second bit.

=H6+H7-H9-SUM(L23:L215)

How would I add that in?
Back to top
View user's profile Send private message
DriverJim
General User
General User


Joined: 23 Jun 2012
Posts: 10

PostPosted: Mon Jul 02, 2012 1:03 am    Post subject: Reply with quote

DriverJim wrote:


Right, I see where I goofed. Here's the formula for the second bit.

=H6+H7-H9-SUM(L23:L215)

How would I add that in?


All right, I got something to work. Now what I want to do, rather than have numbers showing is have the result field be blank until an input goes into one of the fields. So far I have

Code:
=SUMIF($B23;"cash";$F23)-SUMIF($B23;"cash";$G23)-SUMIF($D23;"cash payment";$H23)+($C$14)+($I22)


and would like to add something that looks like

Code:
=IF(AND(ISBLANK(F23);ISBLANK(G23));"";OFFSET(J23;-1;0;1;1)-F23+G23)


Yes, I know I'm getting into some really complex formulas but . . . well, it looks better than having $0.00 repeated a couple hundred times.
Back to top
View user's profile Send private message
DriverJim
General User
General User


Joined: 23 Jun 2012
Posts: 10

PostPosted: Thu Jul 05, 2012 11:09 pm    Post subject: Reply with quote

For those that need similar coding:

Code:

=IF(AND(ISBLANK(F24);ISBLANK(G24);ISBLANK(H24));"";OFFSET(I24;-1;0;1;1))+SUMIF($B24;"cash";$F24)-SUMIF($B24;"cash";$G24)-SUMIF($D24;"cash payment";$H24)


Code:

=IF(AND(ISBLANK(F25);ISBLANK(G25);ISBLANK(H25));"";OFFSET(J25;-1;0;1;1)-F25+G25)


Code:

=IF(AND(ISBLANK(F24);ISBLANK(G24);ISBLANK(H24));"";OFFSET(M24;-1;0;1;1)-F24+G24-N24)


The above formulas will leave the target cells blank until an entry is made into F. G or H then will display the sum total in I, J or M. There is a similar code in N, thus the -N## in the third string. While intended for personal budgets, if you need any of the strings whichever target (OFFSET) cell you use make certain to change that locator (ie I24) and the ISBLANK identifiers.

The strings are unlocked, meaning a single copy can be duplicated going down the rows until the end of the sheet in question without having to change the formula row-by-row.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group