Pay Rate formula

[Camineet]

Dear Excel friends,

I'm in need of help with a somewhat complicated (for me) formula to arrive at a Pay Rate for my therapeutic counseling agency's counselors.

Their monthly pay rate per work unit is determined by their so-called Roster Score along with an adjustment for any Missing Items.

I've already been able to successfully create a formula for determining the Rate based on the Roster Score. But I need to further develop the formula to make an adjustment to the results based on Missing Items.

The cells of interest in this post are H7, H8, and H9.

The enhancement to the formula I need is as follows...

If Missing Items equals zero, no change to Rate.

If Missing Items equals 1 or 2, Rate becomes 30 or that which was determined by the existing formula (that which is determined by Roster Score alone), whichever is lower.

If Missing Items equals 3 or greater, Rate becomes 27.50.

Any help you guys can offer would be greatly appreciated. I think the needed enhancement is an Or function with multiple terms accommodating the various combinations of Roster Score and Missing items.



Many Thanks,

Excel Workbook
	B	C	D	E	F	G	H	I
5				Hire Date	Roster Score	Missing Items	Rate	Bonus to Amy
6	Amy J				0.943	0	30
7		Smith	Frannie	11/5/2008	0.930	1	27.5	0
8		Hammil	Andrea	11/26/2009	0.944	2	29.5	40
9		Hughs	Maxine	5/1/2008	0.998	3	32.5	100
10							Total	140
Sheet1

 

[Saagar]

Simple way is to leave your H column as is. Let's call it "Roster Rate" instead of "Rate". Insert a new column at I. Your Current Column I with "Bonus Pay" will become Column J.

In cells I7, insert this formula.
=IF(G7=0,H7,if(G7<=2,Min(30,H7),27.5))

I7 will be now your intended Pay Rate.

Hope this helps!

 

[Camineet]

Excellent! Thanks a lot for your help. I was able to adapt the formula to the Supervisor Amy J also. The Supervisor receives a unit rate of $2.50 higher in all scenarios. Great stuff, I really appreciate it.