I am trying to create what I would call an if/then formula in Excel 2003. If
someone types the letters PTO into a cell have tat equal zero in the formula.
I have a sum formula of a set of cells I want Excel to be able to add a zero
into the formula if someone types the letters PTO into the cell. My formula
works great until someone types letters into a cell then - of course -I get a
value error in the cell.

In your sum formula...i.e. =A5 + B5 + C5... you can insert an if statement
for the case where the value may not be a number for example PTO is written
in C5

=A5 + B5 +if(isnumber(C5),C5,0)

Hopefully this helps.

"John Krsulic" wrote:

I am trying to create what I would call an if/then formula in Excel 2003. If
someone types the letters PTO into a cell have tat equal zero in the formula.
I have a sum formula of a set of cells I want Excel to be able to add a zero
into the formula if someone types the letters PTO into the cell. My formula
works great until someone types letters into a cell then - of course -I get a
value error in the cell.

Thanks for your help
Here is my formula
=B11+D11+F11+H11+J11+L11+N11+P11+R11+T11+V11+X11+Z 11+AB11+AD11+AF11+AH11+AJ11+AL11+AN11+AP11+AR11+AT 11+AV11+AX11+AZ11+BB11+BD11+BF11+BH11
I need to make sure that if they write PTO in any cell it will tell the
formula it =the value of 0 so I do not get a name error. The suggestion
worked from below but only if it is typed in C5.
"RJ" wrote:

In your sum formula...i.e. =A5 + B5 + C5... you can insert an if statement
for the case where the value may not be a number for example PTO is written
in C5

=A5 + B5 +if(isnumber(C5),C5,0)

Hopefully this helps.

"John Krsulic" wrote:

I am trying to create what I would call an if/then formula in Excel 2003. If
someone types the letters PTO into a cell have tat equal zero in the formula.
I have a sum formula of a set of cells I want Excel to be able to add a zero
into the formula if someone types the letters PTO into the cell. My formula
works great until someone types letters into a cell then - of course -I get a
value error in the cell.

Here's an example of how you could do that.

Original formula may be:
=A1+B1

But if A1 is PTO, then:
=IF(A1="PTO",0,A1)+B1

HTH,
Paul

"John Krsulic" <John wrote in message
...
I am trying to create what I would call an if/then formula in Excel 2003.
If
someone types the letters PTO into a cell have tat equal zero in the
formula.
I have a sum formula of a set of cells I want Excel to be able to add a
zero
into the formula if someone types the letters PTO into the cell. My
formula
works great until someone types letters into a cell then - of course -I
get a
value error in the cell.

Thanks for your reply
Here is my formula
=B11+D11+F11+H11+J11+L11+N11+P11+R11+T11+V11+X11+Z 11+AB11+AD11+AF11+AH11+AJ11+AL11+AN11+AP11+AR11+AT 11+AV11+AX11+AZ11+BB11+BD11+BF11+BH11
I need to make sure that if PTO is typed into ANY CELL that is gives back
the value of 0 in the formula. Not just A1.


"PCLIVE" wrote:

Here's an example of how you could do that.

Original formula may be:
=A1+B1

But if A1 is PTO, then:
=IF(A1="PTO",0,A1)+B1

HTH,
Paul

"John Krsulic" <John wrote in message
...
I am trying to create what I would call an if/then formula in Excel 2003.
If
someone types the letters PTO into a cell have tat equal zero in the
formula.
I have a sum formula of a set of cells I want Excel to be able to add a
zero
into the formula if someone types the letters PTO into the cell. My
formula
works great until someone types letters into a cell then - of course -I
get a
value error in the cell.

In A1:
=IF(OR(COUNTIF(B:IV,"PTO"),COUNTIF(A2:A65536,"PTO" )),0,SUMPRODUCT(--(MOD(COLUMN(B11:BH11),2)=0),B11:BH11))

PTO can enter in any cells except cell A1, and cells B11:BH11


"John Krsulic" wrote:

Thanks for your reply
Here is my formula
=B11+D11+F11+H11+J11+L11+N11+P11+R11+T11+V11+X11+Z 11+AB11+AD11+AF11+AH11+AJ11+AL11+AN11+AP11+AR11+AT 11+AV11+AX11+AZ11+BB11+BD11+BF11+BH11
I need to make sure that if PTO is typed into ANY CELL that is gives back
the value of 0 in the formula. Not just A1.


"PCLIVE" wrote:

Here's an example of how you could do that.

Original formula may be:
=A1+B1

But if A1 is PTO, then:
=IF(A1="PTO",0,A1)+B1

HTH,
Paul

"John Krsulic" <John wrote in message
...
I am trying to create what I would call an if/then formula in Excel 2003.
If
someone types the letters PTO into a cell have tat equal zero in the
formula.
I have a sum formula of a set of cells I want Excel to be able to add a
zero
into the formula if someone types the letters PTO into the cell. My
formula
works great until someone types letters into a cell then - of course -I
get a
value error in the cell.

RJ's method should work for you.

=SUM(B11,D11,F11,H11,J11,L11,N11,P11,R11,T11,V11,X 11,Z11,AB11,AD11,AF11,AH11,AJ11,AL11,AN11,AP11,AR1 1,AT11,AV11,AX11,AZ11,BB11,BD11,BF11,BH11)

