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

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 ?

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

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

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

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?

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

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

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.

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

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?

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?

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

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.

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?

Ian,

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

Was it between the Cost Price of 200-1000?

Thanks.