Having trouble getting cell to do what I want

[stankier]

I am new to the site and I have been having an issue I can not figure out! I am working on our payroll program, and I want Excel to add a specific amount, say 24, to every employees bank of hours monthly. I want this to be done automatically without having to enter a figure. So for example on the first of ever month the program would add these hours to a column "A". I have the complete program done except for this.

After it adds these hours in to column "A" I need the cell to have a maximum amount, say 200, that it will not allow it to exceed. Anything over that 200 I need to automatically deduct said amount and multiply it by $10. So as an example if employee "Ben" has earned 180 hours in his bank, March first he will get 24 hours added to that amount. So he would have 204. Then it would deduct 4 hours, keeping him at max amount of 200, and multiply 4 by $10= $40.

Can this be done?

 

[AlanY]

try

Excel 2012
	A	B
1	180	800
Sheet3

Cell Formulas
Range	Formula
B1	=MIN(A1+24,200)*4

 

[stankier]

try

Excel 2012
	A	B
1	180	800
Sheet3

Cell Formulas
Range	Formula
B1	=MIN(A1+24,200)*4

This doesn't fit all the perimeters I need

 

[B___P]

try
in A2 : =MIN(A1+24;200)
in B2 : =10*MAX((A1+24-200);0)

where A1 is last month's bank of hours

bye

 

[stankier]

try
in A2 : =MIN(A1+24;200)
in B2 : =10*MAX((A1+24-200);0)

where A1 is last month's bank of hours

bye

Can I do this task in the same cell?

 

[stankier]

OK I am having trouble with the above formulas. Let me try to use the example attached to as my question. Every month I want to add 24 (A1) to B2. However B2 can not exceed 200. If it does I need the "overage" to be calculated by 10 and placed in C3. B2 amount can vary. So in my example I would add A1 to B1 which gives me 224. I then need to subtract any amount over 200, which can vary, in This case 24 X 10=240 and place that in C3. In the A2 same issue but the max amount would be 204, and the amount of 4X10=40 needs to be placed in C3. In the example I added what I need, but don't know the formula to make it happen.




A B C
1 24 200 $240
2 24 180 $40

 

[AlanY]

try

Excel 2012
	A	B	C
1	24	200	240
2	24	180	40
Sheet1

Cell Formulas
Range	Formula
C1	=(MAX(200,A1+B1)-200)*10

 

[stankier]

try

Excel 2012
	A	B	C
1	24	200	240
2	24	180	40
Sheet1

Cell Formulas
Range	Formula
C1	=(MAX(200,A1+B1)-200)*10

That worked THANK YOU!!!!!!!! I have been messing with this for over a week.

 

[AlanY]

you're welcome

 

[stankier]

Since this is a payroll program and we get paid bimonthly I am having this issue. I have a Column that keeps the master times lets call it A1. When I add 24 via data validation drop down tab it will add the 24 to the A1 column. However it will subtract when I leave that same drop down blank. How can I make it add 24 to column A! every time I click the button/drop down box?