Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF _Different Values-
I'm working on a formula for Split Shift (SS), Having different hourly rates
and different hours worked. For example, Rate 1: @ $8.25/hr if the employee (EE) worked from 4:00 hrs to 4:59 hrs the SS is $7.00, if the EE worked from 5:00 hrs to 5:59 hrs then the SS is $6.75 and so on up to 10:59 hrs. Rate 2: @ $9.00/hr if the EE worked from 4:00 to 4:59 hrs the SS is $4.00, if the EE worked from 5:00 to 5:59 hrs then the SS is $3.00 and so on up to 10:59 hrs. I have several rates, how can I include all of them in the formula? This is my formula so far: =IF((A18=8.25)*AND(B18=4)*(B18<5),"7",IF((B18=5) *(B18<6),"6.75",IF((B18=6)*(B18<7),"6.50",IF((B18 =7)*(B18<8),"6.25",IF((B18=8)*(B18<9),"6",IF((B1 8=9)*(B18<10),"5.75",IF((B18=10)*(B18<11),"5.50" ,))))))) Thanks in advance for your help. Rick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF _Different Values-
Rick,
For Rate 1, try this: =IF(A18=8.25,MAX(5.5,(B18=4)*(7-MAX(0,INT((B18-5)+1)*0.25)),0),0) What happens above 11 hours? Rate 2 isn't so well behaved - the one dollar decrease is too large.... HTH, Bernie MS Excel MVP "Rick." wrote in message ... I'm working on a formula for Split Shift (SS), Having different hourly rates and different hours worked. For example, Rate 1: @ $8.25/hr if the employee (EE) worked from 4:00 hrs to 4:59 hrs the SS is $7.00, if the EE worked from 5:00 hrs to 5:59 hrs then the SS is $6.75 and so on up to 10:59 hrs. Rate 2: @ $9.00/hr if the EE worked from 4:00 to 4:59 hrs the SS is $4.00, if the EE worked from 5:00 to 5:59 hrs then the SS is $3.00 and so on up to 10:59 hrs. I have several rates, how can I include all of them in the formula? This is my formula so far: =IF((A18=8.25)*AND(B18=4)*(B18<5),"7",IF((B18=5) *(B18<6),"6.75",IF((B18=6)*(B18<7),"6.50",IF((B18 =7)*(B18<8),"6.25",IF((B18=8)*(B18<9),"6",IF((B1 8=9)*(B18<10),"5.75",IF((B18=10)*(B18<11),"5.50" ,))))))) Thanks in advance for your help. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 doesnt show Y-axis values when the values are small. | Charts and Charting in Excel | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) |