Free Microsoft Excel 2013 Quick Reference

# ic50 calculation

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

## Related Results

### Ic50 calculation

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

### Showing the IC50 value on a graph.

Hello. Ive been lurking on here for a while and getting help with excel, now i need some answers!

Im doing a competition assay, and would like to show the IC50 value on the graph. i know how to calculate the IC50, but im not sure how to put it on my graph.

Heres an example of what i would like, a sigmoidal curve and a "box" showing my IC50 value...any suggestions?

### Locking and hiding the formula calculation area of worksheets

I have a worksheet in which lower rows contain a lot of table and workings for the formulas used in top rows.
It looks rough and I want that the user can not go or see the lower rows after row82 and neither go beyond column L.
So the scroll area should be A1:L82.

Bearing in mind that this hidden area has a lot of vital info on which the working area calculations depend.
I tried couple of VB codes from this forum but nothing seems to work for me.

someone help!

### Random Cells Not Calculating?

I'm making a spreadsheet for someone I don't know, she is to enter some data which will give her statistics.

All was well and working very good.. and then;

I wanted to make it so the "omits adjacent cells" error wouldn't show, so i set it to not check for errors.

I also wanted to make it so she could only enter things into certain cells to save her wiping formulas, so i selected all the cells i wanted to unlock with intentions of protecting it later.

I also wanted to make it so she can't see the formulas, so i selected everything and checked the "Hidden" box, assuming it will only bother hiding formulas from the formula box... (this is where I think I might have done something wrong)

I went on to remove all the dummy data and realised i had left one cell displaying a zero (i'd rather it display nothing) so I change the formula from =Leads!A1 to =IF(Leads!A1"",Leads!A1,"") - normally this would work fine, but instead it just displayed the formula.

I went back and unticked hidden for everything and locked all the cells again, and its still not calculating. even when i put something in "Leads!A1" that cell does nothing... I promised I'd have this to her tomorrow And I'm worried I've screwed something up I've also tried exiting & opening it back up.

The other cells seem fine, but I'm scared to mess around too much.

### Calculating average from different workbooks

Dear Members,
Every month I have to calculate averages from a workbook that is updated on a daily basis.
C:/User/Me/Reports2012/June 2012/
This folder above has all the reports for this month except Fridays and I have folders from Jan to Dec, each folder contains reports for that particular month except for Friday.
The files are named DailyReport DDMMYY.xlsx
I want to calculate the average of
Columns: D, F, H, J, L, N, Q, T, V, X, Z, AB, AD, AF, AH, AJ, AL, AM, AQ, AT, AW, AZ, BC, BF and
Rows: 9, 17, 25, 33, 41, 49, 57, 73, 81, 89, 97, 106, 114, 122
I mean is I want to calculate average of D9, D17..... D122
F9, F17, ... F122
Until BF9, BF 17, .... BF 122.
From all the workbooks’ Sheet2
Is it possible or should I just copy and paste the information onto one worksheet and then calculate the average?

### Use two different calculations based on ID number in column

I have a list in Excel with several hundred 8 digit ID numbers in column D. Column C contains the total number of hours the student employee worked. Column E needs to calculate using two different equations depending on if the ID number in column D is a manager's. If the ID number is an employee the total hours worked is divided by 8. IF the ID number represents a manager the total hours worked is divided by 4. I've attempted this by using an array for the manager ID list and also using VLookup without success.

In essence I need to loop through column D, check which type of ID number is present, and then enter the correct formula in column E.

### Form that calculates and shows the total price of the products selected in an order

Hi,
I use Excel 2003 and I have some experience with macros but I am totally new to forms.

I would like some guides to design a form that calculates and shows the total price of the products selected in an order as follows:

Each product is identified by:
1) a label,
2) a checkbox,
3) a listbox for the Quantity,
4) a textbox for the Price.

- The product unit prices used in the form are stored in a table.
- When a product is checked the form shows "1" in the Quantity listbox and the calculated product price (quantity * product unit price) in the Price textbox.
- When a product is unchecked the form clears both Quantity listbox and Price textbox.
- If the user modifies the Quantity of a checked product the form shows the new calculated price (quantity * product unit price) in the Price textbox.
- The user can also modify the calculated price by entering a value in the Price textbox.

