[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
DriverJim
General User

Joined: 23 Jun 2012
Posts: 10

 Posted: Sun Jul 01, 2012 10:26 pm    Post subject: [SOLVED] New formula help for a personal budget 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
karolus
OOo Advocate

Joined: 22 Jun 2011
Posts: 210

Posted: Sun Jul 01, 2012 10:53 pm    Post subject:

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
DriverJim
General User

Joined: 23 Jun 2012
Posts: 10

Posted: Sun Jul 01, 2012 11:03 pm    Post subject:

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?
DriverJim
General User

Joined: 23 Jun 2012
Posts: 10

Posted: Mon Jul 02, 2012 1:03 am    Post subject:

 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.
DriverJim
General User

Joined: 23 Jun 2012
Posts: 10

Posted: Thu Jul 05, 2012 11:09 pm    Post subject:

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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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