April 16, 2008 5:48 PM Subscribe

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 answers total)

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

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

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

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

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

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:

Now you can use your new function in your excelsheet, like so:

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

`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

This thread is closed to new comments.

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]