comparing a value in a cell to see if it is higher than a number

[Guest]

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]

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

 

[Guest]

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

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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]

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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]

=MAX(B20-B10-7.5,0)

--
HTH

Bob Phillips

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

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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

 

[Guest]

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.5>0)*(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

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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

=MAX(B20-B10-7.5,0)

--
HTH

Bob Phillips

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

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL



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

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL



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

 

[Guest]

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.5>0)*(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

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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

 

[Guest]

Hello Bob,

I meant to say travelling hours instead of overtime hours on the last post...


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 travelling 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.5>0)*(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 travelling
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

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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

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.5>0)*(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

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL



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

=MAX(B20-B10-7.5,0)

--
HTH

Bob Phillips

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
:

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL



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




:

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL



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

 

[Guest]

Hey,

Sorry I didn't realize that short formula did everything I asked for! Thank
you very much for your help.. Disregard the other replies asking for more
help!

PK

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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]

neater!

KL

or MAX(B20-7,5,0)

--
HTH

Bob Phillips

Hi PK,

Try typing the following formula in [B21]:

=(B20-7.5>0)*(B20-7.5)

or

=IF(B20-7.5>0,B20-7.5,0)

Regards,
KL

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