| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula add if someone types PTO have it =0
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
Hi Rick
Just don't tell Bob Phillips that I used a double unary!<bg I think you might just get away with it. Bob must be busy at the moment as he doesn't seem to be quite as active in the NG currently .<g I'll keep quiet, as long as you don't mention when I use them!<g -- Regards Roger Govier "Ragdyer" wrote in message ... 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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula add if someone types PTO have it =0
It's a DEAL!<g
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Roger Govier" wrote in message ... Hi Rick Just don't tell Bob Phillips that I used a double unary!<bg I think you might just get away with it. Bob must be busy at the moment as he doesn't seem to be quite as active in the NG currently .<g I'll keep quiet, as long as you don't mention when I use them!<g -- Regards Roger Govier "Ragdyer" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
| eliminating types of missing data from final formula | Excel Discussion (Misc queries) | |||
| Issue writing IF OR Error Formula Types | New Users to Excel | |||
| types of graphs in excel | Excel Discussion (Misc queries) | |||
| Summing types with one formula | Excel Discussion (Misc queries) | |||
Linear Mode
