I have a worksheet with the following formula
Formula:
Please Login or Register to view this content.
'Washer 1'!C3 contains Numerical Data
How can I create a chart that will omit the #N/A cells, and only display the numerical data returned by the remaining cells?
I know for Conditional Formatting #N/A bust be searched as =ISERROR, but can't find how to make a chart ignore these cells.
Start your chart from row 2...
=SERIES(,Fails!$A$2:$A$821,Fails!$B$2:$B$821,1)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn
I know I have a lot to learn still with Excel - i used to think I was quite proficient.
Here is where my issue is. If I use Column D of the "Fails" worksheet to filter out the results, and omit N/A, but because I am using NA() as my FALSE in my IF Statements, in D24 it doesn't show the value of C24 because B24 is =ISERROR.
I need to be able to create a chart that displays the results of Washer 1 AND Washer 2, but omit any null value.
It doesn't seem to matter what I use for my FALSE: "#N/A", NA(), "", etc, and whether that is in B/C or D I don't seem to be able to get a chart that works.
if I use "#N/A" as my FALSE in Column B, then D24 displays C24 value, but the then chart displays dots along the 0 axis where it seems to interpret #N/A as 0.
I'm still not entirely sure what the objective is....
=IF(SUM(IF(ISERROR(B2:C2),1,0))=2,"Hide","")
copied down??
I agree that I am a bit confused.
When I opened your original sample file in post #1 and pull up the Select Data dialog to see how the series are defined, I notice that the "name" field for each series refers to rows 1 to 809. The only plotted point is for row 810. Was this your intention, or did Excel misunderstand what you wanted when you created the chart?
If I guess at what you want, I:
1) In "Fail," I clear the filter from Hide so I can see the entire table.
2) In "Failures," I bring up the select data dialog. For each data series:
2a) Enter "Washer#" in the name field.
2b) Enter A2:A821 in the X values field.
2c) Enter B2:B821 or C2:C821 in the Values field.
With those changes, the chart looks like what I would expect. The chart is ignoring the N/A error values, as expected, and showing the real values. I can use the Hide column in Fails to filter the missing values from the table.
Does that work?
Originally Posted by shg
Hello both, and thank you for your patience.
I have completed and attached the steps as suggested by MrShorty, but my chart appears to only show 7/5/17 - 23/5/17.
If I can explain what I am trying to achieve: Ultimately I will have 9 Washers, each with their own Sheet. "Fails" looks to these and pulls across all of the failed readings - anything over 5.0. From the "Fails" worksheet I want to produce a chart that displays the date and value of any of the failed readings, but not displaying any of the other washers for that day if they all passed - i.e. if washer 1 reads 10, washer 2 reads 15 I would expect to see 2 dots above the relevant date, but not see the 7 dots for the washers that passed on the same date.
This is why I was trying to use the "hide" column in "Fails" to allow me to see if there was ANY value in the 2 (ultimately 9) columns, so if in 12 months I only got 2 failed readings my chart would only display 2 dates, and 2 dots.
I am hoping that clarifies what I am trying to achieve in this workbook.
That might be messy. It will certainly LOOK messy. Why not bring the date over NEXT to the chart, showing ONLY washer name and fail dates for each washer separately?
Would that work for you?
See sheet 1.
=IFERROR(INDEX(Fails!$A:$A,AGGREGATE(15,6,ROW(Fails!$B$2:$B$821)/(NOT(ISERROR(Fails!$B$2:$B$821))),ROWS(S$2:S2))),"")
copied across and down.
Hi Glenn
I think with what the Powers That Be are wanting to achieve I think this is the best fit for them.
Hopefully, once live data is added, and with the use of CF Duplicates any trends will be easier to identify.
Thank you so much for the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks