Log In
Register
Home
Active Member
October 10, 2019
Offline
hi to all,
July 16, 2010
Offline
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
Active Member
October 10, 2019
Offline
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.
July 16, 2010
Offline
I gave you a solution above. Did you try it?
Active Member
October 10, 2019
Offline
please read my question carefully. how it can be solution of that. how it will highlight the scoring.
July 16, 2010
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi,
You should use IFERROR.
The way you use -IFERROR seems wrong, what's the logic behind this?
Active Member
October 10, 2019
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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?
1 Guest(s)
