Is there a way to get a formula to return a value instead of an error (#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value at some where other than zero.
Printable View
Is there a way to get a formula to return a value instead of an error (#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value at some where other than zero.
You can do that by wrapping your formula inside of an IF statement with a test to see if it creates an error or not. Consider this : A B C columns and in rows 1 5 0 =A1/B1 will give you a #DIV/0 error. But if you do this at C1: =IF(ISERROR(A1/B1),"invalid",A1/B1) you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd get the true answer. You can substitute a value for "invalid" such as zero or one, like: =IF(ISERROR(A1/B1),0,A1/B1). The ISERROR() test will handle all errors, there are other options such as ISERR() handles any error except #N/A while ISNA() only handles #N/A - good for use with VLookup and Hlookup operations.
Got it, thanks. I just had to use a cell reference with the value I wanted so the graph would show it crossing at the average I have set it at. I used the IF Statement to get rid of the Error and substituted a period. Saved me a lot of time and headache. Literally.
I have a spreadsheet that i have entered a really long formula into. The data I have now, in some parts, is empty (since I wanted to go ahead and set up the formulas for future ease). However, since the fields are empty (I've put dashes (-) in as place holders), I get the #VALUE! error. Is there someway that I can do an If statement so that If I get the #VALUE! error, the cell that contains the formula (and the error) will come up empty.
Do not place dashes in the cells. These dashes change the cell from blank to non-blank. Keep them empty/blank then you won't have to be concerned with them. Why do you need place-holders. What do they do. A possible error trap would be =IF(cellref="-","",else whatever).
I was using place holders because the spreadsheet is being used for external reporting and I wanted to make it clear that they were intentionally blank. When I left the cells completely empty, I was getting the #DIV/0 error for having the formula refer to empty cells. So, the same initial question would still apply since I would like to eliminate the unattractive error message for the external reporting but I want to leave the formula there since it will be used inn future terms.
Please also help me: i use this formula: >> =SUM(IF(ISERROR(H449:H508),0,H449:H508)) and i believe that i have it right but it would just display a zero value ("0"). i tried to evaluate the function it would give me the right answer but just don't display in the worksheet. 1 LFKNA Cosmetics - Liquid Foundation - Nude Amber 195.00 195.00 :
GROSS AMOUNT Php- <<< not displaying the answer which is supposed to be "195.00".
- #N/A #N/A #N/A
- #N/A #N/A #N/A
- #N/A #N/A #N/A
The following =SUM(IF(ISERROR(H449:H508),0,H449:H508)). That is an array formula and needs to be entered using the key combination of CTRL, SHIFT, ENTER, not just ENTER. Try this non-array alternative (normally entered): =SUMIF(H449:H508,"<1E100").