| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| 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 |
|
| Back to top |
|
|
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? |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
|