The total price of the order is shown in a textbox adding the PRICE textboxes of all procucts.
By pressing a command button "PLACE ORDER" the user records all fields of the order in a table.
By pressing a command button "CLEAR FORM" the user clears the form.

Suggestions will be greatly appreciated.

Regards,
Didier

### VBA macro using calculated value out as new input in.

Hi

I am trying to write a VBA macro that will mimic what i have already done in an excel worksheet i have made. The excel worksheet uses inputs for a heat exchanger and finds the temperature as it leaves the heat exchanger. This temperature is then the new input for the second line of the worksheet and it changes quite a few of the values in the sheet. I have the excel worksheet working where you just drage the row down and it does it automaticly, but i would like to have a VBA macro that all the values can be input in with the number of cycles until the heat exchanger equalizes out.

I am able to get the macro to work when it calculates just the first cycle, all the equations work my variables are ok for just one time. What i don't know how to do is make my program realize that it has to use the calculated temperature out of the heat exchanger as my new input for the heat exchanger. I know how to do it once so it is more of a syntax or understanding of a loop or an array for the values that are changing.

Thanks any help is appreciated.

### Changing VBA code to work with calculate

I use this code to adjust Y axis using min and max functions in the cells. I have to click on the cells to activate the change. How can I change the code to work with "Calculate"? Also, what code do I need to work for secondary Y axis?
```
VB:
Range)
Case "\$M\$2"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "\$M\$3"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case Else
End Select
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Thanks.

### Calculate totals based on three criteria

Hi,

I'm trying to calculate a series of numbers based on three different criterion. My problem seems to be that when I reference the first "current month" cell it sees it as a string instead of a date. Here's what I'm trying to do:

Calculate numbers that belong to a certain employee id.
Calculate those numbers between the 1st and the 15th of the month, and then again (in a different cell) from the 16th to the EOM. Let's use emplyee 4405 as an example.

Ex: Sum the earned column if the employee id is 4405 and the date is between the 1st and the 15th.

My "current month" start date is auto-populated from another sheet in the workbook and the subsequent cells are simply using the +1 copy. I'm just using it on this sheet to act as a reference point for my codes. All of the other data is retrieved from a remote database and refreshed when the workbook is opened or the dates in the other worksheets are changed:

Date Sold Earned Employee ID Month 7/1/2012 4.45 4405 7/1/2012 7/4/2012 5.27 4225 7/2/2012 7/5/2012 14.75 1522 7/3/2012 7/8/2012 86.32 4405 etc. down to 31 7/12/2012 14.15 4405 7/15/2012 32.60 1522 7/18/2012 88.90 2335 7/24/2012 52.15 4405 7/30/2012 14.20 4423

Thanks very much for any help on this!

### Hourly Calculation in Excel 2010

Hi, I'm playing this game and would like to calculate which buildings are worth my time

Following things are from a game and I want to calculate what's the best things to use

Buildings make cash for me

Building 1 makes 80 coins every 2 min
Building 2 makes 120 coins every 5 min
Building 3 makes 160 coins every 8 min
Building 4 makes 200 coins every 12 min

Question is, how can I make excel calculate it on an hour and 24 hourly base?

How would I put these calculations into Excel. I figured it out with other stuff, but they were hourly bases
Building 1 makes 600 coins every 4 hours, that's 3600 in 24 hours. In Excel that would be =(D3/B3)*24
D3 being the 600 B3 being the 4 hours

I hope this is allowed to be asked on this forum, as it's not the normal kind of Excel question I suppose.

Thanks for any help!

### If statement formula to calculate when there is text involved.

ok I am using this formula

=((D9-C9)+(F9-E9)+(H9-G9)+(J9-I9)+(L9-K9)+(N9-M9)+(P9-O9))*24

if one of those cell have the text "off" in it I still want it to finish up the formula and calculate the total hrs worked.

Basically what this does is calculate total hrs worked for the week. Right now if i want to show an employee off I would just leave the day blank but I have been requested to put the word "off" in for the day. Currently my time in and my time out cant equal the same so I basically be showing under the time in cell to have the word "off" and time out be blank.

Thanks for any help.

### Time calculations

i have a doubt in excel regarding time calculation.
for example : i came to office at 8.45 am and i go from office at 1.20 pm
how can i know how much time i spent in my office
and also i need all the formulas regarding time calculation

### Formula to calculate most common trifectas in a data series

Dear All Users

I have collected race results for several race meetings over several months. I would now like to analyse the data to determine the most common trifectas, (series of 3 runners) from race results (in sets of 3 numbers for each race, 1st, 2nd and 3rd).

Eg. Race Results Data below.

HORSHAM, 28/06/12 SANDOWN PARK 28/06/12 GEELONG 28/06/12 1st Place 2nd Place 3rd Place 1st Place 2nd Place 3rd Place 1st Place 2nd Place 3rd Place RACE 1 6 8 4 5 4 6 5 1 8 RACE 2 8 4 3 5 2 7 8 2 1 RACE 3 8 4 2 8 6 1 8 1 3 RACE 4 7 3 4 8 3 2 1 5 8 RACE 5 8 3 4 1 2 6 8 5 7 RACE 6 3 5 1 8 4 5 8 6 5 RACE 7 5 2 3 8 4 5 7 1 3 RACE 8 6 3 5 7 3 6 8 4 5 RACE 9 3 8 4 8 3 7 3 6 7 RACE 10 1 5 6 3 5 4 5 1 7 RACE 11 1 5 8 5 4 3 6 2 1 RACE 12 7 3 5 6 5 1 7 8 1

I have been able to work out manually that in the above example,at Horsham 28/06/12 the combination 8, 4, 3 comes out 3 times. I would like suggestions as to how to calculate this using Excel for each race meeting and also overall the data. Knowing the MODE or the COUNT doesn't help because it doesn't account for the combination of numbers that come up regularly. I would also like to be able to distinguish between the different permuations, that is, 3, 4, 8 = 8, 3, 4 = 8, 4, 3 = 3, 8, 4.

There are currently over 200 columns of data by 12 rows to which I am adding to daily.

I am using Excel 2007.

If anyone can offer any help, that would be greatly appreciated.

Regards

### Search for Date and Output Calculations onto Separate Sheet

I'm trying to write a program that sorts and calculates a specific data text file.

So far I've gotten the code to output the station code and year.
The averages output as well but some reason they are slightly off.
And the month portion doesn't get past Month 12 (December).
I tried using select case but it didn't work out.
Also, if its possible to add in a function that reads in the .txt file rather than copy and paste the txt file into Excel.
Some of the data .txt files are larger than Excel capacity.
I've comment out the slightly working portion ,so I could focus on the months.

For Ex:
Station Code Month Flow(cfs)
02429900 June 1973 -----
02429900 July 1973 -----

Station Code Annual Flow(cfs)
02429900 1973 ------
02429900 1972 ------
etc.

```
VB:

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
```
VB:
AVGs()

'Range("C28").Select
'Dim i, j, k, h, m As String
'i = DatePart("yyyy", ActiveCell)
'j = -1
'k = 0
'm = 0
'Do
'    j = j + 1
'    If DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1 Then
'    k = k + ActiveCell.Offset(j, 1)
'    Else
'    End If
'Loop While DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1
'    h = k / j
'    ThisWorkbook.Sheets("Sheet2").Range("F2").Value = h
'    ThisWorkbook.Sheets("Sheet2").Range("E2").Value = DatePart("yyyy", ActiveCell.Offset(j, 0))
'    ThisWorkbook.Sheets("Sheet2").Range("D2").Offset(m, 0).Value = ThisWorkbook.Sheets("Data").Range("B28").Offset(m,
0).Value
'
'
'Do
'    If DatePart("yyyy", ActiveCell) = i + 1 Then
'
'        i = DatePart("yyyy", ActiveCell)
'        j = -1
'        k = 0
'        Do
'        j = j + 1
'        If DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1 Then
'            k = k + ActiveCell.Offset(j, 1)
'        Else
'        End If
'
'        Loop While DatePart("yyyy", ActiveCell.Offset(j, 0)) < i + 1 Or IsEmpty(ActiveCell.Offset(j, 0))
'            h = k / j
'            m = m + 1
'            ThisWorkbook.Sheets("Sheet2").Range("F2").Offset(m, 0).Value = h
'            ThisWorkbook.Sheets("Sheet2").Range("E2").Offset(m, 0).Value = DatePart("yyyy", ActiveCell.Offset(j, 0))
'            ThisWorkbook.Sheets("Sheet2").Range("D2").Offset(m, 0).Value =
ThisWorkbook.Sheets("Data").Range("B28").Offset(m, 0).Value
'    Else
'        ActiveCell.Offset(1, 0).Select
'    End If
'Loop Until IsEmpty(ActiveCell.Offset(j, 0))

Range("C28").Select
Dim n, o, p, q, r, x As String

n = DatePart("m", ActiveCell)
o = -1
p = 0
r = 0
Do
o = o + 1
If DatePart("m", ActiveCell.Offset(o, 0)) < n + 1 Then
p = p + ActiveCell.Offset(o, 1)
Else
End If
Loop While DatePart("m", ActiveCell.Offset(o, 0)) < n + 1
q = p / o
ThisWorkbook.Sheets("Sheet2").Range("C2").Value = q
ThisWorkbook.Sheets("Sheet2").Range("B2").Value = Format(ActiveCell.Offset(o - 1, 0), "mmm yyyy")
ThisWorkbook.Sheets("Sheet2").Range("A2").Offset(r, 0).Value = ThisWorkbook.Sheets("Data").Range("B28").Offset(r,
0).Value

Do
If DatePart("m", ActiveCell) = n + 1 Then

n = DatePart("m", ActiveCell)
o = -1
p = 0
x = 0

Do

If DatePart("m", ActiveCell.Offset(o, 0)) < n + 1 Then

o = o + 1
If DatePart("m", ActiveCell.Offset(o, 0)) < n + 1 Then
p = p + ActiveCell.Offset(o, 1)
Else
End If
Else
End If

If DatePart("m", ActiveCell.Offset(o, 0)) > n + 1 Then
x = 2

Do
o = o + 1
If DatePart("m", ActiveCell.Offset(o, 0)) = 12 Then
p = p + ActiveCell.Offset(o, 1)
Else
End If
Loop Until DatePart("m", ActiveCell.Offset(o, 0)) < 12 Or IsEmpty(ActiveCell.Offset(o, 0))
Else
End If

Loop While DatePart("m", ActiveCell.Offset(o, 0)) > 0 Or IsEmpty(ActiveCell.Offset(o, 0))
x = 0
q = p / o
r = r + 1
ThisWorkbook.Sheets("Sheet2").Range("C2").Offset(r, 0).Value = q
ThisWorkbook.Sheets("Sheet2").Range("B2").Offset(r, 0).Value = Format(ActiveCell.Offset(o - 1, 0), "mmm yyyy")
ThisWorkbook.Sheets("Sheet2").Range("A2").Offset(r, 0).Value = ThisWorkbook.Sheets("Data").Range("B28").Offset(r,
0).Value
Else
ActiveCell.Offset(1, 0).Select

End If
Loop Until IsEmpty(ActiveCell.Offset(o, 0))

End Sub

[B]
[/B]

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

Any assistance would be much appreciated
Thanks

### Calculating Distance between many points

So I have a lot of data, i posted a bit of it in the sample document, and I need to calculate the absolute distance from each point to every other point. So item 1 i need to know how far it is from 2,3,4 and so on. I can't get Excel to figure out my pattern when I do a few cells and my feeble attempts at loops didn't work.
I'm looking for code or just formulas that can save me time in finding the distances. Let me know if you need anything else

### PTO calculation in Excel

Hello,

I have tried several attempts on this board from what others have posted, but i have had no luck. PTO is a strange animal and when you are a small company, with small means, it becomes difficult to always find the best solution. We calculate like so:

Years 1 and 2 total max pto of 152 hours; Per Pal Accruual assuming 80 hrs worked, 5.85 for a total Max earned of 19
year 3 total max pto pf 176; 6.77 hours per 80 hours worked; 22 total per year
year 4 total max pto 192; 7.39 hours per 80 hours worked; 24 days per year
year 5 and on total max PTO 208; 8 hours per 80 worked; 26 total days per year

other issue, a persone can carry over up to 30 days annually or what they call bank PTO hours. can some one assist with a formula for this?

I think i am close but i have tried these,

=CHOOSE(IF(DATE(YEAR(B6)+5,MONTH(B6),DAY(B6))=20,A1*0.0375,IF(A1>=15,A1*0.025,0)))

So i am stuck in the middle. Please assist.

### Calculate evolving variance

Hello,

I have a column of data, ranging from A1 to A20 and I want to calculate the variances (VAR) of the data in the following way: First I need var(A1:A2), then var(A1:A3), then var(A1:A4) up until var(A1:A20). But if I calculate the first variance var(A1:A2) and then try to extend the formula by dragging the "+" at the right corner into the respective cells, Excel 2007 instead calculates var(A2:A3), var(A3:A4) etc. How can I tell Excel to calculate the evolving variance formulas starting ALWAYS from A1 to the respective cell Ax ?

Thanks so much,
congratz

### Calculate data from large number of external csvs without vba

Hello,

I have a spreadsheet with a list of about 60 .csv filenames. Is it possible to calculate a sum from a given column of each of these files and display it in the spreadsheet. I realize that I could use the external data link wizard, but that seems exceptionally time consuming as it must be done one file at a time.

Any help would be appreciated,

Riddley

### Calculate Duration Based On Start & End Times

I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :-

Duration = (End Date,End Time) - (Start Date,Start Time ), but if i want the result in minute, what will i do.

### Perform Calculations With UserForm TextBoxes Then Formatting Them

Using Excel 2010.

Press "Calculate" on the attached User Form. Puts values in four tboxes. Works perfectly and correctly.

Press 'Calculate' AGAIN without changing anything. Produces a totally different result.

Hope someone can spot the flaw?

Ochimus

```
VB:
UserForm_Initialize()

