How can I implement an s-curve function in excel?

April 16, 2008 5:48 PM

How can I implement an s-curve function in excel?

I have some programming experience, but my maths is crap.
I want a generic function to use to to effect a smooth transition between two numbers. Asigmoid funtion seem useful.


I have read throught these pages :
http://en.wikipedia.org/wiki/Sigmoid_function
http://www.computing.dcu.ie/~humphrys/Notes/Neural/sigmoid.html

But my poor math skills stop me from implementing it.
I do not understand how to transfer the equations I see in to code, or excel.

Much of the problem is I can't properly read and understand the notation used in math functions.
I did ask in an IRC math channel, but several people laughed.

If someone could show me how to implement the sigmoid function in excel, it would help me learn.

Thanks

posted by matholio to computers & internet (6 comments total)

I set up two columns, seems to work:
A B
1 -6 | =1/(1+EXP(-A2))
2 -5 | =1/(1+EXP(-A3))
...
(Fill down)
posted by 0xFCAF at 6:09 PM on April 16, 2008 [1 favorite]

Oops, let's try that again


A B
1 -6 =1/(1+EXP(-A1))
2 -5 (fill down)
3 -4
4 -3
5 -2
6 -1
7 0
8 1

posted by 0xFCAF at 6:12 PM on April 16, 2008

Depending on what you need to use it for, I would suggest a hermite curve interpolation instead of using the sigmoid (although that would work too).

I guess you do it in Excel something like

A4+(-2*C4*C4*C4+3*C4*C4)*(B4-A4)

Where A4 has a, B4 has b, and C4 has t.

Then you would be interpolating smoothly from a to b as t went from 0 to 1.

The thing you have to watch out for with the sigmoid is that it doesn't go from 0 to 1, so it could be harder to work with.
posted by demiurge at 6:16 PM on April 16, 2008

There is a spline macro that does this nicely. It's free, give it a try.
posted by Eringatang at 6:16 PM on April 16, 2008

You can see the curve of the cubic equation that my earlier formula is using (-2t^3 + 3t^2) here.

What do you need the smoothing for?
posted by demiurge at 6:29 PM on April 16, 2008

Open the macro editor (Alt-F11), insert a new module, add paste the following code:
Function sigmoid(t)
sigmoid = 1 / (1 + Exp(-t))
End Function
Now you can use your new function in your excelsheet, like so:
=sigmoid(10)
I use this trick all the time, if in-cell formulas get too complex. Good luck!
posted by Psychnic at 3:04 AM on April 17, 2008