Formulas 1

[vince1209]

Group Clinical Plan Patient Satisfaction Payout
A2 X Threshold $100.00
C2 X Threshold $100.00

I am trying to create a formula that will produce the follow: If group A2 (cell A2),clinical plan equals x ,and the patient satifaction equal Threshold then the payout equals $100.00.

I have tried evrything to make this work. Any suggestions?

 

[AnotherHiggins]

=If(And(Group = "A2", ClinicalPlan = "X", PatientSatifaction = "Threshold"),100,"")


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

"I have tried evrything to make this work."

Like what?

Hint:
[tt]
AND(expression1,expression2)
[/tt]
NOT
[tt]
expression1 AND expression2
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

I don't think i explained myself clearly. This is what I am trying to do. I have a group depicted by A2, B2, C2 and A2-1 that I need to formulate with either one of these words: "treshold", "target" or "stretch". For the cell which is called Patient Satifisfaction the formula I am trying to create is this:

"If threshold equals A2 then $100.00 or equals B2 then $125.00 or equals C2 then $150.00 or equals A2-1 then $50.00".

"If target equals A2 then $150.00 or equals B2 then $175.00 or equals C2 then $200.00 or equals A2-1 then $70.00".

"If stretch equals A2 then $200.00 or equals B2 then $225.00 or equals C2 then $250.00 or equal A2-1 then $100.00".

I am trying to create a formula without having a lotof different scenarios. Any suggestions?

 

[Gavona]

Sorry, I do not understand.

Can you post some data including the expected results from that data.....and clarify which columns you are trying to calculate?
Also any formulae you have tried already.



Gavin

 

[SkipVought]

Vince,

You refer to TARGET and STRETCH in your 2nd post. Where is that in the example you originally posted.

John's post answered your question EXACTLY!

Get your stuff straight!

Please be CLEAR, CONCISE and COMPLETE.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

Sorry if my last trasmittal was not clear. I will try this again. I am trying to create a formula based on the following:
Cell:A B C D
Incentive Clinical Patient Satisfaction Payout
Group Plan
A2 X
C2 X
B2
A2-1
A2 X
C2

In cell A I have the Incentive Group. In cell B I have identified which ones in Incentive Group is a Clinical Plan. For Cell C it will be manually input whether if it is treshold, target or stretch. For cell D I am trying to create a formula for the payout based on the following:

"If threshold equals A2 then $100.00 or equals B2 then $125.00 or equals C2 then $150.00 or equals A2-1 then $50.00".

"If target equals A2 then $150.00 or equals B2 then $175.00 or equals C2 then $200.00 or equals A2-1 then $70.00".

"If stretch equals A2 then $200.00 or equals B2 then $225.00 or equals C2 then $250.00 or equal A2-1 then $100.00".

Sorry for not being clear enough!!

 

[SkipVought]

How can "threshold equals A2?" Your examples do not make any sense to me.

Do you mean, when Patient Satisfaction is threshold and Incentive Group is A2?

What you need is a TRUTH TABLE that consists of ALL POSSIBLE COMBINATIONS of ...
[tt]
Incentive Clinical Patient Satisfaction Payout
Group Plan
[/tt]
and what Payout value or logic for each.
[highlight]
Please do a FIND on this PAGE for TGML, and PLEASE use in order to make your examples understandable.
[/highlight]

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

Maybe this will help:

Threshold Target Stretch

Group A2 $100 $150 $200
Group A2-1 $50 $75 $100
Group B2 $125 $175 $225
Group C2 $150 $200 $250

based on the above info, I need a formula to give me the following below:

If group A2=Threshold then $100
If group A2=Target then $150
If Group A2=Stretch then $200

The only thing that will be input manually is whether an employee has met either the treshold, target or stretch. That will be given to me. I want to have a formula so that when I enter one of the three it will give me my payout results.

 

[SkipVought]

Put your TABLE on a sheet
[tt]
Threshold Target Stretch
A2 $100 $150 $200
A2-1 $50 $75 $100
B2 $125 $175 $225
C2 $150 $200 $250
[/tt]
lets assume its on Sheet2!A1 and following.

So here's your data on Sheet1
[tt]
Group Clinical Plan Patient Satisfaction Payout
A2 X Threshold
C2 X Threshold
[/tt]
[tt]
D2": =VLOOKUP(A2,Sheet2!$A$2:$D$5,MATCH(C2,Sheet2!$A$1:$D$1,0),FALSE)
[/tt]
and copy down.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}