determine if a cell value falls within a certain range and multiply that value by 1.5

[bmd3056]

Hi

I am trying to write a formula for calculating overtime rates.
It needs to identify if a value entered into a cell (in this case F4) is between 7.6 and 9.6.
If the value is 8.6 it needs to give a value of 1, if the value is 9.6 it needs to return a value of 2 and all the variations in between.
It then needs to multiply the value returned by 1.5

i have included the spreadsheet i am working on for reference

Please help as i'm tearing my hair out over this

[Logit]

It is always better to attached a representative workbook of your project with mockup data. Doesn't need to be alot of data, just enough to demonstrate what you are seeking.

What does :

If the value is 8.6 it needs to give a value of 1, if the value is 9.6 it needs to return a value of 2 and all the variations in between.

"and all the variations in between." mean ? I'm confused.


Also you indicate the number 7.6 to 8.6 and then 8.6 to 9.6 ... one returns a 1 and the other a 2.
Where does the 1 and 2 go in the spreadsheet ?

Then multiply the value by 1.5 ... so

1 x 1.5 = 1.5

2 x 1.5 = 3

Where does the 1.5 and the 3 go ?

[bmd3056]

hi Logit

thanks for the reply, sorry if i didnt provide enough information, i thought the atttached excel spreadsheet provided enough info

what i need to do is create a formula that can assist me in calculating the overtime that is owed to an employe based on the hours that they work.

in this matter overtime is calculated as follows:
up to 7.6 hours = normal hours of work and no overtime
7.6 to 9.6 hours = work performed for any amount of time in this period is calculated at 150% of base rate
any hours worked over 9.6 hours = ork performed for any amount of time in this period is calculated at 200% of base rate

i was able to create a formula that calculated the overtime worked in excess of 9.6 hours using the formula =MAX(0,(E4-9.6)*2)

in this instance i am using the hours worked on tuesday (10.6 hours), as such this includes 2 hours to be calculated at 150% and 1 hour at 200%

what i need is a formula that will recognise the range of time worked between 7.6 hours to 9.6 hours and transform these values into a corresponding range of values between 0 to 2. This would then need to be multiplied by 150%.

i hope this makes more sense and any help that you can give will be greatly appreciated

[Logit]

.
.
Is this what you meant by "all the variations in between" ?

Excel 2007 32 bit

7.6	=	0
7.7	=	0.1
7.8	=	0.2
7.9	=	0.3
8	=	0.4
8.1	=	0.5
8.2	=	0.6
8.3	=	0.7
8.4	=	0.8
8.5	=	0.9
8.6	=	1
8.7	=	1.1
8.8	=	1.2
8.9	=	1.3
9	=	1.4
9.1	=	1.5
9.2	=	1.6
9.3	=	1.7
9.4	=	1.8
9.5	=	1.9
9.6	=	2

Sheet: Sheet1

[bmd3056]

yes it is, i just didnt know how to write that into a formula