基于多个变量设置期权值[关闭]

Question

Here is the scoop. I am making a product calculator which has 5 different options, but the value I need to apply to the initial variable is dependent on two separate variables to determine the correct value to multiply by.

So logically, I have value A which I need to multiply by variable B, however, the value of B is dependent on the input from variables C and D. Value B is an absolute value. To add insult to injury, if value A is less than 12, I need to subtract .5 or .25 depending on the value of variable C from the original value before B is applied. I am lost here.

Here is the original equation from the excel spreadsheet that was given to me to adapt:

=IF(AND(B9="Option_1",B11="Horizontal"),D6+K6+1-0.00515464*(D6+K6),IF(AND(B9="Option_1",B11="Vertical"),D6+K6+1-0.0064433*(D6+K6),IF(AND(B9="Option_2",B11="Horizontal",D6>12),D6+K6-0.5+1-0.010417*(D6+K6),IF(AND(B9="Option_2",B11="Vertical",D6>12),D6+K6-0.25+1-0.005208*(D6+K6),IF(AND(B9="Option_2",B11="Horizontal",D6<12),D6+K6-0.25+1-0.010417*(D6+K6),IF(AND(B9="Option_2",B11="Vertical",D6<12),D6+K6+1-0.005208*(D6+K6),IF(AND(B9="Option_3",B11="Vertical",D6>12),D6+K6-0.5+1-0.010417*(D6+K6),IF(AND(B9="Option_3",B11="Horizontal",D6>12),D6+K6-0.25+1-0.005208*(D6+K6),IF(AND(B9="Option_3",B11="Vertical",D6<12),D6+K6-0.25+1-0.010417*(D6+K6),IF(AND(B9="Option_3",B11="Horizontal",D6<12),D6+K6+1-0.005208*(D6+K6),IF(AND(B9="Option_4",B11="Vertical"),D6+K6+1-0.03125*(D6+K6),IF(AND(B9="Option_4",B11="Horizontal"),D6+K6+1-0.02083*(D6+K6),IF(AND(B9="Option_5",B11="Vertical"),D6+K6+1-0.0625*(D6+K6),IF(AND(B9="Option_5",B11="Horizontal"),D6+K6+1-0.052083*(D6+K6)))))))))))))))

Thanks!

Answer 1

The answer to this question should probably provide some sort of guidance on how to get from the sort of code that you've provided (a very long string of spreadsheet ternary IF functions that is quite hard to understand) to something in PHP that is more concise, and perhaps slightly more readable.

I don't know if I'm good enough at explaining this sort of thing to actually truly help you, but I think the most important steps to take in the process would be to;

Reformat the code

Putting each condition on a line of it's own, and each resulting output block on a line of it's own, can be hugely helpful to understanding the logic you've got. This often means that you can see patterns as you look down through the code.

Group common logic

Once the code is formatted nicely, and you can perhaps see some patterns emerging, you should be able to group statements into fewer blocks.

---

Having taken these steps myself on the code you provided, this function should provide the same output for the given values of D6, K6, B9 and B11 as you were getting in the spreadsheet;

function calculateValue($D6, $K6, $B9, $B11) {
    $modifiers = array(
        'Option_1' => array('Horizontal' => 0.00515464, 'Vertical' => 0.0064433),
        'Option_2' => array('Horizontal' => 0.010417, 'Vertical' => 0.005208),
        'Option_3' => array('Horizontal' => 0.005208, 'Vertical' => 0.010417),
        'Option_4' => array('Horizontal' => 0.02083, 'Vertical' => 0.03125),
        'Option_5' => array('Horizontal' => 0.052083, 'Vertical' => 0.0625),
    );
    $x = $D6 + $K6;
    $y = $modifiers[$B9][$B11];
    $sub = 0;
    if ($B9 == "Option_2") {
        if ($B11 == "Horizontal") {
            $sub += 0.25;
        }
        if ($D6 > 12) {
            $sub += 0.25;
        }
    } else if ($B9 == "Option_3") {
        if ($B11 == "Vertical") {
            $sub += 0.25;
        }
        if ($D6 > 12) {
            $sub += 0.25;
        }
    }
    return $x - $sub + 1 - $y * $x;
}

Hopefully this makes the logic seem more readable and understandable as well.