I have a dataset with has individual values e.g. 50
But once it gets over certain value e.g. 70, the value is displayed >70.
Which is fine, but Excel does not allow me to plot as it does not see the table column as number because of it.
So my pivot table is showing 0. And my Power query gives me errors for cells with symbols when changing data type to Number.
How can plot the data?
How/where do you want them plotted?
Rory
A line chart. For example: Values Y axis, and Specimen on X axisOriginally Posted by rorya
I mean where on the chart would >70 appear? It's not a number so you need to give it a numeric value for it to be plotted at.
I have recreated a small approximation. Weird, in the original table from PQ even values without the symbol were showing as 0 in the pivot table. Maybe there were some hidden characters I needed to clean up.
Anyway, in this instance, the >50 are showing as 0 on the chart. If they would show as 50 or slightly above in the chart would be awesome/
I'm still not sure how you intend to solve the problem.
As rory notes, any value >50 or >70 is a text string, and Excel charts treat text strings as the value 0. A naive, "ignore all the unsaid stuff" solution is to replace the >50 type values with a number. I can enter 51 into D9 and D10 of your sample file, refresh the pivot table, and both charts show the 51 for those two categories. Problem solved.
However, I may have just created a new problem, because >50 and 51 can mean very different things. I may have got a number to show up in the chart, but that number may obscure the real meaning that "this is above a threshold value of 50." If I can somehow convey to my readers that "the number 51 is a placeholder for the idea of >50," then maybe this is okay. If I cannot trust my readers to be able to grasp that, then I have obscured the meaning and made this harder for my readers to understand.
What do you think? Will it be adequate to simply replace ">xx" values with some number? Or are you needing something more elaborate so you can retain the meaning of "above threshold" for these values?
@MrShorty
Yeah, I figured it was most likely a futile exercise. But I thought to ask just in case there was a way I wasn't aware of.
I totally get what your saying. Maybe like you said turning them into 71 and maybe have those data points in specific colour (example orange). And in the legend or labels mention that orange is above 70
I have thought of a possibility using number formatting:
1) Replace the >50 text in D9 and D10 with the number 50 (or other suitable number). Refresh the pivot table.
2) Add number format to D22:D26 (and D7:D11, if desired) like [=50]">"General;General (if you are not familiar with number format codes, see here: https://support.microsoft.com/en-us/...rs=en-us&ad=us pay particular attention to the part about having conditions in the number format code)
That would allow you to have the number 50 (or whatever you decide you want) in those cells and they will be treated as numbers, but also display the number 50 with a greater than character in front of it to indicate that the number really means "above threshold." Would that work for you?
That's incredible what you did there. Excellent work! Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks