hi to all,
Hi Sanju,
It's a bit tricky to follow your explanation because there are no #N/A errors in cells A14 or A15 in the file linked to so I'm not clear which sheet has the problem, plus this is Google sheets, not Excel.
That said, assuming it's Sheet1 and you want the formula in column E to ignore cells in columns C & D when they are blank, you can use this formula:
=IF(OR(C2="",D2=""),"",D2-C2)
Mynda
first of all thanks a lot for replying to me post that is not A14 that is AI4. if any other formula done the same work i would like to have change in my sheet.
I gave you a solution above. Did you try it?
please read my question carefully. how it can be solution of that. how it will highlight the scoring.
This is the question I answered:
"my real problem is that when there is no planned date or actual date then it shows #N/A in that particular week.( please see AI4, AI5 and there are many more.) i want that this error should not be shown up if there are not any work its should show up the value 0 but don't want to use iferror function."
If you have another question, it wasn't clear, sorry. Please try rephrasing it in clear English with reference back to your file with sheet and cell references so I know exactly what you're referring to and what your desired result is.
Mynda
Hi,
You should use IFERROR.
The way you use -IFERROR seems wrong, what's the logic behind this?
first of all a lot of thanks for replying to my query. i have tried both in my sheet. but if i use if error it starts to show cell blank when there is no data. while i want 0 in place of blank cell. and when i use -if error, it starts to show calculations in positive because my formula already calculates in negative so -if error and my formula's negative value becomes positive. that's the problem.
I think you are making a mistake here.
IFERROR will not display blanks. IFERROR dislays whatever YOU want to display, like:
=IFERROR(oldformula,"") ' this displays a zero-length string, wrongly reffered to as blanks.
=IFERROR(oldformula,0) 'this displays a zero if the oldformula returns error.
=IFERROR(oldformula,"text message") 'this displays a text message if the oldformula returns error.
Why are you using the minus outside iferror?
If the formula returns a positive value, you need it to keep it positive, or you need to change it to negative?
If it's a negative result, you need to keep it negative, or you need to change to positive?
