Complicated IF this THEN that FORMULA

Susan Swisher

So, I have a pretty complicated formula request:

IF [Approved/Contracted Budget $$ (Total)] IS blank look in [Per Pt Costs (Submission)] Column and take that Number and put it in [Per Pts Costs (FINAL)] Column; however, if [Approved/Contracted Budget $$ (Total)] is completed, use that amount ($$) and put it in the [Per Pts Costs (FINAL)] Column

The Per Pt Costs (Submission) amount always comes 1st and we work towards the Approved/Contracted Budget $$ (Total); so, eventually both numbers will be completed; and the Approved/Contracted Budget $$ always “trumps” the “Submission” $$.

David Tutwiler

=IF([Approved/Contracted Budget $$ (Total)] > 0, [Approved/Contracted Budget $$ (Total)] / [Column (N)], [Per Pt Costs (Submission)])

I just added the division into the part that takes the Approved Budget $$ Total

David Tutwiler

Not too bad Susan. Can you see if this works for you? It will need to go into the Per-Pts-Costs-(Final) column and I would suggest making it a column formula.

=IF([Approved/Contracted Budget $$ (Total)] > 0, [Approved/Contracted Budget $$ (Total)], [Per Pt Costs (Submission)])

The way to read this is, if there is anything in the Approved Budget, then use that number. Otherwise, use the Per Pt Costs Submission.

Brent Wilson

It seems like a simple IF statement if I understand it Correctly

=IF( ISBLANK([Approved/Contracted Budget $$ (Total)]@row), [Per Pt Costs (Submission)]@row, [Approved/Contracted Budget $$ (Total)]@row)

So if the [Approved/Contracted Budget $$ (Total)]@row is empty use the  [Per Pt Costs (Submission)]@row otherwise use the [Per Pt Costs (Submission)]@row.

The issue becomes that you want that value in the [Per Pts Costs (FINAL)] which will eventually be manually overwritten. So you cannot use a Cell Formula

I would recommend you introduce another column say [Per Pt Costs (Tracking)]

and put this in the column

=IF( ISBLANK([Approved/Contracted Budget $$ (Total)]@row), [Per Pt Costs (Submission)]@row, IF( ISBLANK([Per Pts Costs (FINAL)]@row), [Approved/Contracted Budget $$ (Total)]@row, [Per Pts Costs (FINAL)]@row))

then the Tracking Column would always show you the cost you are currently targeting.

Susan Swisher

IT is working; however, I neglected to mention (forgot this part); IF the Approved/Contracted Budget $$ (Total) figure is complete, I need a nested THEN statement to complete a another formula to calculate the actual Per Patient.

For Example:

Approved/Contracted Budget $$ (Total) = $1,000,000

Column (N) = 10 (this is the number of patients)

Therefore, you have to take Approved/Contracted Budget $$ (Total) / 10 (number of patients to get $100,000 (as the Per Pt Costs (FINAL).

So, how do I NEST that THEN statement in that formula?

David Tutwiler

=IF([Approved/Contracted Budget $$ (Total)] > 0, [Approved/Contracted Budget $$ (Total)] / [Column (N)], [Per Pt Costs (Submission)])

I just added the division into the part that takes the Approved Budget $$ Total

Susan Swisher

Works perfectly.. THANK YOU.

David Tutwiler

Awesome, glad it is working. Will you mark my last response as Answered so that anyone else who might have this problem will see it at the top of this thread?