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.

I think I'm understanding. I think I got confused because I thought C in the first post was concentration, then you said that M was concentration. In any case, I'm sure you know enough algebra to tell if the formula you entered is correct for what you are doing. If it looks wrong, do the algebra and put in the correct formula.

So, it looks like you should have 4 columns at this point: absorbance (y), concentration (x), ln(x), 1/x. To plot against time, add another column (probably should insert to the left of ln(x) and 1/x) and enter the times. Then select the three columns (time, ln(x) and 1/x) and use the insert chart command (not sure where it will be on the ribbon -- it's in the insert menu on my older version).

There are a few ways to get r^2. Easiest might be to use the trendline feature on the chart. Add a trendline for each series, then tell it to show the r^2 value.
You can also get the r^2 value from the LINEST function if you tell it to output the supplemental statistics. I also know there is a specific function for r^2, but I can't remember what it is called (PEARSON() maybe or R2()). You should be able to find it in Excel help searching for r squared value.

Yes, this is all good. How do I get excel to solve my equation to give me my x values? I don't know how to enter my y values and my equation to get excel to solve it. I have no idea how to work excel.

It looked like from the first post, you already know how to do the algebra to solve for x at a given y, so I'm not sure what you are asking. Excel really doesn't do algebra for us. We do the algebra, then put the formula into Excel. So you take the formula for x that you show in post 15, then put it into excel, substituting in for y the cell reference where you put the value for y. If I followed correctly, you had put the y's into column A, and we were putting the x's into column B, so B1 should be =(A1-.0313)/67880, then copy down.

Once I have B1 as that, and my y's in column A, where do I go in excel for it to solve to give me my x's?

???? Isn't that the formula for x? Once that formula is put into B1, it should calculate x without any more input from you.

It is. I have them entered, but I do not understand what it is that I need to do, or where I need to go to see my calculated x values.

So is column B showing the formula instead of the values?

Yes, it is. Where is it exactly that I need to enter the formula? Do I simply put it in a cell, or is there a specific process to enter it?

Two possible reasons:

1) Are you including the "=" at the front of the formula? The equals sign tells Excel this is a formula you are entering. Without it, Excel assumes you are entering a text string and just shows the text string.

2) There is toggle option in the main options dialog for showing formulas rather than values. Make sure this is selected correctly.

Yes, I am entering it exactly as:
B1=(A1-.0313)/67880

Do I select the formula button on the toolbar to enter it? Or do I just paste it into the cell? If it is the formula tab, which do I select to enter it? I am confused on this point.

You are including the B1 before the equals sign? Leave the B1 out.

I will leave the B1 out. Do I type it in plain text into the cell?

I have entered it directly into the cell and gotten an answer. Is there any way to make it do this for all my cells rather than by hand?

Yes, that is all there is to it.

So, is there not a short cut to getting excel to solve for all of my values with my given function?

I'm not sure what you are asking. Having entered the formula into one cell, it should be a simple copy and paste to put the formula into the rest of the column.
Select cell B1
Press ctrl-c to copy to the clipboard
select cells B2 through B??
press ctrl-v to paste the formula.

Will this change the A value so that it will calculate it for the different ones?

repost, disregard.

If you look at the formula I posted in post 6, some of the references had $ in them. The $ tell Excel to make that part of the reference absolute, meaning that it doesn't change with a copy/paste. The reference without the $ will change relative to the cell pasted into. So, the A1 in the formula entered in B1 will change to A2 when pasted in B2 and so on. So, if you've entered all of your y values into column A, copying the formula down in column B should give the value of x corresponding to the y value entered in column A.

Okay, so I attempted to do this, paste:
=(A1-.0313)/67880
all the way down. It gives me the same value in my B column for every A value. It does not seem to change specific to what A column it is entered next to.

Check your calculation options (options dialog) and make sure it is set to automatic.

And, my apologies, but I've got to go. Good luck. When you get this little exercise figured out, you should have a good intro to spreadsheets in general, and Excel specifically.

Okay, I will try and figure it out. If not, I suppose I can change the A value by hand. Thank you so much for helping me figure this out. I am very appreciative.