# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  ic50 calculation

## irr

I am a research fellow. I have a dose response curve consisting of various data points. Now I would like a predict a possible value for given y value.

I have few x values
I have few y values corresponding to the x values.


How to calculate a x value from given y value based on the above data. I tried linear iterpolation, I am not getting good results.

Thank You

----------


## Barrie Davidson

How about this formula (assumes your known 'y' values are in A2:A5, your known 'x' values are in B2:B5, and your 'y' value you are calculating an 'x' value for is in A2)?




```
Please Login or Register  to view this content.
```

----------


## irr

Thanks Barrie,

 I tried and I am not succeful.
the x values are ( basically doses of a drug) 0, 0.0625,0.125,0.25,0.5,1 and y values (are the response in percentage for the above dose of drug) 
100
60.14824775
59.44357571
57.99234068
45.58768253
35.23732537

I would like to find the dose for the 50% response.

Logically it should fall betwee the doses 0.25 and 0.5

I would like to calculate the exact value please.

Thank you.

----------


## MSP77079

I suggest that you do the following:

+ Make a scatter plot (using Excel).
+ Click on one of the data point in the plot; this will select the data series.
+ Right-click and choose "Add Trendline ..."
+  In the options tab, choose "Show equation on chart"
+ Also, choose "Show R-squared"

You can change the type of trendline until you are happy with the fit.

----------


## irr

Thanks. I fitted a trend line and got a equation as well which basically 
y=97.34(x to the power of 0.4928)

what happens next. so how to go forward to find the x value for y of 50 please.

----------


## MSP77079

My math teacher predicted this would happen if students were allowed to use calculators.

y=97.34(x to the power of 0.4928)
y = 97.34 ( X ^ 0.4928)
y/97.34 = x ^ 0.4928
ln(y/97.34) = 0.4928 * ln(x)
ln (x) = [ln(y/97.34)]/0.4928

x = exp([ln(y/97.34)]/0.4928)

----------


## irr

Thank you very much. In our school and our days(1988-92), we were banned to use calculator. However we did not have the microsoft excel. I am grateful that I learnt to calculate the requrired values, in excel.

Could you may be able to solve one more of my problem. I mean I have same x values. but y values are not in a trend. then in my graph I get two x values for a y value. In that case if I WANT TO FIT A CURVE NONE OF THEM SEEM TO BE SUITABLE. 
for example  for the above x values and in a different experiment.
100
45.47929195
52.08651729
54.14265039
44.14178909
40.73028774

I know that my y value of 50 shoud fall in the first two doses(i.e 0 and0.0625) rather than(0.25-.05). since I am not able to get a correct curve even with transforming the data, I am unable to calculate the dose for 50% response.

Thanks in  advance. and sorry for the trouble.

----------


## MrShorty

If this is something you're going to be doing regularly as a research fellow, I would suggest finding resources on curve fitting or regression in Excel.

Personally, I prefer to use the LINEST function rather than chart trendlines because (a) the equation coefficients are in worksheet cells where I can use them in other calculations and (b) I can use a greater variety of equation forms.

Of course, one can also get into trouble using curve fitting incorrectly.  If you still have access to it, one of the classes I wish I had taken in college was a good statistics class.  If this is going to be a part of your career for a long time to come, I would suggest trying to get some understanding of the basic theory, algorithms, and pitfalls that are associated with this tool.

----------


## MSP77079

Hi Irr, 

I apologize for the wise-crack comment earlier.  It is always difficult to try to be funny in writing.

Adding on MrShorty's posting ... see if you have the Add-In named "Analysis ToolPak"  (look under Tools >> Add-Ins) and click it on if it is not already on.  This will give you some more possibilities in addition to what comes with standard Excel.

Agree in general that more Statistics is a good thing.  I would encourage thinking about Statistical Design of Experiments, though, vs. Mathematical Statistics or Probability.

But, in this case, you are only attempting to interpolate, right?  The dosages very far from 50 are therefore meaningless.  Try dropping off the end-points and just fitting the data nearer to the middle of the range.

----------


## irr

Hi, MSP77079
No need to apologize.  Yes I am trying to interpolate my dose to get 50% response. Yes I agree to drop some of the data. Then to get the best fit curve I have to use the first two y values in the above example. 
The problem I am facing mainly is the response of the drug which has biphasic some times triphasic type of response. (lower response at the lower concentration and as the dose increased I get higher response and further increase in the dose response goes down, I cannot help it because as it is response of the drug).

But I think, will use the first few data.

 One more thing as you suggested in the first example the equation was 
y=97.374x to the power of -0.4928 (I omited the minus sign, in error). However I tried calculating the value of x for y value of 50
x=exp[ln(97.374/50)/0.4928]

I got the value of 3.8 some thing, could you please confirm is it the same value you are getting as well. I should get aroud 0.257


Thank you very much for  your time.
Sincerely
Irr

----------


## MSP77079

If I throw away both end-points, I get y = 44.763(x^-0.1235)

If I throw away only the zero-point, I get y = 38.777(x^-0.1926)

----------


## irr

> If I throw away both end-points, I get y = 44.763(x^-0.1235)
> 
> If I throw away only the zero-point, I get y = 38.777(x^-0.1926)




Thanks. I guess I didnot make it clear for you.
x values                        
0
0.0625
0.125
0.25
0.5
1

