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 BiAnnually.
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 BiAnnually 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.
Best 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="BiAnnually", [Service Cost]@row/2)))))
Answers

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="BiAnnually", [Service Cost]@row/2)))))
I added the 'ISNUMBER' at the beginning to try to prevent formula errors if a nonnumber ended up in the column. Also, you defined Biannually 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 BiAnnual 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

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.

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="BiAnnually", [Service Cost]@row/2)))))

Thank you so much! It works perfectly!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!