Hello Bob,
That works in a separate cell but I want to do everything in one cell
because I want to calculate any hours worked over 7.5 hours and then also
check to see that they don't come from the overtime hours. It is possible to
make a formula that:
Looks the see if the total hours are over 7.5. (did this already by using
automsum for total hours in B19)
If so tell the hours over 7.5 (did this using the =(B19-7.50)*(B19-7.5) and
that formula was put in cell B20.
at the same time make sure the hours over 7.5 don't come from the overtime
hours which are located in the B10 cell.
And put the result in B20.
In other words do everything with one formula in one cell.
Thanks again for your help!
Best Regards,
PK
"Bob Phillips" wrote:
or MAX(B20-7,5,0)
--
HTH
Bob Phillips
"KL" wrote in message
...
Hi PK,
Try typing the following formula in [B21]:
=(B20-7.50)*(B20-7.5)
or
=IF(B20-7.50,B20-7.5,0)
Regards,
KL
"PK" wrote in message
...
Hi,
I need to compare a value in a cell and see if it is higher than 7.5.
If
it
is higher than 7.5 I want to subract 7.5 from it and move the result
into
another cell. For instance. If B20 7.5 Subract 7.5 from B20 and Move
to
B21. How can I do that easily? I was trying to use the IF function but
cannot find the correct formatting to get it to move the value to the
B21
cell.
Thanks,
PK
"Bob Phillips" wrote:
=MAX(B20-B10-7.5,0)
--
HTH
Bob Phillips
"PK" wrote in message
...
Thank you very much. Now for another tough one for me at least since you
were so quick with the last one!
Now I forgot that I have to look at one cell (B10) and see how much the
value is and subtract it from the value that is in B21.
The purpose of this spreadsheet is making a hour report for us
commissioning
guys at my job so that we can keep up with how many normal working hours
we
work and also overtime hours. But when calculating the hours, if we are
travelling we don't make any overtime.
So for everything over 7.5 hours, we get overtime (B20-7.5). But we don't
get any overtime hours for travelling. So those hours cannot be
calculated
in the equation.
I need to calculate all of the total working hours including travelling
hours and put them into this B20 field. I have done this already by using
an
autosum on the different types of working hours that we can have.
Included
in that is travelling hours.
The formula you gave me worked perfectly but now I need to check this B10
field which is travelling hours and see if there are any hours entered
into
the B10 field and if they are, I need to subract them from the total
overtime
hours in the B21 field. All this in one formula so I just enter it in B21
again.
Thanks
PK
"KL" wrote:
Hi PK,
Try typing the following formula in [B21]:
=(B20-7.50)*(B20-7.5)
or
=IF(B20-7.50,B20-7.5,0)
Regards,
KL
"PK" wrote in message
...
Hi,
I need to compare a value in a cell and see if it is higher than 7.5.
If
it
is higher than 7.5 I want to subract 7.5 from it and move the result
into
another cell. For instance. If B20 7.5 Subract 7.5 from B20 and
Move
to
B21. How can I do that easily? I was trying to use the IF function
but
cannot find the correct formatting to get it to move the value to the
B21
cell.
Thanks,
PK
"KL" wrote:
Hi PK,
Try typing the following formula in [B21]:
=(B20-7.50)*(B20-7.5)
or
=IF(B20-7.50,B20-7.5,0)
Regards,
KL
"PK" wrote in message
...
Hi,
I need to compare a value in a cell and see if it is higher than 7.5.
If
it
is higher than 7.5 I want to subract 7.5 from it and move the result
into
another cell. For instance. If B20 7.5 Subract 7.5 from B20 and
Move
to
B21. How can I do that easily? I was trying to use the IF function
but
cannot find the correct formatting to get it to move the value to the
B21
cell.
Thanks,
PK
|