ragdyer I need your help!

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

Cheryl

Hello,
You gave me the following formula so I could track the overtime worked
in our department and it worked perfect except I was wondering if you
could edit it to include the correct cells (I told you my information
was in cells a5 and c5).

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

My information is actually in cells B5 to AF5 and when I tried to
include the cells in your formula it never worked.
Thanks a million!!
 
Hi Cheryl

Frank did reply to you yesterday, suggesting a suitably modified version of
the formula I posted.
RD made a further reply suggesting a further modification of the formula I
posted.

Use either
="OT= "&SUMPRODUCT((LEFT(B5:AF5,2)="OT")*(MID(B5:AF5,3,5)))
and for ST
="ST= "&SUMPRODUCT((LEFT(B5:AF5,2)="ST")*(MID(B5:AF5,3,5)))
 
Cheryl,

Try this alternative

="OT="&SUM(VALUE(IF(LEFT(B5:AF5,2)<>"OT",0, RIGHT(B5:AF5,LEN(B5:AF5)-2))))

it is an array formula, so enter with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Cheryl,

For the future, it's better for everyone, *especially you*, to stay with
your original thread.
If you would return to it now, you would see where a lot of people have all
volunteered their efforts in order to provide you with many suggested
solutions to your question.
My suggestion doesn't really work with the *changes* in the data ranges you
stipulated in your second post in your original thread, BUT ... there were
others suggested, that would work perfectly with your new requirements.

Some have been *kindly* repeated here, in this thread, where most of the
time, you would have been *told* to go and find these suggestions in your
original thread.

Since you posted twice in the original thread, most folks in this NG would
expect that you know how to find your posts.

If, on the other hand, as I suspect of most new posters, you can't find it
today because of the amount of new messages added, it might pay you to
perhaps spend some time reviewing the information in these links below.
Besides etiquette, there is information on how to *mark* your posts, so that
you can find them very easily, even weeks and/or months afterward.

http://www.cpearson.com/excel/newposte.htm

http://www.mvps.org/dmcritchie/excel/xlnews.htm

http://www.mvps.org/dmcritchie/excel/oe6.htm

http://www.cpearson.com/excel/DirectConnect.htm

http://www.cpearson.com/excel/links.htm#Newsgroups

http://www.mvps.org/dmcritchie/excel/posting.htm
--

HTH,

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



Hello,
You gave me the following formula so I could track the overtime worked
in our department and it worked perfect except I was wondering if you
could edit it to include the correct cells (I told you my information
was in cells a5 and c5).

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

My information is actually in cells B5 to AF5 and when I tried to
include the cells in your formula it never worked.
Thanks a million!!
 
Thank you all very much,
This formula (see below) worked perfect for my overtime & attendanc
spreadsheet and now I have only one more question.
="OT="&SUM(VALUE(IF(LEFT(B5:AF5,2)<>"OT",0
RIGHT(B5:AF5,LEN(B5:AF5)-2))))
This formula calculates across my spreadsheet (b5:af5). Can I no
total this formula in the column?
example:
aq5:aq110 = ?
Thanks,;
 
Have you tried it ?

Just substitute the cell references.
--

HTH,

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

Thank you all very much,
This formula (see below) worked perfect for my overtime & attendance
spreadsheet and now I have only one more question.
="OT="&SUM(VALUE(IF(LEFT(B5:AF5,2)<>"OT",0,
RIGHT(B5:AF5,LEN(B5:AF5)-2))))
This formula calculates across my spreadsheet (b5:af5). Can I now
total this formula in the column?
example:
aq5:aq110 = ?
Thanks,;)
 
Hello,
I tried to sum my column witht he following, but it didn't work?
="ST="&SUM(VALUE(IF(LEFT(AQ5:AQ109,2)<>"ST",0,RIGHT(AQ5:AQ109,LEN(AQ5:AQ109)-2))))
Thanks again
 
It works for me Cheryl !
And I copied the formula *you* posted above.

Are you sure that you are entering it with CSE, and you are *automatically*
getting curly brackets to enclose the formula ?
 
It won't work for me the darn thing!
I'm sure I entered it with CSE, and I got the curly bracket
automatically. It puts #VALUE in the cell?
I do want to thank you for all your help though!
 
Cheryl > said:
I tried to sum my column witht he following, but it didn't work?
="ST="&SUM(VALUE(IF(LEFT(AQ5:AQ109,2)<>"ST",0,
RIGHT(AQ5:AQ109,LEN(AQ5:AQ109)-2))))
Click to expand...
....

If this is giving #VALUE! error results, then you must have some cells that
don't have valid decimal numbers after the second character. Indeed, if you
have any blank cells in this range, the formula above *will* return #VALUE!
errors. What are the possible contents of all cells in this range?
 
<<"you must have some cells that don't have valid decimal numbers after the
second character">>

True, but to be *completely* accurate Harlan,
#VALUE! is returned *only* when the first two characters are "ST".
Without the "ST", anything entered in the range will *not* return the error.
In fact, "ST" *alone* produces the error, where <Space>ST *doesn't*.


<<"any blank cells in this range, the formula above *will* return #VALUE!
errors">>
This is not accurate.
*Completely* blank cells will not return an error.

So it is understandable how the OP could have error producing data in the
range that is not easily discernable by a cursory observation.
--

Regards,

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



Cheryl > said:
I tried to sum my column witht he following, but it didn't work?
="ST="&SUM(VALUE(IF(LEFT(AQ5:AQ109,2)<>"ST",0,
RIGHT(AQ5:AQ109,LEN(AQ5:AQ109)-2))))
Click to expand...
....

If this is giving #VALUE! error results, then you must have some cells that
don't have valid decimal numbers after the second character. Indeed, if you
have any blank cells in this range, the formula above *will* return #VALUE!
errors. What are the possible contents of all cells in this range?
 
After all the time spent in this thread Cheryl, WHY are you using *TO* in
you formulas instead of OT to define overtime ?

As far as a formula for totaling the straight time (ST) and the overtime
(OT), since the columns *only* contain either one or the other, a much more
simple formula will suffice.

Enter this in column AQ and copy across to AR.
Adjust the ranges as necessary.

=SUM(--MID(AQ5:AQ11,4,6))

*AGAIN*, I remind you to enter this as an array formula - CSE !

This will return only numbers, without any added text (ST= / OT=).

I'm sure that by now you can adjust the formula to add text if needed.
--

HTH,

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



Formula needed to total columns AQ & AR = ?
Please see attachment.

Attachment filename: total for aq & ar =.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=445811
 
Back
Top