Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having cells default to 0
I am using a sum total of column 'c' on sheet 1. The dollar values in cell
'c' are automatically entered based on information in column 'B'. The dollar value refers to a dollar amount listed in column 'c' on sheet 3. When I try to add the dollar amounts though I get an error message. I believe it is because the dollar value in column 'c' sheet 1 only appears if there is information in the adjacent cell in column 'B', I usually end up with a #n/a. How do I sum up the column when I have error messages in them? Is there a way to tell the cell that will have the total dollar value, to only include the cells with numbers in the given range and ignore cells with errors? OR Can I use a formula in conditional formats that tells the cells with errors to default to '0' if there is an error? Thanks for any help, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having cells default to 0
Check to see if the formula returns an error, if so then set the value to
0... for instance If(Iserror(B1),0,B1) Something like that Basically whatever formula you have now, just add an If(Iserror( to it "Riptide" wrote: I am using a sum total of column 'c' on sheet 1. The dollar values in cell 'c' are automatically entered based on information in column 'B'. The dollar value refers to a dollar amount listed in column 'c' on sheet 3. When I try to add the dollar amounts though I get an error message. I believe it is because the dollar value in column 'c' sheet 1 only appears if there is information in the adjacent cell in column 'B', I usually end up with a #n/a. How do I sum up the column when I have error messages in them? Is there a way to tell the cell that will have the total dollar value, to only include the cells with numbers in the given range and ignore cells with errors? OR Can I use a formula in conditional formats that tells the cells with errors to default to '0' if there is an error? Thanks for any help, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having cells default to 0
The cell actually returns a #REF in the cell. i tried your formula and added
it to the end of the formula already in the cell but it didn't work. A $0 appeared in the cell, however, the sum formula wasn't recognizing it. Here is the formula that i have in the cell: =OFFSET(INDEX(INDIRECT(A10),1),MATCH(B10,INDIRECT( A10),0)-1,1) The in conditional formatting, I have: =ISERROR(C6) and format the text to match the background. The sum formula I have is: =SUM(B6:B19,B21:B30) any help would be greatly appreciated as this appears to be the last big issue that i have with this spreadsheet. Thank you, "akphidelt" wrote: Check to see if the formula returns an error, if so then set the value to 0... for instance If(Iserror(B1),0,B1) Something like that Basically whatever formula you have now, just add an If(Iserror( to it "Riptide" wrote: I am using a sum total of column 'c' on sheet 1. The dollar values in cell 'c' are automatically entered based on information in column 'B'. The dollar value refers to a dollar amount listed in column 'c' on sheet 3. When I try to add the dollar amounts though I get an error message. I believe it is because the dollar value in column 'c' sheet 1 only appears if there is information in the adjacent cell in column 'B', I usually end up with a #n/a. How do I sum up the column when I have error messages in them? Is there a way to tell the cell that will have the total dollar value, to only include the cells with numbers in the given range and ignore cells with errors? OR Can I use a formula in conditional formats that tells the cells with errors to default to '0' if there is an error? Thanks for any help, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible to make certain cells default to 0? | Excel Discussion (Misc queries) | |||
reset certain cells to a default value | Excel Worksheet Functions | |||
How do I format default cells in a template | Excel Worksheet Functions | |||
Can you set to default the # of decimal places in Format Cells? | Setting up and Configuration of Excel | |||
Set Default Value in Cells | Excel Discussion (Misc queries) |