Thread: Using Excel as variable solver

Hello! I am new to using excel for analyzing data. I have a calibration curve from the first part of my experiment. It is derived from absorbance vs concentration data points to give me my equation for Beer's Law. It is in the form of A=mC+b, rearranging to solve for C, I have C=(A-b)/M

I have two sets of data points on absorbance vs time. I need to use excel to plug in my absorbance data to convert it to concentration so I can figure out the order of the reaction.

How can I get excel to do this for me? I have 200 data points each in the abs v time tables, so by hand wouldn't be efficient. I don't have the initial absorbance vs concentration data, just the line. I am using excel '04 on a mac.

Help is much appreciated!

It's been way too long since I dealt with reaction rate data like that, so I can't remember the specifics. If I remember correctly, it essentially boiled down to a curve fitting problem. From the coefficients and/or exponents in the resulting equation, you could deduce the reaction order.

As noted, Solver can be a useful tool for this sort of thing. As I said, I can't remember the specifics, but if the equation turns out to be linear (in the linear algebra sense, so any polynomial is a "linear" equation), you would also be able to use the LINEST function to get at the equation.

I'm sure you can get Excel to do your analysis. If you need more help, help us to understand the math behind the analysis, and we can help you put it into Excel.

Okay. So I have my equation from Abs vs Conc to give me an equation that I have as y=67880x+.0313. I have readings off of a LabQuest for 200 data points that give me the different absorbance points, my y values in the equation. I need to use excel to solve for my x values, or concentrations. I am just not sure how to enter this data into excel and have it solve for me. My lab ta never provided instruction.

I suppose I should add that I will obtain my concentration, or M, then will graph M vs T, ln(M) vs T, and 1/M vs T. I am unsure on how to get these entered, too.

You gave the equation above [C=(A-b)/M], so I'm not sure what you are asking. It should be as easy as entering all the A's into a column. Enter the formula into the adjacent column, then copy the formula down so that it calculates C for each A.
So, if the absorbances are in A1 through A200, B1=(A1-$D$1)/$E$1 where D1 and E1 contain b and m from the calibration equation, then copy down.

I misunderstood, I thought C was concentration. Whatever variable you solve for, it should be as simple as entering that equation into column B. =ln(M) and =1/M would easily be entered as formulas into columns C and D to calculate those quantities.

Okay, So in A, I would enter my Absorbance values, in B, I enter the equation you have given me. Do I need to copy and paste it into every cell to correspond to my A values? I suppose this question goes for my b and m values in D and E. I am just not well aware of how excel works, I'm sorry. Thanks for your help so far.

Do I need to copy and paste it into every cell to correspond to my A values?

Only if you want the quantity(ies) calculate for each absorbance. If you don't copy it down, then it will only calculate the formula for the one absorbance. I assumed from your post that you wanted these quantities calculated for each absorbance.

Yes, I need it for each. Is there a short cut to paste it into every cell at once? Also, will it give me my values in a column?

I'm sure there are a lot of different ways to do it. Being a bit old-fashioned, I find I can often navigate faster with the keyboard, so I tend to use ctrl-C to copy the one cell into the clipboard, then use shift-ctrl-pagedown to select all of the cells I want, the ctrl-v to paste. I see co-workers who prefer to use a mouse to select the desired paste range, and some who even know how to use the little "plus sign" that appears at the bottom corner of a cell to drag and fill the formula down.

Okay. I have it entered into my cells. I am not sure where to go from here. My A column has all of my concentrations/M. My B column has the equation you provided and my C and D columns my b and m. How do I get excel to solve this for me? I don't know the steps to take.

I have to apologize, but I'm not sure either. I haven't followed very well what you are doing, so I'm not certain the formula I gave you is right, and you haven't explained what you need to do to analyze these. What is the next step? Do you need to curve fit these data somehow? Or plot them?

Okay, let me restart. I have made a calibration curve to fit Beer's law for absorbance. I have a best fit line from my calibration curve, which gives me the equation y=67880x+.0313. I have a list of y values that I need to plug in to y=67880x+.0313 to get my x values. I then need to plot my x values as x vs time, ln(x) vs time, and 1/x vs time. Which ever is most linear, r^2 value closest to one, gives me the reaction order. I have just solved for x with x=(y-.0313)/67880.

So, I need excel to take my y values to give me my x values. Then take those x values and plot them against time.

I thank you greatly for your time and patience with me.