Can't create an X-axis on a scatter plot

Zach_

Hi there.

I want to make a scatter plot that graphs points based on the following two pieces of data: impact score and design hours. My issue is that I can't get the X-axis to display the content in my design hours column.

Here is a picture of my scatter plot. The dark blue points at the bottom of the Y-axis shouldn't be there; those are my design hours values that should make up the X-axis. If it's not clear what I mean, I would like the X-axis to display the range of my design hours.

Here is how I have things arranged in my sheet:

What am I missing?

Thank you for the help.

Gia Thinh

Here is my solution.

In the sheet, the yellow columns are the original ones and the green columns are the newly added ones

ID : Text number column, prepopulated as much as you want.

Design Hours2. : is a distinct and sorted column of the Design Hours2 column in the yellow zone, with a column formula:
=IFERROR(SMALL(DISTINCT(COLLECT([Design Hours2]:[Design Hours2], [Design Hours2]:[Design Hours2], <>"")), ID@row), "")

Scatter Unstarted. : column formula:
=IFERROR(INDEX(COLLECT([Scatter Unstarted]:[Scatter Unstarted], [Scatter Unstarted]:[Scatter Unstarted], @cell <> ""), MATCH([Design Hours2.]@row, COLLECT([Design Hours2]:[Design Hours2], [Scatter Unstarted]:[Scatter Unstarted], @cell <> ""), 0)), "")

Scatter In Process. : column formula
=IFERROR(INDEX(COLLECT([Scatter In Process]:[Scatter In Process], [Scatter In Process]:[Scatter In Process], @cell <> ""), MATCH([Design Hours2.]@row, COLLECT([Design Hours2]:[Design Hours2], [Scatter In Process]:[Scatter In Process], @cell <> ""), 0)), "")

Scatter Completed. : column formula
=IFERROR(INDEX(COLLECT([Scatter Completed]:[Scatter Completed], [Scatter Completed]:[Scatter Completed], @cell <> ""), MATCH([Design Hours2.]@row, COLLECT([Design Hours2]:[Design Hours2], [Scatter Completed]:[Scatter Completed], @cell <> ""), 0)), "")

I hope it helps!

Gia Thinh

Hi there,

Try to move the Design Hour to the most left and make it as series labels as shown in the screenshots below.

Hope it works for you.

Zach_

Thank you, @Gia Thinh. That created the X-axis, just like I wanted.

But the way that the chart is plotting the points is still undesirable. Every scatter plot I've ever seen charts coordinates with a single point, where the X and Y values intersect. Having two points for each item, one for the X value and one for the Y value, makes the chart confusing and unattractive. Is there a way to change this?

This seems possible. Look at the image included in this article published by Smartsheet, titled "Scatter Charts."

Gia Thinh

Try to disable the Render option in the Horizontal Axis!

Zach_

@Gia Thinh Thank you for the response.

That solved the "unattractive" problem (largely), but the "confusing" problem is still there. Why wouldn't the chart arrange the figures in the X-axis from least to greatest, then plot the points accordingly? As is, the chart is not useful; it doesn't enable the viewer to discern any kind of trend or to make decisions based on impact vs. effort.

Zach_

I'm still experimenting, trying to figure this out. I created side-by-side scatter charts. From what I can figure out, I can either have the

LEFT VERSION (traditional scatter plot, but without the ability to indicate the status of the projects represented by the points)

OR

RIGHT VERSION (scatter plot with points color coded to indicate status, but with the points randomly arranged due to X-axis being out of numerical order)

I don't know how Smartsheet managed to color code their points and make the chart display like my left version, as depicted in the article linked above. It seems like this should be possible.

Zach_

@Gia Thinh, I'd like a combination of the two side-by-side charts I posted above. I want the X-axis to show a range, in numerical order, and I want the points to be colored coded based on a status.

Here's a quick mockup I made in Photoshop.

Gia Thinh

Is this what you want?

Zach_

@Gia Thinh, that's very close to what I had in mind, yes. Can you please share how you did it?

Gia Thinh

Here is my solution.

In the sheet, the yellow columns are the original ones and the green columns are the newly added ones

ID : Text number column, prepopulated as much as you want.

Design Hours2. : is a distinct and sorted column of the Design Hours2 column in the yellow zone, with a column formula:
=IFERROR(SMALL(DISTINCT(COLLECT([Design Hours2]:[Design Hours2], [Design Hours2]:[Design Hours2], <>"")), ID@row), "")

Scatter Unstarted. : column formula:
=IFERROR(INDEX(COLLECT([Scatter Unstarted]:[Scatter Unstarted], [Scatter Unstarted]:[Scatter Unstarted], @cell <> ""), MATCH([Design Hours2.]@row, COLLECT([Design Hours2]:[Design Hours2], [Scatter Unstarted]:[Scatter Unstarted], @cell <> ""), 0)), "")

Scatter In Process. : column formula
=IFERROR(INDEX(COLLECT([Scatter In Process]:[Scatter In Process], [Scatter In Process]:[Scatter In Process], @cell <> ""), MATCH([Design Hours2.]@row, COLLECT([Design Hours2]:[Design Hours2], [Scatter In Process]:[Scatter In Process], @cell <> ""), 0)), "")

Scatter Completed. : column formula
=IFERROR(INDEX(COLLECT([Scatter Completed]:[Scatter Completed], [Scatter Completed]:[Scatter Completed], @cell <> ""), MATCH([Design Hours2.]@row, COLLECT([Design Hours2]:[Design Hours2], [Scatter Completed]:[Scatter Completed], @cell <> ""), 0)), "")

I hope it helps!

Zach_

Thank you, @Gia Thinh!

Gia Thinh

Zach,

This is an interesting problem and I'd like to solve it.

I'm happy to help!