I have a formula-heavy Excel project that I am working on, but I would like to refrain from using Macros/VBA for as long as I can. The sheet incorporates two distinct quantities; for simplicity's sake, a "Checking" account and a "Savings" account. The Checking account has $1,000 in it, while the Savings account has $2,000. Purchases deplete the Checking account first, and purchases made with the Checking account alone are supposed to come with raffle tickets. If the Checking account cannot cover a purchase by itself, the remainder can be deducted from the Savings account, but no raffle tickets would be given. Any purchases involving Savings account do not come with raffle tickets. My formula might look something like this in JavaScript:
The problem arises when I try to translate this to Excel. My sheet employs two flags: one functional flag to track that the item has been purchased (B1 below), and an unimplemented flag that tracks whether the purchase comes with raffle tickets. One formula I tried for the second flag was:
As there are many simultaneous purchases that the user can make, SUM(B:B) can get very large and the formula eventually turns all 1s to 0s. Simply using =IF(B1<=1000,1,0) leads to false positives.
Ultimately, I want Excel to be able to tell "when" purchases are made so it knows A) what account(s) to deduct the cost from and B) when to grant raffle tickets. I am not sure how, or even if, I can proceed solely with Excel's provided formulas. While I am relatively inexperienced when it comes to computers, I have a decent grasp on the basics and if statements. Any assistance or feedback would be greatly appreciated.
Bookmarks