Can anybody help me build this calculation formulae in Jotform please? (I have no code experience)
 IanRmAsked 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_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 SupportjonathanAnswered 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.
 IanRmAnswered 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 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 SupportjonathanAnswered 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.
 IanRmAnswered 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
 IanRmAnswered on October 19, 2014 at 09:38 AM
The answer in field B is CONDITIONAL on the ACTUAL VALUE in field A
 JotForm SupportjonathanAnswered 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.
 IanRmAnswered 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
 IanRmAnswered 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
 IanRmAnswered 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.  JotForm SupportjonathanAnswered 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.
 IanRmAnswered 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?
 IanRmAnswered 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
 IanRmAnswered 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?
 JotForm SupportjonathanAnswered 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.
 IanRmAnswered on October 19, 2014 at 10:29 AM
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 SupportjonathanAnswered 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.
 IanRmAnswered 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.
 IanRmAnswered 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
 IanRmAnswered on October 19, 2014 at 04:06 PM
 JotForm SupportjonathanAnswered 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 199 or 2001,199
Please check it and give us your feedback if this will work or not.
Thanks.
 IanRmAnswered 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
 JotForm SupportWelvinAnswered 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!
 IanRmAnswered on October 21, 2014 at 08:56 AM
anybody there?
 JotForm SupportWelvinAnswered 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!
 IanRmAnswered on October 22, 2014 at 03:39 AM
when might Jonathan be back on duty please?
 JotForm SupportCharlieAnswered 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.
 IanRmAnswered on October 26, 2014 at 04:31 AM
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 SupportjonathanAnswered 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/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.
 IanRmAnswered 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)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 SupportjonathanAnswered 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 2001000?
Thanks.