Nested IF / THEN / SUM Formulas - Based on Values in Drop Down List

Hello!

I am working on a Commission log and tracking project. The commission amount is based on the amount billed which is contingent on the contract billing cycle rather than the total cost of the sale.

So, I am trying to create a formula that will calculate the billing amount based on the bill cycles from a drop down list, so each value in the list will either return the Service Cost amount if its a Single Service or Annually but divide the Service Cost by 12 for Monthly, by 4 for Quarterly or by 6 for Bi-Annually.

Here are my columns for reference...

If I were writing out the the formula to do this in the Cycle Bill Cost column, it'd basically be this:

If Single Service or Annually is selected in Billing Cycle then Cycle Bill Cost will equal service cost. If monthly is selected in Billing Cycle then divide Service Cost by 12. If quarterly is selected in Billing Cycle then divide Service Cost by 4. If Bi-Annually is selected in Billing Cycle then divide Service Cost by 6. Which would give me the cost that Cycle Bill Cost would equal.

Hopefully that makes sense. This the first time I am attempt to do a complex formula and I'm failing really hard at it. Any help would be greatly appreciated.

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey

    Sorry - I answered from my phone before and couldn't fix the formula

    IF(ISNUMBER([Service Cost]@row), IF(OR([Billing Cycle]@row="Single Service", [Billing Cycle]@row="Annually"), [Service Cost]@row, IF([Billing Cycle]@row="Monthly", [Service Cost]@row/12, IF([Billing Cycle]@row="Quarterly", [Service Cost]@row/4, IF([Billing Cycle]@row="Bi-Annually", [Service Cost]@row/2)))))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Amber M.

    Try this

    =IF(ISNUMBER([Service Cost]@row, IF(OR([Billing Cycle]@row="Single Service", [Billing Cycle]@row="Annually"), [Service Cost]@row, IF([Billing Cycle]@row="Monthly", [Service Cost]@row/12, IF([Billing Cycle]@row="Quarterly", [Service Cost]@row/4, IF([Billing Cycle]@row="Bi-Annually", [Service Cost]@row/2)))))

    I added the 'ISNUMBER' at the beginning to try to prevent formula errors if a non-number ended up in the column. Also, you defined Bi-annually as dividing by six. Isn't it two? If my interpretation is incorrect then change my 2 in the formula to a 6.

    Does the above work for you?

    Kelly

  • Hey @Kelly Moore

    Thank you for catching that Bi-Annual mistake. I was thinking six months and not dividing it by 2.

    However, the formula isn't working. I copy and pasted it as is with the test info already added in.

    As you can see from the screenshot above, it gives me "#incorrect argument set" error. Is it because the Billing Cycle is a drop down list? Or what other issue could it be?

    Thank you so much for your help, I really appreciate it!

    Amber

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Oh if I had a nickel for all the times I've done this - I didn't close the first ISNUMBER. sorry. Add a parenthesis there, which probably means remove one on the end.

  • Where would I put the closing parenthesis for the ISNUMBER? I've tried it in a couple of spots and it gives an #unparseable error.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey

    Sorry - I answered from my phone before and couldn't fix the formula

    IF(ISNUMBER([Service Cost]@row), IF(OR([Billing Cycle]@row="Single Service", [Billing Cycle]@row="Annually"), [Service Cost]@row, IF([Billing Cycle]@row="Monthly", [Service Cost]@row/12, IF([Billing Cycle]@row="Quarterly", [Service Cost]@row/4, IF([Billing Cycle]@row="Bi-Annually", [Service Cost]@row/2)))))

  • Thank you so much! It works perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!