| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF/SUM Formula
I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF/SUM Formula
Hi,
There's nothing wrong with the formula so if AB20 evaluates as false then it will sum the 4 cells providing they are numbers but if any are text you will get a #VALUE! error. What error are you getting? Mike "mickey" wrote: I am reading values from several worksheets that are compiling on a front worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF/SUM Formula
I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error. "Mike H" wrote: Hi, There's nothing wrong with the formula so if AB20 evaluates as false then it will sum the 4 cells providing they are numbers but if any are text you will get a #VALUE! error. What error are you getting? Mike "mickey" wrote: I am reading values from several worksheets that are compiling on a front worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF/SUM Formula
Hi,
Then I think you have a couple of issues. Changing 0 to "" evaluates as false and because you get a value error then some or all of your numbers in P2 etc seem to be text. Try this for each of those cells =isnumber(P2) and each should evaluate as true. If any evaluate as false then they are text. second consider AB2 is it really <0 or is there a small number that the formatting of your formula cell or of AB2 stops you from seeing. Mike "mickey" wrote: I'm getting a blank cell. The cells are all formatted as accounting. If I change the formula from a 0 to "" I do get the #VALUE! error. "Mike H" wrote: Hi, There's nothing wrong with the formula so if AB20 evaluates as false then it will sum the 4 cells providing they are numbers but if any are text you will get a #VALUE! error. What error are you getting? Mike "mickey" wrote: I am reading values from several worksheets that are compiling on a front worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF/SUM Formula
Mike:
I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2 and I get a #VALUE! error. "Mike H" wrote: Hi, Then I think you have a couple of issues. Changing 0 to "" evaluates as false and because you get a value error then some or all of your numbers in P2 etc seem to be text. Try this for each of those cells =isnumber(P2) and each should evaluate as true. If any evaluate as false then they are text. second consider AB2 is it really <0 or is there a small number that the formatting of your formula cell or of AB2 stops you from seeing. Mike "mickey" wrote: I'm getting a blank cell. The cells are all formatted as accounting. If I change the formula from a 0 to "" I do get the #VALUE! error. "Mike H" wrote: Hi, There's nothing wrong with the formula so if AB20 evaluates as false then it will sum the 4 cells providing they are numbers but if any are text you will get a #VALUE! error. What error are you getting? Mike "mickey" wrote: I am reading values from several worksheets that are compiling on a front worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF/SUM Formula
Formatting as a number won't change anything, if the cell contains text then
text it remains. How are these 'numbers' derived, what's the formula? Mike "mickey" wrote: Mike: I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2 and I get a #VALUE! error. "Mike H" wrote: Hi, Then I think you have a couple of issues. Changing 0 to "" evaluates as false and because you get a value error then some or all of your numbers in P2 etc seem to be text. Try this for each of those cells =isnumber(P2) and each should evaluate as true. If any evaluate as false then they are text. second consider AB2 is it really <0 or is there a small number that the formatting of your formula cell or of AB2 stops you from seeing. Mike "mickey" wrote: I'm getting a blank cell. The cells are all formatted as accounting. If I change the formula from a 0 to "" I do get the #VALUE! error. "Mike H" wrote: Hi, There's nothing wrong with the formula so if AB20 evaluates as false then it will sum the 4 cells providing they are numbers but if any are text you will get a #VALUE! error. What error are you getting? Mike "mickey" wrote: I am reading values from several worksheets that are compiling on a front worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF/SUM Formula
If I remove the statement and try to sum the 4 cells with a simple A+B+C+D
formula I get the #VALUE! error. ???? "mickey" wrote: I'm getting a blank cell. The cells are all formatted as accounting. If I change the formula from a 0 to "" I do get the #VALUE! error. "Mike H" wrote: Hi, There's nothing wrong with the formula so if AB20 evaluates as false then it will sum the 4 cells providing they are numbers but if any are text you will get a #VALUE! error. What error are you getting? Mike "mickey" wrote: I am reading values from several worksheets that are compiling on a front worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF/SUM Formula
You say:
"I am reading values from several worksheets that are compiling on a front worksheet..." If this is the case then I'd expect to see the sheet name referenced in your formula, such as; =C2+Sheet2!C2+Sheet3!C2 "mickey" wrote in message ... I am reading values from several worksheets that are compiling on a front worksheet. However, when I try to sum those values my formula is not recognizing the later cell values. =IF(AB20,AB2,P2+S2+V2+Y2) Anyone experience this and how did you resolve it? By the way, I'm running Excel 07. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
| Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
| Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
| Commenting custom formula fields/formula on formula editor | Excel Programming | |||
Hybrid Mode
