What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    Can anybody help me build this calculation formulae in Jotform please? (I have no code experience)

    Asked by IanRm on October 19, 2014 at 06:20 AM

    Please refer to this google sheet to - know exactly what I'm trying to do.

     

    https://docs.google.com/spreadsheets/d/1_dWnzCVZMkVGuDfRy0G3AU760EYtbPwVh-2joDvgkXE/edit?usp=sharing

     

    I am abrand new user of Jotform

    (with no code skills) and looking for help please to construct the main MARGIN CALCULATION formulae in Jotform that when a cost price is entered for which we wish to calculate the selling price based on this table - can anybody help me achieve this formulae. If field A = the cost price I need field B to reflect the sell price - using this table to apply the appropriate margin value and come up with a Sell price - please?

     

    I'm willing to pay for this help - if that is appropriate

     

    How do I give more info? - I only see POST ANSWER ?

    Screenshot
    JotForm table calcuclation margin calculation
  • Profile Image
    JotForm Support

    Answered by jonathan on October 19, 2014 at 08:09 AM

    Hi,

    I suggest you simplify also the calculation for us. 

    I checked the calculation table you shared and it is quite overwhelming and can be confusing initially.

    How about you give us an actual example calculation so we can understand the formula and input data involved.

    Example: 

    If you check this mortgage calculation template, you will be able to easily understand what were calculated.

    Give us a sample calculation and we will see what we can do.

    Thanks.

     

     

     

  • Profile Image

    Answered by IanRm on October 19, 2014 at 09:19 AM

    I need to ask Have you opened the shared sheet? - 

    If the BASE COST - is €1-€44 - then add €45 margin value
    (the formula will reference to a field that has the appropriate value €45- this can change)

    If the BASE COST - is €45-€74 - then add €55 margin value.
    (the formula will reference to a field that has the appropriate value €55 this can change)

    If the BASE COST - is €75-€99 - then add €75 margin value.
    (the formula will reference to a field that has the appropriate value €75 this can change)

    If the BASE COST - is €100-€149 - then add €75 margin value.
    (the formula will reference to a field that has the appropriate value €75 this can change)

    If the BASE COST - is €150-€199 - then add €85 margin value.
    (the formula will reference to a field that has the appropriate value €85 this can change)

    If the BASE COST - is €200-€9999 - then add €85 margin value.
    (the formula will reference to a field that has the appropriate value €85 this can change)

    FROM €200 upwards it changes from adding on a FIXED VALUE margin to add in Margin% 
    (important to note MARGIN% - NOT MARK UP.

    If the BASE COST - is €200-€9999 - then 
    Divide the BASE COST say €275  divided by .7 (to get 30% Margin) = €392.86
    30% margin uses a factor of 1.42857 of the base cost to arrive at selling price

    25% margin uses a factor of 1.33333 of the base cost to arrive at selling price

    20% margin uses a factor of 1.25 of the base cost to arrive at selling price

    10% margin uses a factor of 1.11111 of the base cost to arrive at selling price

    Above is explained in more on the sheet - if you have opened it

     

     

     

  • Profile Image
    JotForm Support

    Answered by jonathan on October 19, 2014 at 09:28 AM

    Hi,

    Yes, I did check the shared spreadsheet. I even saw another name viewing it (Ian Mullen) which I think was you.

    --

    can you please instead create an actual formula and sample calculaiton for this

    If the BASE COST - is €1-€44 - then add €45 margin value 

    (the formula will reference to a field that has the appropriate value €45- this can change)

    this way I will be able to translate the formula and calculations into the jotform calculation.

    What you actually provided just now was the same breakdown that was in the spreadsheet.. I could not formulate the calculation using only that.

     

    Thanks.

     

     

     

     

  • Profile Image

    Answered by IanRm on October 19, 2014 at 09:36 AM

    Thank you Jonathan
    Not sure how I can explain better?
    If i could write the example that solves this - I would not need the help in the first place.

    I'm 100% comfortable with SIMPLE A and B referencing C to calculate an answer.

    The KEY FACTOR here is that I'm wishing to 'reference' or 'look up' a table (which I call margin table) to solve the answer

  • Profile Image

    Answered by IanRm on October 19, 2014 at 09:38 AM

    The answer in field B is CONDITIONAL on the ACTUAL VALUE in field A

  • Profile Image
    JotForm Support

    Answered by jonathan on October 19, 2014 at 09:45 AM

    I understand that it was using a lookup table.. but I just want to determine the actual calculation formual.

    Example: base on this description 

    If the BASE COST - is €1-€44 - then add €45 margin value 

    (the formula will reference to a field that has the appropriate value €45- this can change)

    SELLING PRICE = BASE COST + MARGIN VALUE

    MARGIN VALUE = ?  

    ** the margin value is pick from a reference table.. but we need to determine HOW it picks i.e 45 as the value.

    What is the formula for the form factor to to say that it should be 45.

     

    Thanks.

     

     

     

  • Profile Image

    Answered by IanRm on October 19, 2014 at 09:52 AM

    If the BASE COST - is €1-€44 - then add €45 margin value 
    (the formula will reference to a field that has the appropriate value €45- this can change)

    Above is JUST  ONE of 13 different values in the table supplied commencing from €1 - €10,000 cost values

    €45 is applied in this example because the COST value falls between €1 and €44 value

  • Profile Image

    Answered by IanRm on October 19, 2014 at 09:57 AM

    If the COST VAUE of the item is LESS THAN €200  a fixed margin value is to be applied depending in where in the range from €1 - €199 it lands?

    There are 5 Different Bands between €1 - €199

    WHEN THE BASE COST VALUE HITS €200 or MORE:

    Then the method of calculation changes to '%MARGIN' 

    Where again there are another 8 BANDS (different % Margins applicable) between €200 and 10,000

  • Profile Image

    Answered by IanRm on October 19, 2014 at 10:02 AM

     

    Trying to establish one formula that would produce a selling price from a purchase or production cost - 
    but result being conditional on variable levels of purchase cost attracting different margin values.
  • Profile Image
    JotForm Support

    Answered by jonathan on October 19, 2014 at 10:06 AM

    Please understand that I am trying to determine an exact formula and calculation to use in jotform because we want to put all calculations within the form as much as possible.

    It will be much  more complex if we are going to use an external data source (in this case the look up table) that is not within the jotform. This is because you will then need to code custom functionality to have the capability of the lookup table. This will involve custom programming.

    Whereas if we can keep everything within the form builder and use Calculation feature then it will be simpler to achieve.

    --

    from this..

    €45 is applied in this example because the COST value falls between €1 and €44 value

    We can actually build calculation formula for this.

    Like this

    IF BASE COST > 0 and < 45 THEN 

    MARGIN VALUE = 45

    now that I have the margin value, I can calculate this

    SELLING PRICE = BASE COST + MARGIN VALUE

    your thoughts?

     

    Thanks.

     

     

     

     

     

  • Profile Image

    Answered by IanRm on October 19, 2014 at 10:13 AM

    Thank you Jonathan, your example is correct IF THE VALUE was >0 and <45 margin Value = 45 BUT can the ONE formula be further constructed to cope with all values in the margin table?

     

  • Profile Image

    Answered by IanRm on October 19, 2014 at 10:14 AM

    so that the formula will return a correct value for any cost value between €1 and €10,000

  • Profile Image

    Answered by IanRm on October 19, 2014 at 10:24 AM

    I was hoping that the table would be part of the form but the formulae would reference it somehow?

  • Profile Image
    JotForm Support

    Answered by jonathan on October 19, 2014 at 10:25 AM

    If my understanding is correct, base on the lookup table , we only need about 13 conditions to calculate the would be margin value

     

    like this:

    IF BASE COST > 0 and < 45 THEN 

    MARGIN VALUE = 45

    IF BASE COST > 44 and < 75 THEN 

    MARGIN VALUE = 55

    IF BASE COST > 74 and < 100 THEN 

    MARGIN VALUE = 75

    |

    |

    so on...

    I assume the percentage part was the percent of the BASE COST. That can still be easily done using the Calculation. it can be like this

    IF BASE COST > 199 AND < 1000 THEN

    MARGIN VALUE = (BASE COST * .30)

    Thanks.

  • Profile Image

    Answered by IanRm on October 19, 2014 at 10:29 AM

    Thank you Jonathan,

    First part is Correct.

     

    Second part is not correct your example is using MARK-UP 

    Margin is a % or the Selling price.

    Markup is a % of the cost price - but I'm trying to work on MARK-UP please

  • Profile Image
    JotForm Support

    Answered by jonathan on October 19, 2014 at 10:34 AM

    Tell us the correction for this

    IF BASE COST > 199 AND < 1000 THEN

    MARGIN VALUE = (BASE COST * .30)

    How do you calculate the Margin Value for BASE COST above 199?

    Better if you can give actual numbers and calculation

     

    Thanks.

     

  • Profile Image

    Answered by IanRm on October 19, 2014 at 01:29 PM

    EXAMPLE: The cost x 1.428571428600 will give you 30% MARGIN

    you will see all the other multiplication factors that are used to achieve the different %' MARGIN in the top left section of my sheet.

     

  • Profile Image

    Answered by IanRm on October 19, 2014 at 02:37 PM

    Is this what your after?

    The numbers from my MARGIN TABLE put in series?

     

    if A= >1<45 +45,or if A= >44<75 +55, or if A= >74<100 +75, or if A= >99<150 +75, or if A= >149<200 +85 or if A = >200 <1000 *1.4285714286, or if A = >999 <2000 *1.3333333333, . . . . and so on

  • Profile Image

    Answered by IanRm on October 19, 2014 at 04:06 PM

     

  • Profile Image
    JotForm Support

    Answered by jonathan on October 19, 2014 at 06:58 PM

    Hi Ian,

    Sorry for the delays.

    I made a quick demo jotform http://www.jotform.me/form/42917507275460 

    that have the calculation according to the look up table.

    The initial lookup condition formula is like this

     

    For now, to test the jotform use only Cost Price from 1-99 or  200-1,199

    Please check it and give us your feedback if this will work or not.

     

    Thanks.

     

  • Profile Image

    Answered by IanRm on October 20, 2014 at 01:45 AM

    Thank you Jonathan

    numbers less than 99 are working fine - but not numbers over 99

    Larger numbers like 350 as example - are not correct yet

    350 divided by .7 = 500

    or

    350 x 1.428571428600 = 500    - (but currently it is producing a sell price of 649.70 which is not correct)

    sorry

  • Profile Image
    JotForm Support

    Answered by Welvin on October 20, 2014 at 07:06 AM

    Hi Ian,

    I'm reading the whole thread, but I think I need to double check  everything so I leave this for now to Jonathan. I think, he will be here later today.

    Thank you!

  • Profile Image

    Answered by IanRm on October 21, 2014 at 08:56 AM

    anybody there?

  • Profile Image
    JotForm Support

    Answered by Welvin on October 21, 2014 at 10:02 AM

    I'm sorry, I am sure Jonathan received your reply. We'll update you here. We're sorry for the delay.

    Thank you!

  • Profile Image

    Answered by IanRm on October 22, 2014 at 03:39 AM

    when might Jonathan be back on duty please?

  • Profile Image
    JotForm Support

    Answered by Charlie on October 22, 2014 at 10:28 AM

    Hi,

    It seems that the flow of conversation and solution for this thread is well established. Rest assure that Jonathan is and will be notified regarding this thread to give you an update.

    Regards.

  • Profile Image

    Answered by IanRm on October 26, 2014 at 04:31 AM

    Hi - might Jonathan be about please? -

    If Jonathan is on leave or un-well - could someone else please take up this one please? that would be appreciated very much.

    JUST FOLLOWING UP ON AN UNRESOLVED REPLY on October 20, 2014 

    Jonathan,

    Numbers less than 99 are working fine - but not numbers over 99

    Larger numbers like 350 as example - are not correct yet

    350 divided by .7 = 500

    or

    350 x 1.428571428600 = 500    - (but currently it is producing a sell price of 649.70 which is not correct)

    sorry

  • Profile Image
    JotForm Support

    Answered by jonathan on October 26, 2014 at 07:54 AM

    Hi Ian,

    Sorry for the delays.

    Can you please clone the jotform http://www.jotform.me/form/42917507275460

    Follow this user guide: http://www.jotform.com/help/42-How-to-Clone-an-Existing-Form-from-a-URL

    This way you will be able to review how I created the formula in your form builder.

    As I have mentioned my formula was created like this

     

    If Cost is 350 it gives 299.70 as the Margin Value. Is the calculated Margin Value wrong?

     

    Please tell me what is suppose to be the correct Margin Value for Cost of 350.00

    I know you are saying that it was suppose to be  1.428571428600  as you mentioned

    350 x 1.428571428600 = 500    - (but currently it is producing a sell price of 649.70 which is not correct)

     

    I want to understand how you come up with 1.428571428600 margin value for the cost 350.00

     

    Thanks.

     

     

     

     

     

     

     

     

  • Profile Image

    Answered by IanRm on October 26, 2014 at 09:11 AM

    Hi Jonathan,

    The 1.428571428600  IS NOT A MARGIN VALUE

    1.428571428600  is the MULTIPLICATION FACTOR THAT WE MULTIPLY A COST PRICE BY IF WE WISH TO CALCULATE A SELLING PRICE THAT WILL RETURN A GROSS MARIN = 30%
    (% MARGIN IS THE %  OF THE END SALES VALUE - NOT THE COST VALUE)

    ARK-UP IS THE %  OF THE COST - BIT WE NEVER USE % MARK-UP - WE ONLY USE % MARGIN- AS ABOVE)

    THIS TABLE WILL GIVE YOU SOME EXAMPLES
    OF THE MULTIPLICATION FACTORS THAT ARE APPLICABLE FOR A FEW GIVEN / COMMON % MARGINS.

    Jonathan, Does this explain what % MARGIN is? and how you achieve a selling price using the concept of MARGIN? for you or NOT?

    For example to achieve 30% Gross Margin you multiply the product COST by 1.428571428600

    each % Margin has a different multiplication value which I can change once I see the formula working for one value 

    Might this work?

  • Profile Image
    JotForm Support

    Answered by jonathan on October 26, 2014 at 10:07 AM

    Ian,

    When will be the time to multiply 1.428571428600 to the Cost Price?

    Was it between the Cost Price of 200-1000?

     

    Thanks.