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.
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="Bi-Annually", [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="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
-
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="Bi-Annually", [Service Cost]@row/2)))))
-
Thank you so much! It works perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!