Fitting psychometric function

Standard approach I would use for something like this:

1) Select the desired sigmoidal function. You mention the logistics function, which usually specifies this specific function: https://en.wikipedia.org/wiki/Logistic_function . There are other, similar functions, so this is mostly about being sure that you have accurately specified your desired equation.

2) Since most logistics type functions are nonlinear and not "linearizable", they require non-linear regression algorithms. In Excel, this is usually accomplished using the built in Solver utility. Are you familiar with using Solver?

3) Obviously, there are details that will depend on how your data are arranged in the spreadsheet. When I do this, I almost always arrange the data as:

Please Login or Register  to view this content.

At the bottom of column D will usually be some kind of "objective function" -- the function that will measure how good my fit is. When going for a "least squares" type O.F., this could be =sumsq(column D) or =sumxmy2(columnC,ColumnB). Or other OF as you choose. Also, pay attention when building the equation in C2. If you use the correct combination of relative and absolute references, this will be easy to copy.

4) Call Solver, and tell it to
a) set target cell -- OF
b) to a minimum or maximum, depending on how your OF measures goodness of fit
c) by changing -- the cells that contain the equations parameters.

5) Evaluate result.

6) To solve for y at a given x, you simply need a copy of C2 in an out of the way row, with the desired x put in column A. To solve for x at a given y, Make a copy of A2:C2 in an out of the way row, then call Solver again and tell it to "set target cell" C in this row "to a value of" desired value for y, "by changing" the A cell in this row.

6a) Finding y at given x using logistics function in Wikipedia link.
a) y=L/(1+exp(-k*(x-x0))) L, k, and x0 are your fitting parameters. This is the function in column C, using column A as x.
b) Having copied A2:C2 into an out of the way row (I usually do this several rows below the block of cells used for the regression), you can simply entered the desired x into A of this row and column C of this row will automatically compute y corresponding to this value of x.
c) This logistics function can be solved explicitly for x. Not all sigmoidal functions can be solved explicitly for x. If you want, you can tackle the algebra, then put that formula into a cell.
d) If your function cannot (or you do not want to) be solved explicitly for x, then you can use the same copied row and Solver. Simply call Solver, tell it to
set target cell == column C of this row
to a value of == your desired value of y
by changing == column A of this row.
Solver will find the value of x that yields the value of y that you give it.

I'm not sure I really said anything different. Tell me where in this detailing of step 6 you are stuck.

Also in terms of plotting the psychometric function with sigmoid curve, how do I go about it and which values am I plotting, column A against C or column A against B?

Is it necessarily an either/or? I usually plot both column B and column C so I can see how the raw data and the regression equation compare. I usually format column B as markers without lines, and Column C as lines without markers, for better visual effect. If you have arranged the data in columns as I suggested, creating the scatter plot should be as simple as selecting columns A:C, insert -> scatter chart -> markers without lines. If Excel messes up the series definitions, go into the Select Data dialog and correct the errors. Format the two data series as desired.

Tutorial for basic chart creation: http://www.excel-easy.com/data-analysis/charts.html