Help with Design/Nested Formulas

Pam Ferguson
Pam Ferguson Overachievers Alumni

So I need some help with my design or nested formulas.....

I have a formula in a column (listed below) which is perfect, except that in a few instances there are exceptions, and I must add up several different $ amounts from a few CLINS on one particular row and multiply it by different %.

CAF DUE COLUMN: =([CLIN_BILLED_AMOUNT]@row * [I/C/A FEE%]@row) / 100

My thought is to have a Master File of the CLIN Exceptions. This file would contain the following columns: CLIN, Exception I/C/A Fee%, Active/Inactive, and the SUMS (where I need a calculated total of several different CLINS from another reference sheet as this list may fluctuate throughout the year.

Then modify the CAF DUE COLUMN formula above to some effect with a VLOOKUP the CLIN@row and on my CLIN_Exceptions_Sheet and if it's there then use SUMS@row * [I/C/A FEE% Exception] @row) / 100 but if it's not then =([CLIN_BILLED_AMOUNT]@row * [I/C/A FEE%]@row) / 100.

@Paul Newcome Am I thinking along the correct lines? Is there a better way?

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots of the two sheets with data manually entered to show what you are trying to accomplish? I think I am following what you are trying to do, but I want to make sure first.

  • Pam Ferguson
    Pam Ferguson Overachievers Alumni

    Certainly! Here is my concept. Thanks so much.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am not seeing sheet names in your screenshots. I assume that "Capture.2" is your Master and "Capture" is where you want the formula for the calculations?

  • Pam Ferguson
    Pam Ferguson Overachievers Alumni

    Sorry, my system got hung before I could get back in and add that. You are correct. Capture2jpg.jpg is my Master which is where I was thinking I would keep the running log of all the exceptions and the odd Exception %'s. Capture.jpg is the file that contains all the source data where I want the calculations.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am still struggling to follow exactly what you are trying to accomplish...


    Here's is how I understand it right now...


    You want a formula on Capture (source sheet). This formula will first look at Master. If the CLIN is found on Master, you want the formula on Capture to use [I/C/A FEE% Exception]@row. If it is not found on Master, then you want to use [I/C/A FEE%]@row.


    I am still not sure I follow "use SUMS@row" though.

  • Pam Ferguson
    Pam Ferguson Overachievers Alumni

    I figured out a work around by using a pivot table, a helper column and vlookup which gave me what I needed. Maybe clunky but a good quick fix for what they needed in the short term.

    The Sums @ row -- basically I want to total of the column of CLIN_BILLED_AMT for every row except for the rows that have a C in the column. So they have multiple lines that they need for various reasons. I only need the Sum of lines 1, 2 and 3 that have matching groupings. Some have "exceptions" meaning not a common account number where they have to be added together for whatever reason and included together for reporting reasons. These exceptions I wanted to place on a separate listing since there are not that many.

    1

    2

    3

    4 C

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Which column would the "C" be in? The [CLIN_BILLED_AMT] column that you are actually summing or a different column?

  • Pam Ferguson
    Pam Ferguson Overachievers Alumni

    There is actually a separate column that has a c in it (C/L/A).


    You are a such a help! I am struggling with these formulas on this one and I really found a lot of help by searching this community. The other item I am struggling with is writing the correct format to do the correct calculation for the contract award fee. It is comparing the [total funded] * [% Fee] to the sums {totals of the CLIN billed amount - (all those lines that don't have a C on them)] * [% Fee]

    Whichever number is greater I need to enter that number in my CAF DUE Fee column

    If the numbers are equal I still take the sums {totals of the CLIN billed amount - (all those lines that don't have a C on them)] * [% Fee].


    Nested formulas are not my forte.....I am now looking for a class so that I can get smarter with the syntax.

  • Pam Ferguson
    Pam Ferguson Overachievers Alumni

    That is awesome. One question, how can I account for when the numbers are equal? If they are equal, then I take the SUMIFS({Other Sheet Column To Sum}, {Other Sheet Column Containing "C"}, @cell <> "C")) * [% Fee]@row.

    I already see that on a couple of places. Could I use an IF statement with the MAX?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Honestly... If they are equal then they would produce the same result in which case it wouldn't really matter which number is pulled. Then when one becomes greater than the other, the MAX statement would pull the larger of the two.


    Having said that... If both numbers are equal, the MAX function will pull the first number. So if both are equal and you want it to pull the SUMIFS, then you would make sure the SUMIFS is the first part of the MAX.

  • Pam Ferguson
    Pam Ferguson Overachievers Alumni

    Perfect. Thanks so much!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️


    Please don't forget to mark the most appropriate response(s) as "helpful" so that others encountering a similar issue may know that a solution may be found in this thread.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!