Error bars with variable error

[WhiteWaterMatt]

Hi All,

I've got a sheet on which we measure and log the quality of water on a day to day basis. The percentage error for the machine depends on the reading that was given (see below for the specifics). I'm not sure how to get this information into error bars, and have been told by the powers that be, that they won't accept the chart unless it has error bars on it.

+/-10% error at 2.25 ppm
+/-20% error at 0.75 ppm
+/-30% error at 0.20 ppm

I believe that in order to do this, i'll need a calculation to work out the percentage error dependant on the value that's been entered, and then the entire thing put into an 'if' statement.

Any ideas?

Thanks!

[MrShorty]

Rather than an IF() function, I would probably use a lookup table for the error values.

Please Login or Register  to view this content.

Then, with concentration in, say, E2, F2 could be =vlookup(E2,$A$2:$B$4,2,true) (help file for the vlookup() function https://support.office.com/en-us/art...8-93a18ad188a1 ). The error bars can then be added to the chart series using the "custom error" bars option.

[WhiteWaterMatt]

So if the data that i need to calculate an error value for and then put into a vlookup is in column 'C' from cell 2 downwards, how would i go about this? Would i need to create two more columns for the upper and lower percentage error?

[WhiteWaterMatt]

forgot to take the passwords from the sheet(s)

the relevant chart is on the 'Ammonia-Turbidity Chart' Sheet

[MrShorty]

how would i go about this? Would i need to create two more columns for the upper and lower percentage error?

If I understand this question, I would say that it is probably not necessary. The main reason I can think of to include separate upper and lower per cent errors is if you would like the option in the future of having different upper and lower per cent errors (+10%/-20%). Only you would know if this is a real possibility.

Is there a specific part of creating a lookup table then referring to it in a lookup function that you are having trouble with? I have thought that the help file was fairly well written. There are numerous tutorials and examples on the net. If I understood exactly what you were stuck on, it would be easier to speak specifically to the part you are having trouble with. The attached example shows a very simple lookup table and function.