corresponding
y values
100
60.14824775
59.44357571
57.99234068
45.58768253
35.23732537

What would be value of x for a value y=50 I am expecting around 0.27

2) an similarly  what would be x if y=50 in the following

x values                        
0
0.0625
0.125
0.25
0.5
1

y values
100
45.47929195
52.08651729
54.14265039
44.14178909
40.73028774


Thanks for your time

Irr

----------


## MSP77079

> If I throw away both end-points, I get y = 44.763(x^-0.1235)
> 
> If I throw away only the zero-point, I get y = 38.777(x^-0.1926)



The first gives x=EXP(LN(Y/38.777)/-0.1926)
when Y = 50; x = 0.267

The second gives x=EXP(LN(Y/44.763)/-0.1235)
when Y = 50; x = 0.408

----------


## irr

> The first gives x=EXP(LN(Y/38.777)/-0.1926)
> when Y = 50; x = 0.267
> 
> The second gives x=EXP(LN(Y/44.763)/-0.1235)
> when Y = 50; x = 0.408




That is really great. Thank you.  I am so happy to get these values. But for my future calcuation with other experiments could you please give me what  method you used, I mean did you use the trend lines power, log, or polynomial?

2) before calculating did you omited some of the data points, as I can see in the second calculation the y=50 is give x-.408  it can also be less than 0.0625 looking at my values. I suspect you omited the first value?


step 1)what I do is chart a line graph first with the x and y values
step 2) then fix a trend line, best fitting with good r value
step 3) find the equation
step 4) calclate the x value.with a given y value.

is it correct

Sincerely
Irshad

----------


## MSP77079

> did you use the trend lines power, log, or polynomial?



I used Power; but only because you had.  I did not attempt to get the best possible fit.





> before calculating did you omited some of the data points



Let's be sure we are using the same data.  This is what I set up the data table to look like:




```
Please Login or Register  to view this content.
```


For #1, I ignored the first and last point.  For #2, I ignored the first point only. 





> I suspect you omited the first value?



Correct.





> step 1)what I do is chart a line graph first with the x and y values



I use a scatter plot.

After that, I agree completely.  Except, as I wrote above, I did not try to get a good fit.

----------


## irr

Thanks. I got the same answers as you got for the first set of values. however I could not get the same values as you got  for the second set of experiment.
x            y
0	100
0.0625	45.47929195
0.125	52.08651729
0.25	54.14265039
0.5	44.14178909
1	40.73028774

Could you please show me. I hope this should be my last question.

sincerely
Irr

----------


## MSP77079

Hi Irr, 

Now I understand the earlier questions!  I never worked the second set of data.  I worked the first set of data twice!

This second set of data seems to have a maximum point, doesn't it?  So, I used a second order polynomial fit (just now) and got ...

y = -203.05x^2 + 109.13x + 40.239

Golly, I think that means I need to solve the quadratic equation to find X when y = 50.  Back in a minute.

how do you like the number: 0.1133491 ?
I guess there needs to be a pair ... looks like the other is 0.4241 with a lot of zeros behind it.

----------


## irr

The number 0.11133491 is fine for me. But when I present it I should have back to explain how I got the number. Could you please explain that for me. looking at the doses I was expecting the x value to be between 0 and o.0625.
Here response of the drug means the survival population of the cells after drug has been added. with 0 dose there is 100% survival. with 0.0625 dose there 44..%survival, with 0.125 dose 52% survival like that. Here survival of cells goes down and up and down again, but it is the behaviour of the drug.

So considering the first dip of the curve, the survival of cell 50% should fall with the dose 0 to 0.0625, I would have thought.  But please tell how get these numbers, means which curve or regression etc.

Thanks for you time.

Thank you.

----------


## MSP77079

There are 2 answers to this question:
+  inspect the graph
+  use the quadratic formula

When I graph these numbers:



```
Please Login or Register  to view this content.
```


I get a graph showing that at x = 0.0625, y is less than 50; at x = 0.125, y is greater than 50.  So, I expect the value of x giving y = 50 to be between 0.0625 and 0.125; and, somewhat closer to 0.125 than to 0.0625.

Fitting these data to a 2nd-order polynomial, I get
y = -203.05x2 + 109.13x + 40.239

The quadratic formula is devised to give 2 x values (possibly imaginery) that will make y = 0.  To find x values that make y = 50, I need to change the quadratic I am solving for.

50 = -203.05x^2 + 109.13x + 40.239
0 = -203.05x^2 + 109.13x - 9.761

So, 
x = ( -109 +/- sqrt(109^2 - 4*203*9.761)) / (2 * 203)

For the first solution:
x = ( -109.13 + SQRT(109.13^2 - 4*203.05*9.761)) / (2 * -203.05)
x = 0.1133491

For the second solution:
x = ( -109.13 - SQRT(109.13^2 - 4*203.05*9.761)) / (2 * -203.05)
x = 0.4241

Check my numbers, I might be starting with the wrong data, or making a mistake somewhere.

----------


## irr

Dear  MSP77079
That is great. I understand now how you derived this numbers.  I think my problem is solved. Thanks once again. Do you mind giving you email address just incase in future, I can contact you it is OK. my  email address is 


Thanks once again

----------


## irr

[QUOTE=irr]Dear  MSP77079
That is great. I understand now how you derived this numbers.  I think my problem is solved. Thanks once again. 



Thanks so much. 
Bye 
Irr

----------

