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.

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

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)))))

All comments

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

Amber McWhorter

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

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.

Amber McWhorter

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

Hey

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

Amber McWhorter

Thank you so much! It works perfectly!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.