What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No signup 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.

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 AMPlease refer to this google sheet to  know exactly what I'm trying to do.
https://docs.google.com/spreadsheets/d/1_dWnzCVZMkVGuDfRy0G3AU760EYtbPwVh2joDvgkXE/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 ?

JotForm Support
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.

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 price25% 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

JotForm Support
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.

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

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

JotForm Support
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.

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

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

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. 
JotForm Support
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.

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?

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

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

JotForm Support
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.

Thank you Jonathan,
First part is Correct.
Second part is not correct your example is using MARKUP
Margin is a % or the Selling price.
Markup is a % of the cost price  but I'm trying to work on MARKUP please

JotForm Support
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.

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.

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


JotForm Support
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 199 or 2001,199
Please check it and give us your feedback if this will work or not.
Thanks.

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

JotForm Support
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!

anybody there?

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

when might Jonathan be back on duty please?

JotForm Support
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.

Hi  might Jonathan be about please? 
If Jonathan is on leave or unwell  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

JotForm Support
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/42HowtoCloneanExistingFormfromaURL
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.

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)ARKUP IS THE % OF THE COST  BIT WE NEVER USE % MARKUP  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?

JotForm Support
Ian,
When will be the time to multiply 1.428571428600 to the Cost Price?
Was it between the Cost Price of 2001000?
Thanks.
