Convert Excel formula and retrieve specific value with equivalent Javascript

Question

I am trying to convert the below Excel formula to Javascript. I have tried several times and will show you the values that the formula spits out as well as the names for the cell references as needed. I will also show what I have tried and failed and even using an online Converter tool found here:

http://excelformulabeautifier.com/

Here is the formula:

=IF(B11<=B19,(B11*(B18/100)(B24/100)),IF(B11<=B21,(B19(B18/100))+((B11-B19)(B20/100)),(B19(B18/100))+((B21-B19)(B20/100))+((B11-B21)(B22/100)))*(B24/100))

Here is what the converter gave me:

(B11<=B19,(B11*(B18/100)(B24/100)),IF(B11<=B21,(B19(B18/100))+((B11-B19)(B20/100)),(B19(B18/100))+((B21-B19)(B20/100))+((B11-B21)(B22/100)))*(B24/100))

But I am having trouble trying to figure out how to handle the nested if statements. I am also not an Excel guru on trying to figure out whether the if statements are nested else statements as well.

Any guidance would be greatly appreciated. Here are the values for the different cells and what the input in them is as well as what the output needs to be:

B11 - Sale Price - 90,000 B19 - Commission Amount First Break - 100,000 B18 - Commission Percent of sale price first amount - 6 B24 - Listing Broker Percent - 50 B21 - Commission Amount Second Break - 200,000 B20 - Commission Percent of sale price 2nd amount - 4 B22 - Commission Percent of sale price 3rd amount - 2

The output is for listing broker commission and the value is: 2700

Any help is appreciated. Thanks!

shahkalpesh shahkalpesh · Accepted Answer · 2015-05-05T14:12:40

var B11 = 90000;
var B19 = 100000; // - Commission Amount First Break -
var B18 = 6; // - Commission Percent of sale price first amount -
var B24 = 50; // - Listing Broker Percent 
var B21 = 200000; // - Commission Amount Second Break -
var B20 = 4;  // Commission Percent of sale price 2nd amount 
var B22 = 2; // - Commission Percent of sale price 3rd amount - 

var result;
if (B11 <= B19)
{
    result = (B11 * (B18/100) * (B24/100));
}
else if(B11 <= B21)
{
    result = (B19*(B18/100))+((B11-B19)*(B20/100));
}
else
{
    result = (B19*(B18/100))+((B21-B19)*(B20/100))+((B11-B21)*(B22/100))*(B24/100);
}

console.log(result);

Convert Excel formula and retrieve specific value with equivalent Javascript

1 Answers