Need help with a formula for calculating bonus $

[NathanB]

I'm trying to help out my company develop a worksheet for tracking the crew leader's monthly production bonuses and AWO (added Work Order) bonuses. Relevant to this particular question are four data columns: AWO Price, Hours Saved, Hours Lost, and Bonus Amount.

Bonuses are not granted for a job if any hours are lost, but crew leaders get a bonus of 10% of the AWO Price if hours are saved or if the job breaks even. So for the Bonus Amount column, I need a formula to calculate this amount. All numbers in the worksheet will be entered as positive numbers, so basically the calculation just needs to focus on the Hours Lost column. If the number, here, is greater than 0, the Bonus Amount will be $0.00, if not, the bonus should be 10% of the AWO Price. This is the first part of my question.

The second part of my question is this: At the bottom of the worksheet I have sum totals of each of the columns. A production bonus of $160.00 is granted to the crew leaders if they have saved at least one hour for the month. I need a formula to easily calculate this. (I almost got it on my own, but I know something isn't right.) It basically needs to go like this: If the sum of the Hours Lost column is subtracted from the sum of the Hours Saved column and the remaining number is 1 or greater, then the result is a $160.00 bonus. If not, the bonus is $0.00.

Thanks so much for any and all help!

 

[crook_101]

Hi,

Can you post some of your sheet layout on the board as it is difficult to visualise and then you will get the formula with the correct columns etc, uless you are comfortable adjusting them etc??

Cheers,
Ian

 

[crook_101]

Hi,

Guessing at your layout but ammend formulea to suit

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">AWO Price</td><td style="text-align: center;;">Hours Saved</td><td style="text-align: center;;">Hours Lost</td><td style="text-align: center;;">Bonus Amount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">24</td><td style="text-align: center;;">4</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2.4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">24</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">SUM</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">160</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">C2>0,0,A2/10</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=SUM(<font color="Blue">C2:C3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=IF(<font color="Blue">B5-C5>=1,160,0</font>)</td></tr></tbody></table></td></tr></table><br />

Regards,
Ian

 

[NathanB]

Thanks a bunch, man! That did it for me!

 

[crook_101]

Thanks a bunch, man! That did it for me! :wink:

Anytime, pleased you are sorted