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

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

- Ic50 calculation
- Showing the IC50 value on a graph.
- Locking and hiding the formula calculation area of worksheets
- Random Cells Not Calculating?
- Calculating average from different workbooks
- Use two different calculations based on ID number in column
- Form that calculates and shows the total price of the products selected in an order
- VBA macro using calculated value out as new input in.
- Changing VBA code to work with calculate
- Calculate totals based on three criteria
- Hourly Calculation in Excel 2010
- If statement formula to calculate when there is text involved.
- Time calculations
- Formula to calculate most common trifectas in a data series
- Search for Date and Output Calculations onto Separate Sheet
- Calculating Distance between many points
- PTO calculation in Excel
- Calculate evolving variance
- Calculate data from large number of external csvs without vba
- Calculate Duration Based On Start & End Times
- Perform Calculations With UserForm TextBoxes Then Formatting Them
- Formula to calculate turn around time
- Calculate Number Of Working Days Between 2 Dates
- Is it possible to use custom vba functions in pivot tables calculated fields formulas

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

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?

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!

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.

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?

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.

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

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.

VB:Thanks.Range) Sheet5.Unprotect Password:="password" Select Case Target.Address 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 Sheet5.Protect Password:="password" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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!

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!

=((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.

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

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

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

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

Thanks in advance

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.

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

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

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

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:120519 UPDATE.xlsmUserForm_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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

Created on Modified on Answer

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

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

Working days should read 1

Any help is greatly appreciated.

Shazz

Thanks in advance

No luck finding an answer? You could always try Google.