5 Replies Latest reply on Apr 4, 2012 1:21 PM by MarkWalsh

# Help! multi-calculation JS needed...I have no clue!

Hello, I have a horrific (to my mind) multi-step problem...and I figured out the math I need to use, but not sure how the heck to get it to work in JS - I am at wits' end!

Please, gurus of JS, help me!

So:

1. The User types a number into "cell a"

2. The number in "cell a" needs to be x by \$120

3. then the resulting value of "cell a x \$120" is then x by 40%, which populates "cell b"

4. then "cell b" is x by .9782, and populates "cell c"

5. To get the value for "cell d", divide "cell c" by "cell a".

Now, for the most evil part to my non-scripting mind:

6. if the value of "cell d" is less-than \$48, multiply "cell d" by \$48 anyway to get the value for "cell e"

If the value for "cell d" is greater than \$48, use the greater number to calculate "cell e".

Argh! Ack!

There is more to the form, but I can figure out those bibs and bobs...it's just the run above is making my eyes cross.

• ###### 1. Re: Help! multi-calculation JS needed...I have no clue!

Do you know how to do 1-5?

The calculation for #6 is actually much simpler than you are making it - just use the Max of '48' and 'cell d' to calculate 'cell e' (you haven't quite explained clearly how you are calculating 'cell e' -- are you multiplying cell d by itself?)

var cellD = this.getField('cell d').value

var multiplier = Math.max(48, cellD);

this.getField('cell e') = multiplier * cellD  // Is this what you want?

• ###### 2. Re: Help! multi-calculation JS needed...I have no clue!

The more I look at it, the more it doesn't make any sense! I'm going to work through the sheet again (gah, wish I could attach the file I'm working on).

• ###### 3. Re: Help! multi-calculation JS needed...I have no clue!

It often helps to use descriptive names for your fields (and variables). 'cell a', 'cell b', etc. don't really help much.

I am guessing at what your values are representing:

'cell a' = QTY

\$120 = Price (per unit)

40% = Discount??

'cell b' = Total Price (line item)

'cell c' = ??

'cell d' = ??

'cell e' = ??

So, an example to understand what is happening with the numbers (X represents the unknown multiplier that I questioned for cell e):

'cell a' = 5

'cell b' = 5 * \$120 * .4     [240]

'cell c' = 240 * .9782       [234.768]

'cell d' = 234.768 / 5       [46.9536]

'cell e' = X * Max(48, 46.9536)       [ X * 48]

Is this correct?

• ###### 4. Re: Help! multi-calculation JS needed...I have no clue!

Hi Mark,

That is amazing - pretty much exactly what I am looking for...this is the original part of the "evil" document:

So # of candidates*120*.4*.9782 = Total USA

Test Rate is Total USD/# of Candidates

Test rate, if < \$48, is calculated for Total as (\$48*# of Candidates) anyway.

if the Test Rate is > \$48, it is calculated at the higher rate for Total (Higher Rate*Number of candidates)

*phew*...I see what I had missing in "cell e" now!

• ###### 5. Re: Help! multi-calculation JS needed...I have no clue!

This still doesn't make much sense to me.

According to that, Test Rate will always be the same: 120 * .4 * .9782     [46.9536]

TotalUSD = #Cand * (120*.4*.9782)

TestRate = TotalUSD / #Cand

is the same as

TestRate = #Cand * (120*.4*.9782) / #Cand

is the same as

TestRate = #Cand/ #Cand * (120*.4*.9782)

is the same as

TestRate = 1 * (120*.4*.9782)