Is this formula even possible?

  • Thread starter Thread starter Cheryl
  • Start date Start date
C

Cheryl

Is the following even possible?
Cell B5 = OT2 and cell C5 = OT4....can the formula total B5 & C5 a
OT=6 OR SAY
Cell E4 = ST10 and cell E5 = ST1...can the formula total E4 & E5 a
ST=11

The OT and the ST letters just stand for 'overtime' an
'straight-time'
Thanks
 
Assuming that the prefix is always 2 letters...you can do it this way,
at least, if I'm reading you right.

=RIGHT(B5,(LEN(B5)-2))+RIGHT(C5,(LEN(C5)-2))

But, that wont tell you what the prefix originally was, nor will it
error out if say, you're adding OT and ST together. Although, I think
that's possible as well, but not sure if that's what you need.

Hope it helps.

-Bob
 
For OT, try this:

=IF(AND(LEFT(B5,2)="OT",LEFT(C5,2)="OT"),LEFT(B5,2)&"="&MID(B5,3,10)+MID(C5,
3,10),"NO OT")

And for ST, try this:

=IF(AND(LEFT(E4,2)="ST",LEFT(E5,2)="ST"),LEFT(E4,2)&"="&MID(E4,3,10)+MID(E5,
3,10),"NO ST")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Is the following even possible?
Cell B5 = OT2 and cell C5 = OT4....can the formula total B5 & C5 as
OT=6 OR SAY
Cell E4 = ST10 and cell E5 = ST1...can the formula total E4 & E5 as
ST=11

The OT and the ST letters just stand for 'overtime' and
'straight-time'
Thanks,
 
Another option would be to use the CONCATENATE function.

Your sheet would need to be set up a bit different for this.

Basically, create a cell that has "OT" in it for Overtime and anothe
with "ST" for Straight-time.

THen create your overtime/straight time values into other cells...2, 4
10 etc.

You would then combine these cells together using the CONCATENAT
function.

This would then allow you to add the OT/St values and then Concatenat
them as well..
 
Hi Cheryl

If your range of cells is going to contain a mixture of ST and OT you could
have 2 summary cells.
The one for OT would be
="OT= "&SUMPRODUCT((LEFT(B5:C10,2)="OT")*(MID(B5:C10,3,2)))
and for ST
="ST= "&SUMPRODUCT((LEFT(B5:C10,2)="ST")*(MID(B5:C10,3,2)))

The range of cells can be changed to suit your requirement and will cover
both cases.
 
Cheryl > said:
Is the following even possible?
Cell B5 = OT2 and cell C5 = OT4....can the formula total B5 & C5 as
OT=6 OR SAY
Cell E4 = ST10 and cell E5 = ST1...can the formula total E4 & E5 as
ST=11

The OT and the ST letters just stand for 'overtime' and
'straight-time'
Thanks,
Click to expand...

People have answered the question you asked, but the real solution is to
have ST/OT in one column and the number in another. You can achieve this on
existing data with simple formulas such as
=LEFT(A1,2)
and
=VALUE(RIGHT(A1,LEN(A1)-2))
You then copy these results, use paste special > values to overwrite the
formulas with the results, and then delete the orignal data.

It's much better to sort this out once and then enter future data properly.
That way, you will be able to many more calculations in the future without
complicated and contrived formulas.
 
The following formula worked if the data is in B5 and C5. Could yo
please edit it because the data is in B5 - AF5.
All your help is greatly appreciated, thanks a million!!

=IF(AND(LEFT(B5,2)="OT",LEFT(C5,2)="OT"),LEFT(B5,2)&"="&MID(B5,3,10)+MID(C5,
3,10),"NO OT"
 
Hi
to calculate the OT value use Roger's formulas as follows
="OT= "&SUMPRODUCT((LEFT(B5:AF5,2)="OT")*(MID(B5:AF5,3,2)))
and for ST
="ST= "&SUMPRODUCT((LEFT(B5:AF5,2)="ST")*(MID(B5:AF5,3,2)))

HTH
Frank
 
I used 10 in the "MID" portion of my formula to include any number of digits
trailing the OT or ST.

To Roger's very concise and excellent formula, I would recommend at least an
expansion of the MID portion to at least 5, to take into consideration the
possibility of quarter hours (10.25).
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi
to calculate the OT value use Roger's formulas as follows
="OT= "&SUMPRODUCT((LEFT(B5:AF5,2)="OT")*(MID(B5:AF5,3,2)))
and for ST
="ST= "&SUMPRODUCT((LEFT(B5:AF5,2)="ST")*(MID(B5:AF5,3,2)))

HTH
Frank
 
A good point RD.
I had not bothered to think wider than the example posted by the OP, but
obviously time periods other than integer would be likely to be recorded.
 
Back
Top