formulas

  • Thread starter Thread starter Deborah
  • Start date Start date
D

Deborah

I need a formula that would calculate hours if the total
hours reach 40 then calculate over time at a different rate
for instance
=(g8:g14)<=40 then the product would be that line *19.33
if (g8:g14=>40 then the product would be that line *28.99
if less than 40 19.33 if more 40 28.99 but each line is
figured individually
each day the total no of hours is entered then when they
reach over 40 be calculated at a higher rate is this
possible.
Thanks Deborah
 
Hi Deborah,
I read your message to mean:
You want to compute a value equal to G8 multiplied by a factor;
if G8 does not exceeded 40 then the factor is 19.33
otherwise it is 28.99
Correct?
Then use =G8*IF((G8<=40,19.33,28.99)
Copy this formula down to line 14 for the other rows

Best wishes
 
Deborah, this formula will give you the rate where hours <=40:

=MIN(40,SUM(G8:G14))*19.33

...and this one will give you the rate for hours in excess of 40:

=MAX(0,SUM(G8:G14)-40)*28.99

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
Deborah, if Bernard has got it right that each cell in the range G8:G14
represents a total # of hours worked, and you want to figure standard and
overtime rates, move the formulas from my other post to the columns to the
right and alter them as follows:

=MIN(40,G8)*19.33 for hours <=40
=MAX(0,G8-40)*28.99 for hours >40

If you want it all in one cell, then =MAX(0,G8-40)*19.33+MIN(40,G8)*28.99.

Copy all formulas down to G14.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
I need to conect them
for example
g8 mon 10 @19.33 193.30
g9 tue 10 @19.33 193.30
g10 wed 12 @19.33 231.96
g11 thur 14 (8 @19.33) and 6 @ 28.99 total 328.58

is there a formula that can calculate if and when they go
into over time and a different rate
for instance what if they had over time by wed
thanks Deborah
 
Deborah, here you go. Put this in the column to the right and copy down:

=MAX(0,SUM(G$8:G8)-40)*28.99+IF(SUM(G$8:G8)<=40,G8*19.33,(G8-MAX(0,SUM(G$8:G
8)-40))*19.33)

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
Thanks you so much for your help. This works great except
my guys work seven days a week sometimee, when I only have
5 days this formula is great, but when I pasted it to
seven days on the sixth and seventh days I got a number
that was not right.

for example
mon 10 hr 193.30
tue 10 hr 193.30
wed 10 hr 193.30
thur 10 hr 193.30
fri 10 hr 289.90
sat 1 hr 38.65
sun 1 hr 48.31
what did I do wrong? I can use 5 days but it would be
better if I used 7 Thanks again for your help.
Deborah
 
Deborah, here is the formula to be placed in H8 and copied down (NB: G7 must
not have a number in it):

=MAX(SUM(G$7:G8)-MAX(0,SUM(G$7:G8)-40)-SUM(G7:G$7),0)*19.33+(SUM(G$7:G8)-MIN
(40,SUM(G$7:G8))-MAX(0,SUM(G$7:G7)-40))*28.99

Now I'm sure there's an easier way to do this and I hope someone out there
will post it, but until they do, this formula will work for you, for all
seven days.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
Back
Top