Hello all,

I have a work schedule where the column heading is the employee's name and
the cells below indicate what the employee's status was for that date, as
follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column "C"
using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The only
way I know of to SUM column "C" is to copy and paste the values, then
convert to a number.

Does anyone know an easier way to do this without the copy,paste,convert, if
possible?

Thanks,

Mike

Hi Michael,

The following array formula will sum your values in Column B without the need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value (eg 8hrs), change the '0' in the formula to that value. Note
though that this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Michael Slater" wrote in message ...
Hello all,

I have a work schedule where the column heading is the employee's name and the cells below indicate what the employee's status was
for that date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column "C" using the following formula:
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The only way I know of to SUM column "C" is to copy and paste
the values, then convert to a number.

Does anyone know an easier way to do this without the copy,paste,convert, if possible?

Thanks,

Mike

Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


"macropod" wrote in message
...
Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value (eg
8hrs), change the '0' in the formula to that value. Note though that this
will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Michael Slater" wrote in message
...
Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the copy,paste,convert,
if possible?

Thanks,

Mike

Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



"Michael Slater" wrote in message
. ..
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


"macropod" wrote in message
...
Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though that
this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Michael Slater" wrote in message
...
Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike

Hi guys,

I am trying to do a SUM using this formula on a same type of column:
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml


But the results is 0. Do have to plug anything else in the formula? I
played with it and cannot figure it out.

Thanks in advance,

Frank

"MartinW" wrote in message
...
Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



"Michael Slater" wrote in message
. ..
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


"macropod" wrote in message
...
Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though that
this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Michael Slater" wrote in message
...
Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike

You can use TEXT or format the cell with 1, 2, 3, ... decimals
Using TEXT, try something like this:

=TEXT( SUM( A1:A12 ), "#.####")


--

Rodrigo Ferreira
Regards from Brazil


"Frank Beltre" escreveu na mensagem
...
Hi guys,

I am trying to do a SUM using this formula on a same type of column:
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml


But the results is 0. Do have to plug anything else in the formula? I
played with it and cannot figure it out.

Thanks in advance,

Frank

"MartinW" wrote in message
...
Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



"Michael Slater" wrote in message
. ..
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


"macropod" wrote in message
...
Hi Michael,

The following array formula will sum your values in Column B without
the need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though
that this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Michael Slater" wrote in message
...
Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike

Hi Frank,

Apart from changing the data range to suit your data, the formula will works as posted.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Frank Beltre" wrote in message ...
Hi guys,

I am trying to do a SUM using this formula on a same type of column:
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml
0.1 ml


But the results is 0. Do have to plug anything else in the formula? I
played with it and cannot figure it out.

Thanks in advance,

Frank

"MartinW" wrote in message
...
Hi Michael,

Just as an addition you can also get your original formula to do
the same conversion as your copy and paste step.

Original formula
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

Becomes
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1
or
=IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0
or even
=--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

All three will convert your text answer to numbers.

HTH
Martin



"Michael Slater" wrote in message
. ..
Macropod,

Thank you very much! That fits the bill perfectly!

Regards,

Mike


"macropod" wrote in message
...
Hi Michael,

The following array formula will sum your values in Column B without the
need for helper cells:
=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
To make this an arry formula, input it then press Ctrl-Shift-Enter.

If, where you've got the 'D' response, you'd like add a specific value
(eg 8hrs), change the '0' in the formula to that value. Note though that
this will assume 8hrs for any blank cells in the range also.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Michael Slater" wrote in message
...
Hello all,

I have a work schedule where the column heading is the employee's name
and the cells below indicate what the employee's status was for that
date, as follows:

col A col B
Date Smith
01/01/07 8.0 SIC
01/02/07 4.0 SIC
01/03/07 D (indicating worked the day)
01/04/07 8.0 VAC

TOTAL SIC Hrs: 12.0

I've tried extracting the numerical values into a hidden helper column
"C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")

It extracts the correct value, however, it is formatted as text. The
only way I know of to SUM column "C" is to copy and paste the values,
then convert to a number.

Does anyone know an easier way to do this without the
copy,paste,convert, if possible?

Thanks,

Mike