Help with Design/Nested Formulas
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

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.
Answers

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.

Certainly! Here is my concept. Thanks so much.

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?

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.

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.

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

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

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.

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.

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?

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.

Perfect. Thanks so much!!!

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
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 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!