Calculations based on array values

Question

I'm trying to finish a script that I've been working on for a while now and I just can't get this last part figured out.

Currently, the script below gives me this output:

Which is correct except after each users' records I only have the header row for "Mean, Median and Mode". What I'm trying to do is set this up to get the mean median and mode of the 4 data columns (C, F, I and L). I have the titles/ and headers but underneath that I need to display the formulations. So in the image above, where it goes from the MMM row on to the next user, there should be one more row with the MMM calculations but I just can't figure out how to get this sorted.

I might could set variables for mean median and mode formulas, even though PHPSpreadsheet has those formulas built in but i'm not quite sure how to grab the 4 data results from the array per user and calculate them for the next row.

Any ideas?

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    echo "Connected successfully";      

    //Create and run query
    $sql = "
        SELECT CONCAT(u.first_name, '  ', u.last_name) as Name,
               t.ext_id as ID,
               t.total_talk_time_minutes as TalkTime,
               t.total_outbound as Outbound,
               t.total_inbound as Inbound,
               t.dealers_contacted as Dealers,
               t.date_of_report as Date,
               DAYNAME(t.date_of_report) as Day
        FROM ambition.ambition_totals t
        INNER JOIN ambition.ambition_users u
          ON t.extension = u.extension
        WHERE date_of_report  between
            curdate() - interval 5 day and curdate()
        ORDER BY ID";
    $result = mysqli_query($conn,$sql);

    //Start the spreadsheet
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    //Create header row
    $sheet->setCellValue('A1', 'Name');
    $sheet->setCellValue('B1', 'User ID');
    $sheet->setCellValue('C1', 'Talk Time');
    $sheet->setCellValue('F1', 'Outbound');
    $sheet->setCellValue('I1', 'Inbound');
    $sheet->setCellValue('L1', 'Dealers');
    $sheet->setCellValue('O1', 'Date');
    $sheet->setCellValue('P1', 'Day');

    //Preprocess the data into a multi-dimensional array
    //  with the id as the parent index
    $resultData = array();

    while($row = mysqli_fetch_assoc($result)) 

      {
          $resultData[$row['ID']][] = $row;
      }

    //Set starting row number
    $rowNo = 1;



    //Iterate over the results for each unique id
   foreach($resultData as $idRecords)
{
     $userFirstRow = $rowNo+1;

    //Iterate over the records for this ID
    foreach($idRecords as $record)
        {
            //Increment row number
            $rowNo++;
            //Add record row to spreadsheet
            $sheet->setCellValue("A{$rowNo}", $record['Name']);
            $sheet->setCellValue("B{$rowNo}", $record['ID']);
            $sheet->setCellValue("C{$rowNo}", $record['TalkTime']);
            $sheet->setCellValue("F{$rowNo}", $record['Outbound']);
            $sheet->setCellValue("I{$rowNo}", $record['Inbound']);
            $sheet->setCellValue("L{$rowNo}", $record['Dealers']);
            $sheet->setCellValue("O{$rowNo}", $record['Date']);
            $sheet->setCellValue("P{$rowNo}", $record['Day']);

        }

    //Increment row number
    $rowNo++;

    $range = 'C'.$userFirstRow.':C'.($rowNo-1);
    $sheet->setCellValue("C{$rowNo}", "Mean");
    $sheet->setCellValue("C{$rowNo+1}", '=AVERAGE('.$range.')');


}

    $sheet->setCellValue("C{$rowNo}", $average);

    var_dump($resultData);
    var_dump($average);

    $worksheet1 = $spreadsheet->createSheet();
    $worksheet1->setTitle('Department Total');

    //Add in the query for department totals

    //Add results to spreadsheet


    mysqli_close($conn);

    $writer = new xlsx($spreadsheet);
    $writer->save('Coaching Report - Test.xlsx');

    ?>

yes but I'm making a script that runs every friday and goes out to multiple people. The request is to have these populated upon the script running and the amount of users that have data in this report will vary. I can use ```$sheet->setCellValue('C{$rowNo}', "=AVERAGE({cell})")``` to get that functionality but I'm not sure how to integrate it into the loop properly here — Geoff_S, Feb 07 '18 at 19:39

score 1 · Accepted Answer · answered Feb 07 '18 at 19:44

1

As the first action for each user, store the current rowid number so that you can use it in the formula range, so:

foreach($resultData as $idRecords)
{
    $userFirstRow = $rowNo+1;

Then when you need to set the mean, median, mode values

replace

$sheet->setCellValue("C{$rowNo}", "Mean");

with

$range = 'C'.$userFirstRow.':C'.($rowNo-1);
$sheet->setCellValue("C{$rowNo}", "Mean");
$sheet->setCellValue("C{$rowNo+1}", '=AVERAGE('.$range.')');

and equivalent for the other columns

remembering to do

$rowNo++;

afterwards so that you don't overwrite these values

answered Feb 07 '18 at 19:44

Mark Baker

209,507
32
346
385

Ok so I updated my code above to integrate those changes. Just testing now with one column but on the line for ```$sheet->setCellValue("C{$rowNo+1}", '=AVERAGE('.$range.')');``` I get an error that it's expecting '}' rather than a '+' – Geoff_S Feb 07 '18 at 20:52
It seems to print the average correctly if I remove the '+1' though, just not the row with the title – Geoff_S Feb 07 '18 at 20:55
Use `"C" . ($rowNo+1)` then – Mark Baker Feb 08 '18 at 00:06
I had tried that actually, but still just prints the one row there. I'll have to keep playing around with it, but thanks! – Geoff_S Feb 08 '18 at 03:12

Calculations based on array values

1 Answers1