Watchout for wrap-around.

Regards,
Paul

"John Krsulic" wrote in message
...
Thanks for your reply
Here is my formula
=B11+D11+F11+H11+J11+L11+N11+P11+R11+T11+V11+X11+Z 11+AB11+AD11+AF11+AH11+AJ11+AL11+AN11+AP11+AR11+AT 11+AV11+AX11+AZ11+BB11+BD11+BF11+BH11
I need to make sure that if PTO is typed into ANY CELL that is gives back
the value of 0 in the formula. Not just A1.


"PCLIVE" wrote:

Here's an example of how you could do that.

Original formula may be:
=A1+B1

But if A1 is PTO, then:
=IF(A1="PTO",0,A1)+B1

HTH,
Paul

"John Krsulic" <John wrote in message
...
I am trying to create what I would call an if/then formula in Excel
2003.
If
someone types the letters PTO into a cell have tat equal zero in the
formula.
I have a sum formula of a set of cells I want Excel to be able to add a
zero
into the formula if someone types the letters PTO into the cell. My
formula
works great until someone types letters into a cell then - of course -I
get a
value error in the cell.

Yeah! ... but at the time, I didn't know how many cells he had to total.

This should be a little more concise:

=SUMPRODUCT(--(MOD(COLUMN(B11:BH11),2)=0),B11:BH11)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PCLIVE" wrote in message
...
RJ's method should work for you.

=SUM(B11,D11,F11,H11,J11,L11,N11,P11,R11,T11,V11,X 11,Z11,AB11,AD11,AF11,AH11,AJ11,AL11,AN11,AP11,AR1 1,AT11,AV11,AX11,AZ11,BB11,BD11,BF11,BH11)

Watchout for wrap-around.

Regards,
Paul

"John Krsulic" wrote in message
...
Thanks for your reply
Here is my formula
=B11+D11+F11+H11+J11+L11+N11+P11+R11+T11+V11+X11+Z 11+AB11+AD11+AF11+AH11+AJ11+AL11+AN11+AP11+AR11+AT 11+AV11+AX11+AZ11+BB11+BD11+BF11+BH11
I need to make sure that if PTO is typed into ANY CELL that is gives back
the value of 0 in the formula. Not just A1.


"PCLIVE" wrote:

Here's an example of how you could do that.

Original formula may be:
=A1+B1

But if A1 is PTO, then:
=IF(A1="PTO",0,A1)+B1

HTH,
Paul

"John Krsulic" <John wrote in message
...
I am trying to create what I would call an if/then formula in Excel
2003.
If
someone types the letters PTO into a cell have tat equal zero in the
formula.
I have a sum formula of a set of cells I want Excel to be able to add
a
zero
into the formula if someone types the letters PTO into the cell. My
formula
works great until someone types letters into a cell then - of
course -I
get a
value error in the cell.

Just don't tell Bob Phillips that I used a double unary!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
Yeah! ... but at the time, I didn't know how many cells he had to total.

This should be a little more concise:

=SUMPRODUCT(--(MOD(COLUMN(B11:BH11),2)=0),B11:BH11)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PCLIVE" wrote in message
...
RJ's method should work for you.

=SUM(B11,D11,F11,H11,J11,L11,N11,P11,R11,T11,V11,X 11,Z11,AB11,AD11,AF11,AH11,AJ11,AL11,AN11,AP11,AR1 1,AT11,AV11,AX11,AZ11,BB11,BD11,BF11,BH11)

Watchout for wrap-around.

Regards,
Paul

"John Krsulic" wrote in message
...
Thanks for your reply
Here is my formula
=B11+D11+F11+H11+J11+L11+N11+P11+R11+T11+V11+X11+Z 11+AB11+AD11+AF11+AH11+AJ11+AL11+AN11+AP11+AR11+AT 11+AV11+AX11+AZ11+BB11+BD11+BF11+BH11
I need to make sure that if PTO is typed into ANY CELL that is gives
back
the value of 0 in the formula. Not just A1.


"PCLIVE" wrote:

Here's an example of how you could do that.

Original formula may be:
=A1+B1

But if A1 is PTO, then:
=IF(A1="PTO",0,A1)+B1

HTH,
Paul

"John Krsulic" <John wrote in
message
...
I am trying to create what I would call an if/then formula in Excel
2003.
If
someone types the letters PTO into a cell have tat equal zero in the
formula.
I have a sum formula of a set of cells I want Excel to be able to add
a
zero
into the formula if someone types the letters PTO into the cell. My
formula
works great until someone types letters into a cell then - of
course -I
get a
value error in the cell.

Use the SUM() function!
It ignores text.

=Sum(A1:A110,B5:B15)
=Sum(A1,A33,B4,C2,C25,Z15)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"John Krsulic" <John wrote in message
...
I am trying to create what I would call an if/then formula in Excel 2003.
If
someone types the letters PTO into a cell have tat equal zero in the
formula.
I have a sum formula of a set of cells I want Excel to be able to add a
zero
into the formula if someone types the letters PTO into the cell. My
formula
works great until someone types letters into a cell then - of course -I
get a
value error in the cell.