margin of error: do not change point if within 5% of previous point?

[mattyu]

Hi,

I'm trying to make a graph where margin of error is taken into account. For example, I have two points on the graph, 99 and 100. Since the difference between 99 and 100 is within +-5% of 99, can I make it so that the graph will show both points as 99?

Thanks

[ChemistB]

Graphs in Excel for the most part simply chart data so it is the data setup that needs to be looked at. Can you upload (go Advanced>Manage Attachments) an example or dummy spreadsheet of how your spreadsheet is set up and how you would want it graphed?

[mattyu]

Here's a sample of data from my spreadsheet, I've also graphed it in it's current state. The 2nd and 3rd data points are within 5% of each other, so ideally I'd like for both of them to display as the same point in the graph.

Thanks

[ChemistB]

Using (B2-B1)/B1 *100 to calculate percent difference, it looks like your 1st and 2nd are within 5% of each other as are your 2nd and 3rd and 3rd and 4th. ?

Since you are using an X:Y Scatter chart, assuming we did chart 2 and 3 as 1 point, would it be a 2 or a 3? and would 4 then become a 3?

[mattyu]

Using (B2-B1)/B1 *100 to calculate percent difference, it looks like your 1st and 2nd are within 5% of each other as are your 2nd and 3rd and 3rd and 4th. ?

Since you are using an X:Y Scatter chart, assuming we did chart 2 and 3 as 1 point, would it be a 2 or a 3? and would 4 then become a 3?

Hi,

Sorry if I was unclear, in this case the graph would be a horizontal line (first 4 values within 5% of each other), with a value of 564798. The 5th point is unchanged

I've updated the sample with what I want the final product to look like (2nd graph).

[shg]

In C1 =B1

In C2 and down: =IF(ABS(B2/B1-1) < 5%, C1, B2)

Plot col C.

[mattyu]

In C1 =B1

In C2 and down: =IF(ABS(B2/B1-1) < 5%, C1, B2)

Plot col C.

For the =IF(ABS(B2/B1-1) < 5%, C1, B2) part, will I have to change the formula for each cell since I'm comparing the current value to the value directly before it?

[shg]

Formula refrences adjust automatically when copied.

[mattyu]

ah ok, works now, thanks a lot for the help