| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate an Excel column that contains numbers and #Values
Dear All,
I am trying to calculate a column in Excel but some of the cells within the column have #Value in them because of other calculations within the worksheet. Is there anyway to calculate the numbers in a column (in this case the number are set as a currency) and ignore the #Value that is in some of the cells. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate an Excel column that contains numbers and #Values
One strategy is to change the formulas that are returning #VALUE to an
IF statement, maybe something like this: =IF(ISERROR(your formula here),"",your formula here) This evaluates the formula and returns nothing if the formula returns an error, but returns the formula's result if it is not an error. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate an Excel column that contains numbers and #Values
Hi Dave O
Thanks, but I can not get this to work, I am a bit of a beginer in Excel so maybe I have not explained what I am trying to do properly. ぎ18.00 #VALUE! #VALUE! 0 ぎ425.00 ぎ0.00 ﾂ｣0.00 ぎ18.00 #VALUE! #VALUE! 1 ぎ2,170.00 ぎ2,170.00 ﾂ｣1,446.67 Y 8 ぎ63.00 ぎ504.00 ﾂ｣336.00 1 ぎ460.00 ぎ460.00 ﾂ｣306.67 Y 8 ぎ40.00 ぎ320.00 ﾂ｣213.33 ぎ0.00 ﾂ｣0.00 ぎ0.00 ﾂ｣0.00 ぎ0.00 ﾂ｣0.00 ぎ0.00 ﾂ｣0.00 Total #VALUE! #VALUE! As you can see above because the formula coming across is delivering a #value (which is correct) I can not total the column. I need some way ignoring the #Value so that I can total the column "Dave O" wrote: One strategy is to change the formulas that are returning #VALUE to an IF statement, maybe something like this: =IF(ISERROR(your formula here),"",your formula here) This evaluates the formula and returns nothing if the formula returns an error, but returns the formula's result if it is not an error. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate an Excel column that contains numbers and #Values
What is the formula in your Total line?
"Kevin" wrote: Hi Dave O Thanks, but I can not get this to work, I am a bit of a beginer in Excel so maybe I have not explained what I am trying to do properly. ぎ18.00 #VALUE! #VALUE! 0 ぎ425.00 ぎ0.00 ﾂ｣0.00 ぎ18.00 #VALUE! #VALUE! 1 ぎ2,170.00 ぎ2,170.00 ﾂ｣1,446.67 Y 8 ぎ63.00 ぎ504.00 ﾂ｣336.00 1 ぎ460.00 ぎ460.00 ﾂ｣306.67 Y 8 ぎ40.00 ぎ320.00 ﾂ｣213.33 ぎ0.00 ﾂ｣0.00 ぎ0.00 ﾂ｣0.00 ぎ0.00 ﾂ｣0.00 ぎ0.00 ﾂ｣0.00 Total #VALUE! #VALUE! As you can see above because the formula coming across is delivering a #value (which is correct) I can not total the column. I need some way ignoring the #Value so that I can total the column "Dave O" wrote: One strategy is to change the formulas that are returning #VALUE to an IF statement, maybe something like this: =IF(ISERROR(your formula here),"",your formula here) This evaluates the formula and returns nothing if the formula returns an error, but returns the formula's result if it is not an error. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| duplicate numbers in column a and diferent values in b | Excel Worksheet Functions | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| sum absolute values of a column of numbers in Excel | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| How do you convert numbers as "text" to values for a long column . | Excel Discussion (Misc queries) | |||
Linear Mode
