Solved

Posted on 2011-04-20

Hello all,

I need you help on a formula.

If you look at my excel sheet, in Range("I10"), i have the total money available.

In column E, i have the Gift card value and In column G, this is where i will put Yes, every time that i give a card.

Finally, in Column I is the Money available left after giving a card.

I need to deduct from the Money available Range("I10") every time that i put yes in column G, the value from the same lane in column E.

How can i do this please?

Thanks for your help.

Money-left.xlsx

I need you help on a formula.

If you look at my excel sheet, in Range("I10"), i have the total money available.

In column E, i have the Gift card value and In column G, this is where i will put Yes, every time that i give a card.

Finally, in Column I is the Money available left after giving a card.

I need to deduct from the Money available Range("I10") every time that i put yes in column G, the value from the same lane in column E.

How can i do this please?

Thanks for your help.

Money-left.xlsx

10 Comments

If i add another yes in G13, then in I13, i will have the Money available of 500 less the first value from E12 - the second yes with value from E13. So the result in I13 would be 390.00.

Does it make sense?

=IF(G12="","",$I$10-SUMIF(

Basically, the code you want is =IF(G12="yes",I11-E12,"")

This looks at G12 to see if you typed "yes" in there, if you typed yes, then it will subtract E12 from I11 (which is $500-50) and you get $450. If you put ANY other text besides "yes" then the cell would just stay blank because the text does not meet the IF requirements. That is what the "" means after the last comma.

Check out the sheet and see how it works for you. Every time you put yes in the next row, it subtracts your amount from the total "money left" column.

You can change the formula to take the whole gift column out if you want to make it easier. I added a second sheet to show.

In the simplified version. You simply add how much the gift card was, and the money left column reflects that card. So you only add money when you give the card away, you don't have to say "yes" every time. Whichever works better for you is fine though.

Copy-of-Money-left.xlsx

Copy-of-Money-left--Simplified-.xlsx

=500-SUMPRODUCT(E12:E18,G1

2. You can also put the results into a rolling balance in column I. I noticed you're using 1 instead of Yes, so my formulas work with that.

See attached for the second example:

Dave

Money-left.xlsx

Also you refer to 'yes', but column G has 1s in it.

1. 1. You can put this in Range("I10"):

=500-SUMPRODUCT((E12:E18)*

2. see attached using "Yes" for forumlas as rolling balances:

Money-left-r2.xlsx

By clicking you are agreeing to Experts Exchange's Terms of Use.

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!