[Solved] Calculating the logistic function that models a data set

[hayley]

I have been trying to find the logistic trendline together with its corresponding equation that fits a data set with two variables (x and y). The only options that are in Excel are exponential, linear, logarithmic, polynomial, power, and moving average.

I was wondering whether it is possible to fit a Logistic or Gompertz function. A logistic function is in the form
Here is the question that I am supposed to solve.

After trying to research online and the ExcelDemy Blog, I have only found information about logistic regression which does not answer my question since I am only looking for a logistic function.

 

[Lutfor Rahman Shimanto]

I have been trying to find the logistic trendline together with its corresponding equation that fits a data set with two variables (x and y). The only options that are in Excel are exponential, linear, logarithmic, polynomial, power, and moving average.
View attachment 345
I was wondering whether it is possible to fit a Logistic or Gompertz function. A logistic function is in the form View attachment 347
Here is the question that I am supposed to solve.
View attachment 346
After trying to research online and the ExcelDemy Blog, I have only found information about logistic regression which does not answer my question since I am only looking for a logistic function.

Hello Hayley

Thanks for reaching out and posting your issue. You are correct that there is no Logistic option in the Format Trendline pane, so you must create a custom Trendline that closely resembles the Logistic Trendline option in Excel. You can achieve this by using a user-defined function in Excel VBA, and you can press Alt+F11 and insert the below code in VBE.

Excel VBA Code:

Function LogisticFunction(x As Double, a As Double, b As Double, c As Double) As Double

    LogisticFunction = c / (1 + a * Exp(-b * x))

End Function

Back in your Excel sheet, you can use the custom trendline function in a cell. Replace a, b and c with the appropriate values for the coefficients best fitting your data. To provide the custom logistic trendline to your data, you must adjust the coefficients b and c values until the curve aligns with your data points.

This idea will help you to reach your goal. Good luck.

Regards
Lutfor Rahman Shimanto

 

[hayley]

Hello Rahman I have highlighted the part of the table and pressed Alt+F11 and typed that part of the code. However, when I try to run it, it keeps on asking for a Macros name and I am not sure what I should input. Could you please give me key steps toward getting the output a, b, and c.

 

[Lutfor Rahman Shimanto]

Hello Rahman I have highlighted the part of the table and pressed Alt+F11 and typed that part of the code. However, when I try to run it, it keeps on asking for a Macros name and I am not sure what I should input. Could you please give me key steps toward getting the output a, b, and c.
View attachment 348

Thank you for explaining your problem with such clarity, Hayley! Since the code I provided contains only a User-defined function, the VBE shows you that window asking for a macro name. We can call a UDF from a cell or a sub-procedure.

Key Steps:

• Press Alt+F11 to open the VBE.

​

• Navigate the Insert tab and click on Module.
• Next, copy the below code and Save the file with a macro-enabled extension.

Function LogisticFunction(x As Double, a As Double, b As Double, c As Double) As Double

    LogisticFunction = c / (1 + a * Exp(-b * x))

End Function

• Later, use the UDF like other worksheet functions in sheets.

=LogisticFunction(A2,$F$4,$F$5,$F$6)​

• Use the values of a, b and c, which fit your data.

​

This instruction helps you reach your objective. I'm also providing you with the Workbook used to analyze your problem to assist you in understanding it better.

Regards
Lutfor Rahman Shimanto