Scatter Plot Chart not showing values from cell formula...

[Jimmypop]

Good day all

Stumped a bit on this one and not sure how to proceed...

So I have an item, Item1, that has a target set (in this case 8 (Cell C2)). Only 3 of the 8 are available (Value in B2). I calculate the percentage of this in D2 WHICH IS 38%. This is then rounded to the nearest 10 which is 40%. Then to get my coordinates on my Scatter Graph (made to look like a target) I have TargetPNT where X will always be 5 but Y will change depending on the value in D3 (which as per my example is 40%). This means that 40% is equal to 4 so my xy scatter coordinates will be 5 and 7.5.

No my issue is that if I type the 7.50 in manually then my data point appears on my graph, however if I use a formula to determine the y coordinate then my graph does not show the data point. What am I doing wrong and is there a way to do this?

Book1.xlsx
	A	B	C	D	E	F	G	H
1	Item	Actual	Target	Percentage Complete
2	Item1	3	8	38%		Bulls Eye	4.48	4.98
3	TargetPNT	5	7.50	40%		1	5	8.75
4						2	5	8.3
5						3	5	7.95
6						4	5	7.5
7						5	5	7.1
8						6	5	6.7
9						7	5	6.3
10						8	5	5.8
11						9	5	5
Sheet1

Cell Formulas
Range	Formula
D2	D2	=B2/C2
C3	C3	=IF(D3>0.39,"7.50","0")
D3	D3	=ROUND(D2,1)

 

[felixstraube]

I do not understand what you are doing or trying to do.
How is 40% equal to 4? How does that produce a 7.5?

Anyway in your IF function you are returning a string (text) not a number. So if you are plotting X = B3, Y = C3 what won't work.
You'll have to delete the quotes like this:

=IF(D3>0.39, 7.50, 0)

 

[Jimmypop]

Hi @felixstraube

If the result is 40%, then because 40% starts with a 4, I need the coordinates from the row with the 4 in the table. If you have a look at my picture there is a column with Bulls Eye then 1, 2, 3, 4 etc underneath that. Next to 4 is the coordinates for the 4th ring of the target which is x=5 and y=7.50.

So lets say my result is 34%...this will round to 30% and the new coordinates for the scatter plot chart will be x=5 and y=7.95 meaning that the data point will then fall into the 3rd ring of the target meaning the person is moving farther away from a bulls eye.

Your suggestion made it work, thanks very much. I was a doofus for inserting the quotation marks. December vacay was too long I think.

 

[felixstraube]

Glad you could make it work. Thanks for the feedback