Nesting conditional IF formulas (title improvement suggestions welcome)

[Coral-Reefer]

Please see the attached spreadsheet

This is a commissions calculation spreadsheet for my salespeople. I am trying to create a formula that will return the correct draw balance and one that will accurately reflect the "Commissions Due To Salesperson". For those not familiar with draw balances, we pay a salesperson a monthly draw that is periodically reconciled against the commissions they earn.

My "Draw Account Balance" (P11) works fine for me unless the account balance drops below zero, then it shows a negative balance. As an example, this can be seen by changing G10 to 300,000. When the this occurs I need the Draw Account Balance" (P11) to show zero, and all of the "Monthly Commissions" (M11) to be paid. My issue can be be seen Red formulas are ones I currently have in the worksheet.

For cell P11... =IF(M11<=1500, P10+B11,((M11-1500)*-1)+P10) unless P11 is <= 0 then P11=0

Also for cell P11.. if P10 < 10000, add P10 and B11.... My goal for this formula is to include any draws paid in column B to be added to the "Draw Account Balance"

Also, for cell Q11... =IF(M11<=1500,M11,1500) unless P11=0, then Q11=M11

I hope this is not to confusing and I will be more than happy to answer any questions and make any clarifications.

Thank you all very much for your help.

Chris Bertolini
High County Communications
Linville, NC
828-733-1822

[judgeh59]

=IF(M4<=1500, P3+B4,IF(((M4-1500)*-1)+P3<0,0,((M4-1500)*-1)+P3))

paste this into P4 and copy it down the column....this seems to fix the "going negative" problem....I not quite sure about the being over 10000....I'll keep noodling that one....HTH

[Coral-Reefer]

Yes, that took care of that part of it. Thank you!

Chris

[Coral-Reefer]

During my attempts to solve it on my own I performed preliminary calculations within the P column and worked from the new column and then hid the P column I used for the calculation. Sounds like cheating to me but it was all I knew how to do!

Chris

[judgeh59]

hiding columns isn't a bad thing....especially if you are the only ones who knows....on the second formula you have if column P < 10000 add columns B and P but in the previous formula have B and P being added if M<=1500....are these related?....something like....if P<10000 OR M<=1500 then add B and P?

[FDibbins]

Hi and welcome to the forum

We would love to help you with your question, but 1st, in accordance with forum rules, please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertise, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

[judgeh59]

@FDibbins - I was just writing a note to that effect....Is that proper for me to do that if I'm polite and state I'm not a moderator just a concerned contributor?

[FDibbins]

yes, its ok to suggest a thread title change, judge

[Coral-Reefer]

Thanks for the suggestions on the thread title. I appreciate your politeness (I wish that was more evident on other forums). I am pretty thick skinned and appreciate constructive criticism and input. I was not sure how to state my need without having a drawn out thread title. I will work on it.

Chris

[Coral-Reefer]

hiding columns isn't a bad thing....especially if you are the only ones who knows....on the second formula you have if column P < 10000 add columns B and P but in the previous formula have B and P being added if M<=1500....are these related?....something like....if P<10000 OR M<=1500 then add B and P?

Hmmm, yes, I suppose that "...if P<10000 OR M<=1500 then add B and P..." would be a correct assumption...

I can't think of a good thread title other than "nested and conditional IF formulas" which seems almost as vague as my original title, but it is a step in the right direction.

I am using this as a learning experience as I am trying to change, no, I AM changing my role as CEO from a technically task burdened one to a more strategic one and excel needs to be my good friend as I move forward! Plus, I love the logic of building the formulas!

Thanks again! You all are a godsend!

Chris

[judgeh59]

=IF(OR(M4<=1500,P3<10000), P3+B4,IF(((M4-1500)*-1)+P3<0,0,((M4-1500)*-1)+P3))

See if that helps your P<10000 issue....so, in english what that says is....of M4 is <= 1500 OR P3 is < 10000 then add P3 to B4 otherwise do the other stuff....

[judgeh59]

cell Q11... =IF(M11<=1500,M11,1500) unless P11=0, then Q11=M11

is this another OR.....if M11<=1500 OR P11=0 then Q11 = M11 otherwise it equals 1500?

[Coral-Reefer]

OK. That seems to add the B column as required but now when the M column >1500 it no longer reduces the P column by the amount over 1500 as it did with the original formula of

=IF(M11<=1500, P10+B11,IF(((M11-1500)*-1)+P10<0,0,((M11-1500)*-1)+P10))

[judgeh59]

so the formula with the OR Statement....did you put that in Cell P4 and the copy it down?....

[Coral-Reefer]

so the formula with the OR Statement....did you put that in Cell P4 and the copy it down?....

Yes, and that works, thank you so much. However, when I put the formula stated for Q11 which is to add the amount >1500 in M11 to Q11, I get the dialog box stating, "The formula you typed contains an error".

=IF(M11<=1500,M11,1500) unless P11=0, then Q11=M11

[judgeh59]

See post #12....I haven't done anything yet as I was waiting for a response from you on post #12.....glad to help when I can

[Coral-Reefer]

In response to Post #12...

cell Q11... =IF(M11<=1500,M11,1500) unless P11=0, then Q11=M11

is this another OR.....if M11<=1500 OR P11=0 then Q11 = M11 otherwise it equals 1500?

The description on the second line is accurate, however the formula on the first line returns a dialog box stating ""The formula you typed contains an error".

Thank you for helping.

[judgeh59]

=IF(OR(M3<=1500,P3=0),M3,1500)

paste this into Q3 and then copy down....

[Coral-Reefer]

=IF(OR(M3<=1500,P3=0),M3,1500)

paste this into Q3 and then copy down....

Done. Please see the attached sheet with all recommended formulas included.

Notice P11 accurately equals 12,941.12

Enter 180,000 into I11 and notice that P11 properly drops to 41.12 by 12,900 which is M11-1500, and Q11 properly displays 1500.

Enter 200,000 into I11 and notice that P11 drops to zero as it should, but Q11 still displays 1500.

Q11 needs to show the remaining amount from M11 after P11 reaches 0, which in the case of I11 = 200,000 should, I believe, be 19,486.12

Did I explain this adequately?

Thank you.

[judgeh59]

so to paraphrase....for Q11 if I put 200,000 into I11 and P11 goes to zero you want the value of M11 to be put into Q11....because that would be 16,000....

[Coral-Reefer]

I am not at my machine right now but, I am pretty sure that when 200,000 is put into I11, Q11 needs to not be the value of M11 but the difference between M11 and P11, in other words, what is left of M11 after paying P11 to 0. I hope I explained that correctly.....

[judgeh59]

I'm starting to understand ... sorry being slow....try this file out....if this isn't correct...let me know what you think the value should be and I'll reverse engineer that....

[Coral-Reefer]

Thanks to everyone with helping on this, especially judgeh59. I have more work to do on it and will get back to it shortly.

Chris Bertolini