Help with Design/Nested Formulas

Pam Ferguson

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?

Paul Newcome

Ok. So for the sum, you would use something along the lines of

=SUMIFS({Other Sheet Column To Sum}, {Other Sheet Column Containing "C"}, @cell <> "C")

You know they are both going to be multiplied by the [% Fee], but you want to pull whichever is the largest or the MAX.

=MAX([Total Funded]@row, SUMIFS({Other Sheet Column To Sum}, {Other Sheet Column Containing "C"}, @cell <> "C"))

then multiply

=MAX([Total Funded]@row, SUMIFS({Other Sheet Column To Sum}, {Other Sheet Column Containing "C"}, @cell <> "C"))

* [% Fee]@row

And there you have it.

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

Capture.2JPG.JPG

Capture.JPG

Certainly! Here is my concept. Thanks so much.

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

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

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

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

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

Pam Ferguson

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.

Paul Newcome

Ok. So for the sum, you would use something along the lines of

=SUMIFS({Other Sheet Column To Sum}, {Other Sheet Column Containing "C"}, @cell <> "C")

You know they are both going to be multiplied by the [% Fee], but you want to pull whichever is the largest or the MAX.

=MAX([Total Funded]@row, SUMIFS({Other Sheet Column To Sum}, {Other Sheet Column Containing "C"}, @cell <> "C"))

then multiply

=MAX([Total Funded]@row, SUMIFS({Other Sheet Column To Sum}, {Other Sheet Column Containing "C"}, @cell <> "C"))

* [% Fee]@row

And there you have it.

Pam Ferguson

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

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

Perfect. Thanks so much!!!

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.