No announcement yet.

[help] Excel Graph assistance needed

  • Filter
  • Time
  • Show
Clear All
new posts

  • [help] Excel Graph assistance needed

    Buret Correction.jpg

    Ok, for my Analytical Chemistry class I needed to make the graph in the picture.
    Now, I would like to enter a value from 0-50 and it give me the correction from the "Y-value" on the left.
    Is that possible and how would I do this?
    Last edited by amu107.9; 09-11-2011, 05:00 PM.

  • #2
    Re: [help] Excel Graph assistance needed

    There are two ways you could go about doing this.

    1: Plot a trendline from the graph and do a little plug-and-chug with the volume you are interested in.
    2: Use a series of excel functions so you are given a value from your data set for the specific volume you input. (I have used an ugly combination of CELL, OFFSET, and MATCH to do a similar task)

    The problem with the first method is that you do not have large enough data set to give you a curve with any real meaning. The second method is also not really useful here since you would need to have values for EVERY volume you are interested in. Unless you obtain a significant number of data points, there is really no method you can use to find the value you would have at 45mL, for instance. These methods work very well, though, when you have a spectrum or something that has a few thousand entries. If I am not understanding your question correctly, let me know.

    I have been told many times by my professors to check out the book "Excel for Chemists" whenever I have a question about anything Excel. Might be worth a shot to see if you have access to a copy for future reference.


    • #3
      Re: [help] Excel Graph assistance needed

      I think you can make a trend line (had to type over the numbers to see it correctly), but as you can see from you data if you plot the data that there is an consistent error which increases with volume. You can also see that in your second try the 20mL value deviates from the trend as is the 50mL of the first. You should repeat these two and maybe some more in between to get a better trend line. Then you can add a trend line and use the formula to calculate the "Y" value.

      If I plot your first try (do NO include 0 and I left out 50mL since I think it is a wrong measurement) then you get the following trendline: y=0.0017x+9.9429 (r= 0.9727). Use a cell to fill in the formula and you can now calculate the "Y". You fill in the formula like this (0.0017*D1+9.9429), D3 refers to a cell where you fill in the "x" value!

      I used this set as an example:
      10 9.9624
      20 9.9715
      30 9.9961
      40 10.0105

      But explain what is the purpose to me some more, since I have no Idea why you are trying this!
      Last edited by sapientiea; 09-15-2011, 08:25 AM.


      • #4
        Re: [help] Excel Graph assistance needed

        The density of water is 1 gram/mL. So, 10 mLs of water should weigh 10 grams at 20*C. Unfortunately, the buret doesn't measure this exactly and you can with confidence measure the correction at 1-mL increments by taking the exact mass/volume at 10-mL increments.

        I am creating a chart to correct for the inherent errors introduced by the manufacturer of the buret. This is done by weighing an empty and dry 125-mL Erlenmeyer flask and dispensing a volume of distilled water in 10-mL increments into the Erlenmeyer flask. At each increment you re-weigh the flask and record the mass, subtracting the previously recorded mass from the current mass reading.

        This is repeated atleast twice and the values are averaged for each 10-mL increment. This value is then subtracted from 10-mL and the diference is the correction the graph plots at each 1-mL interval.

        I can say with confidence that the scenario I described it precisely what I did. Unfortunately, I haven't sleep in the last two days as I am studying for an exam tomorrow. So. while it makes total sense to me right now it in actuality may not at all.


        Agapito aka HaveANIceDay

        P.S. The density of water must also be corrected since the room was @ 24*C and not 20*C.


        • #5
          Re: [help] Excel Graph assistance needed

          Then you can use the trend-line as I explained just use the deviation averages, but do not include the "0" value since that is not a measurement.....

 ; Also check this out, since that is what my students do wrong sometimes. BTW your error is really small! What is the given error on the Buret?


          • #6
            Re: [help] Excel Graph assistance needed

            According to my professor it's a Class B buret with a tolerance of 0.1 mL.
            So my measurements have to wrong or I am extremely consistent...(but gonna go with wrong :( )
            I am familiar with how to read a minuscus, but perhaps I am not reading it correctly.

            (image was retrieved from

            So if you look at the picture. I am recording the volume when the minuscus touches the top of the line and not the bottom of the line.
            Since that small amount I think was about 0.02mL of volume. Is that correct?
            Attached Files


            • #7
              Re: [help] Excel Graph assistance needed

              With a concave meniscus you have to use the bottom part of the water in this case 13.3mL (but it's hard to see for me correctly). I have set a red line in the picture as to where you have to read it buret7.gif. I think your doing it correct.

              Yeah a tolerance of 0.1mL on such a big Buret is normal (guess is 50mL, 0.2%error). Fact that your error comes out lower is good, but it is also good that you seem to be consistent. Since your excel graph shows a higher error with increasing volume, it either suggests that the Buret has an error that increases with volume OR that you make a consistent error. For example, if you would use a Buret of 10mL to do this experiment than you would have to multiply the error by 5 if measuring 50mL. Same volume out of a 50mL Buret would only introduce the error once! Same goes for your errors or the error in the buret.

              btw errors given on the Buret and other calibrated glassware are maximum values, due to temperature, manufacturing mistakes and variations. If you would place a Buret in the fridge (4oC) and the add water of 20oC it would have a different reading than if the Buret was 20oC. Sometimes people in the lab also put this calibrated glassware into industrial dishwashers, which most of them are not designed for and can have a volume change as a result.




              TeamSpeak 3 Server




              Twitter Feed