So I am trying to write a formula for a bigger project I am working on, and I am running into a problem. I want to have this formula subtract dues from a member's current credit balance. But my IF Statement is giving me a false result. Any help is greatly appreciated!
m9SReJF.png
Here is my formula:Formula:
Please Login or Register to view this content.
You dont tell your formula what to do if A2 is not less than B8, in the example A2 = 91 and B8 = 90
you actually need to add two bits to your formula as neither of the IFs have a false option.
Your IFs should be
if(A2<B8,"what to do if true, "What to do if false")
so yours needs the XX below replacing with formula/values
Please Login or Register to view this content.
If someone has helped you then please add to their Reputation
Formula:Please Login or Register to view this content.
After revising my post like the above, I am still getting False Result. To clarify, In my example, D2 should reflect the balance shown in A2, BUT, ONLY up to the value of B8. So if A2 shows $150, I would like for D2 to show a value of $90 and E2 to show a value of $60 which is the total credit balance available to the member.
Something like
=MAX(0,MIN(A2,B8))
From the little information we have this is just a guess.
Please post a sample workbook, not a screen capture, they get blocked by most browsers.
Excel Test Sheet.xlsm
Here is a Test Sheet Attached.
Thanks!
I'm not sure that I follow what you need, reading your question and post #3 again, it looks like you're asking for the same thing as in your 'membership dues formula' thread.
The result from my 'membership dues formula' thread is not working as I need. And this thread is me trying to fix it. Maybe I should have reopened that thread but it was already marked as solved so I opted to make this thread instead. But in the Test Sheet attached here I need to distribute the Credit Balance shown in A2 across D2:O2 in $90 increments. However keep in mind that the credit balance is always increasing as members make payments adding to their credit balance. The Bonus Pkg is another problem I am opting to not look at until I get the Dues working correctly.
Here is JeteMc's Solution which I thought at first would work. But then when I put the credit balance to $0 it still shows the member to have paid the first month's $90 dues.
Excel Test Sheet 2.xlsm
Thanks!
Last edited by unit285; 11-13-2015 at 02:27 PM.
For future reference, you should have reopened the old thread, you should be able to edit the title again and remove the [Solved] tag, but I'm not sure if there is a time limit on how long that option is available.
I'm going to ask for a moderator to review your threads and merge them if they think appropriate. Normally I would flag it as a duplicate and ask for it to be locked, but I think that the alternative is more suited here.
Back to your question, try this one in D2, then drag it along the row as needed.
=MIN($B$8,$A2-SUM($C2:C2))
Ok Thank You! Your formula for Dues now works as I was hoping! I appreciate your patience, I am a new member of this forum and will learn from my mistake of not reopening the other thread. I would appreciate it if a moderator could merge the two threads because I will have more questions about this project I am working on.
You're welcome!
I have submitted a request for a moderator to merge the 2 threads if they think it necessary.
Meanwhile, onto the next part of the challenge, the Bonus pkg.
I will work on this a bit and try to post a clear example of what I need for the Bonus Pkgs by tonight if I can.
Thanks again for your help Jason.b75!
Now that we have the dues being distributed across the months.. I will try to explain the next challenge, Basically Members can choose to add Bonus Pkgs to their membership, Bonus Pkgs cost $500 each. Each member has 12 months to pay for both dues and any bonus pkgs they choose to add. Members will have each payment they make recorded in a line with the corresponding date in the line above it. I need these payments to prioritize paying Bonuses if the payment is $500 or larger if the payment is less then $500 then it prioritize paying dues. if Henry has a "Dues" Balance of $1,080.00 for the year and he bought 2 Bonus Pkgs so his "Bonus" Balance for the year is $1,000.00
Henry makes a payment on 11/5/2015 for $250.00 and it should all apply towards Dues. on 11/13/15 Henry makes another payment of $650.00 and now since this payment is more then $500.00 I need for the first $500 to apply towards Bonuses and the remaining $150 to go towards Dues.
James also has $1,080.00 in dues for the year and makes the same payments Henry did, however since James did not purchase any Bonus Pkgs all his payments should apply to his dues.
Loren also has $1,080.00 in dues for the year and he bought 2 Bonus Pkgs, But Loren makes payments that are less then $500.00 each so his payments FIRST go to paying his dues for the entire year and then the remainder goes towards paying for his bonuses.
I hope I have not confused anyone too much. Any help is greatly appreciated. I have attached a test sheet below
Excel Test Sheet 3.xlsm
That makes sense, but just to clarify a couple of details.
I don't see anything in the sample to show how many bonus packages each member has added, so that needs to be fitted in somewhere.
Does the rule of prioritising bonus packages still apply if dues are effectively in arrears.
For example, 2 bonus packages added, payments of $500 in Jan and Feb, would effectively mean that the bonus packages are paid for but dues are 2 months behind? Is this how it should be applied, or should payment dates be considered as well?
Can the layouts be changed if needed?
edit:-
2 bonus packages added, payment of $1100, should this be 2 bonus pkgs & $100 dues, or 1 bonus pkg & $600 dues?
If it should be 2 bonus pkgs & $100 dues, should the bonus pkgs show 1 x $1000 in the top table, or 2 x $500 ?
Based on your post, without taking anything I've mentioned above into consideration.
In R3 enter the number of bonus pkgs to be added.
In A3 =SUM($C$11*R3)-SUM(E3:P3)
In E2 (and copied right, replacing the earlier Dues formula) =MIN($C$10,$B2-SUM($D2:D2,$E3:E3))
In E3 (and copied right) =MAX(0,MIN((MIN(COUNTIF($B14:$M14,">=500"),$R3)*500)-SUM($D3:D3),500),MIN((SUM($B14:$M14)-($C$10*12))-SUM($D3:D3),500))
I'm sure that last formula can be made shorter, but for now that is the best I've found that appears to return the correct results.
Last edited by jason.b75; 11-14-2015 at 09:09 AM.
Column A Row 3, 5, & 7 show the balances owed for any Bonus Pkgs the member may have purchased. The prioritizing rule applies even if Dues are behind because the member has a full 12 months to pay. some members may not pay anything until the last month where they will make 1 lump sum payment to pay for everything at once. Others will make several payments over the course of 12 months, and some will pay their dues month by month. In any of these situations, the rule stays the same. Payments over $500 should go towards Bonuses first if the member owes for them, otherwise go towards dues, payments under $500 should go towards dues first, and then towards bonuses.
After trying your formulas;
I will test your formulas out further but atm they appear to work like pure MAGIC. I love it! I'll let you know if I find anything that does not work.
Thanks a lot!
I have applied your formulas to my project and can say I am 100% pleased with the results! Thank you so much! You are a formula wizard!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks