-
Alsatrus65Asked on December 15, 2019 at 4:58 PM
We have quite complex formulas that we can manage in a spreadsheet but we do not know how to insert these calculation within the forms , so our client can get a premium as a result of the data the fill in the form.
Can you give us some advice of how to get this done?
Best,
Alvaro.
-
AshtonPReplied on December 15, 2019 at 5:07 PM
Thank you for writing to us!
To have a calculation function in your form, you can add a calculation widget in your form. Here are some guides that will provide you more details about how form calculation works:
How to perform calculation in the form.
How-to-Assign-Calculation-Value
How-to-Pass-a-Calculation-to-a-Payment-Field
Conditional-Logic-Inserts-Text-Calculation-Into-A-Field
Also, let us know your requirements and how you want your calculation to work, we can help you out in creating your form.
We will be waiting for your response.
-
Alsatrus65Replied on December 17, 2019 at 3:50 AMGood morning,
Please find attached an excel file with the rating tool we use that
includes the premiums matrix and the formulas for calculating the price.
I also attach the proposal form that would be the form we want to build ,
that contains the questions to be answered that drive to the final price.
Let me know your thoughts. Best regards.
Álvaro Satrústegui SilvelaCEO
Calle Hermosilla 77, 2º Piso, Oficina 3
Madrid, España. 28001
Teléfono: +34 91 154 22 69
Móvil: +34 629 047 800
Web: www.exseluwa.com
En cumplimiento de la legislación vigente en protección de datos
personales, se advierte que los datos de carácter personal se incluirán en
los ficheros creados por EXSEL UNDERWRITING AGENCY, S.L. La finalidad de
los ficheros es la correcta gestión de los contratos de seguro y las
prestaciones derivadas del mismo. El afectado podrá ejercer sus derechos
ante EXSEL UNDERWRITING AGENCY, S.L., C/ Hermosilla 77- Piso 2, Oficina 3–
28001 – MADRID; Tlf.: 911542269, e-mail: administracion@exsel.net como
responsable del tratamiento.
“Este mensaje y cualquier anexo se dirige exclusivamente a su destinatario
y puede contener información CONFIDENCIAL sometida al secreto profesional.
Si no es el destinatario final de este mensaje, le rogamos que nos lo
comunique inmediatamente por esta misma vía y proceda a su destrucción.
Queda notificado que su uso, distribución, impresión, copia y difusión por
cualquier medio esta tipificada como delito en el artículo 197 y siguientes
y 278 del vigente Código Penal”
El dom., 15 dic. 2019 a las 23:07, JotForm () escribió:
... -
AndrewHagReplied on December 17, 2019 at 4:09 AM
Unfortunately, we have not received the attachment. Please open this link in the browser and attach the files so we can check.
Meanwhile, you may try using the Form Calculation and Spreadsheet widgets and see if that works for you.
-
Alsatrus65Replied on December 17, 2019 at 4:18 AM
-
Vanessa_TReplied on December 17, 2019 at 4:38 AM
It seems that you haven't created a form for this need of yours.
Please create the form and add the necessary fields, you can then follow this guide to perform calculations:
https://www.jotform.com/help/259-How-to-Perform-Form-Calculation-Using-a-Widget
Once you already have the form, we can then help you with any difficulties you encounter.
You may share to us which calculation on your spreadsheet corresponds to which field in your form so we can assist you. You may also share a screenshot if that can help you explain better.
-
Alsatrus65Replied on December 28, 2019 at 2:51 PM
Hi Vanessa,
I have finished the form which is the following: https://form.jotform.com/193506322663053
I also attach the spread sheet that include the rating matrix in the sheet called Calculations D&O : https://drive.google.com/file/d/1qUox3W8lP4n5YODBIpUw_ueS_r4PMigz/view?usp=sharing
My problem is how to include the calculations based on this rating matrix. I know how to do it in an excel file but not with the widgets.
There are only two questions that are related to the rating matrix:
cc.Volumen de facturación consolidado (últimos 12 meses): *
and
LIMITE SOLICITADO:
The first question refers to the turnover of the insured company. The second question is a drop down list where they can choose the limit of the insurance.
So depending on both answers you can find in the matrix a premium that corresponds with the turnover and the limit you choose.
Let me know if you can help me integrate the formulas I need for getting the premium from the rating matrix.
Look forward to your news , best regards.
-
Kiran Support Team LeadReplied on December 28, 2019 at 4:52 PM
I have checked the spreadsheet provided by you and understood that changing the Option from Dropdown field is changing the value in 'PRIMA DE RECIBO'. However, I wasn't sure how the calculation is performed on the Spreadsheet and which of the field is the turnover.
It would be helpful if you can provide us with an example of a calculation and the fields associated so that we can assist you further.
We will wait for your response. Thank you!
-
Alsatrus65Replied on December 31, 2019 at 12:39 PM
Hi Kiran,
I have translated the headers and titles of the main fields so you can follow the explanation of the spread sheet.
The first sheet "RATOR D&O" is the data introduction sheet.
In row 9 you can find "INSURED LIMITS" , different options ,all 3 are a drop down list.
This list is the same for the 3 options and represents the insured limits options of the product.
In row 46 you can find "TURNOVER" where you introduce this data (D46)
In row 49 you can find the Premium calculations for each of the 3 options. D49, F49 and H49
All this 3 cells show a formula that is in the second sheet " Calculations D&O"
In "Calculations D&O" you can find the following:
Premium Schedule : Is a matrix of two entries , Turnover and Limits
The figures within the matrix are the premiums.
Calculations: Contains the data of the RATOR D&O corresponding fields . the formulas are in C26, C27 and C28.
The spreadsheet:
https://drive.google.com/file/d/1tG0aERhVLOQpbhg9FRppStuIfIYWXGy0/view?usp=sharing
Look forward to your news , happy new year and best regards.
-
Kiran Support Team LeadReplied on December 31, 2019 at 1:47 PM
Thank you for providing detailed information with the spreadsheet. Please allow us sometime to check this up and get back to you with relevant information.
Thank you for your patience.
-
Alsatrus65Replied on January 13, 2020 at 3:40 PM
Hi Kirian,
Do you have any feedback to share?
Look forward to your news , best regards.
-
Vanessa_TReplied on January 13, 2020 at 5:57 PM
I tried to use an Input Table and assigned calculation values based on your spreadsheet. Unfortunately, since the input table cannot have a default value, then the calculation of the premium cannot be done automatically.
The only possible way I could think of to calculate the premium, is to use calculation conditions, one for each possible limit and turnover pair.
Please see this guide to help you with creating these conditions:
-
Alsatrus65Replied on January 17, 2020 at 5:21 PM
Thanks Vanessa. Can you give me a link to the demo form please?
-
jherwinReplied on January 17, 2020 at 10:17 PM
I will ask my colleague for the demo form link, once she gets online, she will update you through this thread.
Thank you!
-
Vanessa_TReplied on January 20, 2020 at 7:39 AM
Hello Alvaro,
My apologies for the delayed response.
Here's the link for the demo form: https://form.jotform.com/200126771941047
Feel free to clone it to see how it is setup.