tbox1.Value = 1240
tbox2.Value = 1240
tbox3 = 200
tbox4 = 4600

End Sub

Private Sub buttonCALC_Click()

tbox5.Value = Val(tbox1.Value)
tbox6.Value = Val(tbox2.Value)
tbox7.Value = (Val(tbox1.Value) * Val(tbox3.Value))
tbox8.Value = (Val(tbox2.Value) * Val(tbox3.Value))

'Sets textboxes to 2 decimal places:
Dim cCont As Control
For Each cCont In Me.Controls
If TypeName(cCont) = "TextBox" Then
cCont = Format(cCont, " #,##0.00")

End If
Next cCont

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
120519 UPDATE.xlsm

### Formula to calculate turn around time

Hello,

I have 2 fields created on and modified on based on which i need to calculate TAT
Date time stamp in Created on does not have any time restrictions [can be created 24x7]
Date time stamp in Modified on can only be as per conditions mentioned below
Workdays - Monday through FridayWeek Offs - Saturday and SundayOffice Hours- 8 AM to 5 PMNeed to calculate TAT based on Created on and Modified on fields. Ideal anser captured below.

4/25/12 8:00 AM 4/25/12 10:00 AM 2:00:00
4/25/12 8:00 AM 4/26/12 9:00 AM 10:00:00
4/25/12 6:00 PM 4/26/12 9:00 AM 1:00:00
4/25/12 6:00 PM 4/27/12 9:00 AM 10:00:00
4/25/12 6:00 AM 4/25/12 9:00 AM 1:00:00

Any assitance is appreciated.

Thank you,
nk1980

### Calculate Number Of Working Days Between 2 Dates

Hi All,

I am using the below formula to calculate the number of days between dates, and it also calculates how many days to the current day, my only problem is if I type 24/04/12 in the column it shows as 2 days when in fact it is only 1 day, does anyone know how to modify to incorporate this.

=IF(D38"",NETWORKDAYS(B38,D38),NETWORKDAYS(B38,TODAY())) This is in Column E (Working Days)

A B C D E
Date of Meeting Master Release Reqested Time Received Working Days 06/01/12 24/04/12 12:00pm 2

Any help is greatly appreciated.

Shazz

### Is it possible to use custom vba functions in pivot tables calculated fields formulas

Hi there!!! I was wondering if it's possible to use custom vba functions in pivot tables calculated fields formulas, I don't know if I was clear enough because English is not my first language, but what I need is to use a custom function I created in vba in a pivot table calculated field formula.