Changing a spreadsheet formula so it works with PHP

Question

Hello, for years now I have used a math formula to determine relative activity levels for message boards. Now I would like to use that formula in a php search engine to replace the non-functioning Google Page Ranking system.

The data items used are: Members (B2), Posts (D2), Topics (C2), and the Boards creation date (E2). In the spreadsheet the base formula looks like this:

=SUM(((((((B2/E2)+(C2/E2)+(D2/E2)))*0.419)))+((((((B2/C2)+(B2/D2))/2)+(((C2/B2)+(D2/B2))/3)-3.4777)))/7)+0.0017

The hard (non-generated) numbers are numbers I calculated by hand to provide output that seems to provide results close to what would be Alexa Traffic rankings and I modify them each year. The output is always held to 4 decimal places.

Answer 1

Without some more detail about the actual spread sheet, it's very very difficult to come up with a working solution. In general though, you'd probably want to write a function that looks something like this:

function calculate_ranking($members, $posts, $topics, $creation_date) {
    return ((((((($members/$creation_date)+($topics/$creation_date)+($posts/$creation_date)))*0.419)))+(((((($members/$topics)+($members/$posts))/2)+((($topics/$members)+($posts/$members))/3)-3.4777)))/7)+0.0017;
}

If you post some example rows from your spreadsheet it will be much easier to craft a solution that works. I'm not sure I fully understand what your call to the sum function did, so I removed it from my example.

Answer 2

First of all, your equation can be simplified quite a lot. Now, you have this:

=SUM(
    (
        (
            (
                (
                    (
                        (B2/E2) + (C2/E2) + (D2/E2)
                    )
                )
                *
                0.419
            )
        )
    )
    +
    (
        (
            (
                (
                    ( (B2/C2) + (B2/D2) ) / 2
                )
                +
                (
                    ( (C2/B2) + (D2/B2) ) / 3
                )
                -
                3.4777
            )
        )
    )
    /
    7
)
+
0.0017

The SUM function seems useless, as you have only distinct cells, not ranges. Also, a lot of the parenthesis can be removed. Thus, the function becomes this:

(
    (
        B2/E2 + C2/E2 + D2/E2
    )
    *
    0.419
    +
    (
        ( B2/C2 + B2/D2 ) / 2
        +
        ( C2/B2 + D2/B2 ) / 3
        -
        3.4777
    )
    /
    7
)
+
0.0017

Writing that as an one liner:

0.0017 + ((B2/E2 + C2/E2 + D2/E2) * 0.419 + ((B2/C2 + B2/D2) / 2 + (C2/B2 + D2/B2) / 3 - 3.4777) / 7)

To make it PHP, substitute the cell addresses with variables:

0.0017 + (($members / $date + $topics / $date + $posts / $date) * 0.419 + (($members / $topics + $members / $posts) / 2 + ($topics / $members + $posts / $members) / 3 - 3.4777) / 7)

You probably want to make a function out of that that will return the result rounded in 4 decimal places:

function ranking($members, $posts, $topics, $date) {
    $ranking = 0.0017 + (($members / $date + $topics / $date + $posts / $date) * 0.419 + (($members / $topics + $members / $posts) / 2 + ($topics / $members + $posts / $members) / 3 - 3.4777) / 7);

    return round($ranking, 4);
}

And you could call that function like this (I have no idea what the date E2 looks like, thus 30000):

$ranking = ranking(50, 60, 20, 30000);

Hope that helps.