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 [email protected] and on my CLIN_Exceptions_Sheet and if it's there then use [email protected] * [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?