Complicated IF this THEN that FORMULA
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” $$.
Best Answer

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

Not too bad Susan. Can you see if this works for you? It will need to go into the PerPtsCosts(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.

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.
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

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?

=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

Works perfectly.. THANK YOU.

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?
Help Article Resources
Categories
Check out the Formula Handbook template!