[Solved] Add or subtract values in additional column

[hbombgolf]

Can't get the formulas correct when I add or subtract cash shown in column I

Gold Creek 2.ods

(15.59 KiB) Downloaded 67 times

Edit: Changed subject, was formula problem  Make your post understandable by others  -- MrProgrammer, forum moderator

[hbombgolf]

The correct ans. for J14, J15, J16 and J17 should be -$2.00
Something is wrong with formulas in H14 thru H17
H3 thru H13 works fine. When I add $5.00 in I14 the next row is incorrect

[Alex1]

The formulas in H14:H17 are different from the ones in H3:H13. Is that on purpose?

[FJCC]

@hbombgolf - Please explain why the result in J15 should be -2.00. You are calculating 71.06 - 68.06 and getting 3.00, which is correct.

[hbombgolf]

The values in C13 thru C17 are all the same, so the Win/ Loss Column for C14 thru C17 should be -$2.00
The same goes for D,E and F 13 thru 17
The only change is in Column G ,each row is reduced by $2.00
The value in I14 is causing the problem, I believe
I did not win or lose the $5.00 amount in Col. I, I did lose $2.00
The amount in Col. I $5.00, I just added or removed it from the pot
I14 +G13=$8.00
G14 =$8.00-$2.00=$6.00

[FJCC]

I14 +G13=$8.00
G14 =$8.00-$2.00=$6.00

It is not clear to me where the $2.00 in

Code: Select all

G14 = $8.00 - $2.00 = $6.00

comes from.

[hbombgolf]

I have a Tubberware dish containing quarters, amounting to the amount in F3 thru F17.
I also have dollar bills as shown in G3 thru G17
Each row starting with E3 is a poker match
All is fine with the correct answers in column J3 to J13
In I14 I want to add a $5.00 dollar bill to the $3.00 bills in G14 for a total of $8.00
The next poker match cost $2.00 dollars which I subtract from the $8.00 witch is G14 or $6.00

[FJCC]

I think I'm getting closer to understanding your file. I'm now confused by the last row. In row 16, the value in column F is 67.06 and in G it is 2.00. In row 17, F is still 67.06 and G is 0.00, so you lost $2.00. but cell I17 shows -$5.00. Where did those $5.00 come from? Did you actually win $3.00, raising your total Bills to 5, and then pull out the $5.00, leaving you with zero bills? In that case, the result of $3.00 in column J would be correct. If that isn't what happened, please explain how column F didn't change AND you lost the $2.00 in G16 AND you pulled $5.00 from your amount.

[hbombgolf]

J14 thru J17 should all be -$2.00 I lose $2.00 in each match.
The amounts in column I Plus 5 or -5 has nothing to do with have much I won or loss
Column G is reduced by $2.00 for each match.
If I want to increase the dollar bills because G13 is getting down close to 0 dollars I would add dollars I14
If I were to win several dollars I would take dollars out as in I17

[hbombgolf]

If I should win $8.00 dollar bills I would add that amount to G17 which would be G18 $8.00 plus $00.

[hbombgolf]

I'm in Florida, hurricane on the way. I may lose power and the internet. I you don't hear from me for a few days I'll be back.

[FJCC]

The attached file has the modified formulas in H14:H17 and J14:J17. Column G shows the total of your bills after your Win/Loss for that row and after you have added or subtracted bills via column I. J15 now correctly shows -2.00 after adding 5.00 in I14. I removed the -5.00 from I17 since it isn't possible to remove $5.00 while having F17 unchanged and losing the $2.00 you had in G16.

Notice I removed the SUM() function you had in H14:H17 and J14:J17. It is not needed.

[hbombgolf]

Looking great so far. Now see attachment when I added $10.00 and removed $5.00
added $10.00 at G17
Removed $5.00 at I18

[FJCC]

Two things:
The formula in J18 is wrong. It should be

Code: Select all

=H18-H17-I18

You have an = where there should be a -.

Given the way I set up the calculation, the value in G18 should be $3.00. In G17 you have $10.00. In the next row, you lose $2.00 and you take out -$5.00. That reduces G18 to $3.00. J18 will then show -$2.00.

[hbombgolf]

In G17 I have $10.00. As with all the G cells above, each one is reduced by $2.00
I believe my loss in J18 should be -$2.00
H18-H17=$2.00

[FJCC]

I believe my loss in J18 should be -$2.00
H18-H17=$2.00

But J18 is not H18-H17. It is =H18-H17-I18. To account for the $5.00 you pulled out from you poker money, which is totaled in H18, you have to reduce G18 by five, so it shows $3.00, and account for that reduction by using the formula =H18-H17-I18 in J18.
After you pull out the $5.00, you do not have $8.00 in bills for poker, you have $3.00. That is how I set up the calculation.

[hbombgolf]

You are absolutely correct, and I want to thank you for all your help.
This spreadsheet actually goes back 2 years. With your expertise I was able to correct it from day one.
Thank you

[hbombgolf]

Problem Solved