LinkBack URL
About LinkBacks
I was wondering how I can make my line chart disregard zero values?
I don't mean empty cells that I can make the table disregard by editing the data!
Thank you!
I was wondering how I can make my line chart disregard zero values?
I don't mean empty cells that I can make the table disregard by editing the data!
Thank you!
Hi again.
Is the zero an entry, or a calc?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi! Great to see you again 😊
It is a calculation! It’s basically a summary of a chart I made.
If you force the calc to return ERROR, the chart will ignore it
I'm not exactly sure how to do that so I have attached the excel sheet.
I created a dynamic dropdrop list for the chart that is connected to the summary sheet, but the formula makes the empty cell come out as value 0.
If you see on the chart, it makes a drop to 0...
Is there a way I can make it disregard the 0 and connect from 1 to 3 right away?
Thank you
you need your formula in column N to return #N/A rather than zero when the cell in the lookup range is empty.
Quickest way to do this is select the lookup range, CTRL+G > Special Cells > Blanks.
type =NA()
use CTRL+ENTER to populate all cells.
Thank you! It worked
Seeing as this will be an ongoing file, it might be better to have that built into the formula, rather than manually changing the 0's (Andy's suggests are always great though). To that end, maybe this will work for you as well>
N2=IF(INDEX($B$2:$J$73,MATCH(M2,$A$2:$A$73,0),MATCH($N$1,$B$1:$J$1,0))=0,#N/A,INDEX($B$2:$J$73,MATCH(M2,$A$2:$A$73,0),MATCH($N$1,$B$1:$J$1,0)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks