Create Excel Files With Average Formula In PHP Using PHPSpreadSheet

Learn how to create excel files with AVERAGE formula in PHP using PHPSpreadSheet.

You can code and write an average formula that would add values of specific range of cells and get it’s average, or get the total values of only selected cells before getting it’s average.

This article will discuss the basic process of adding cell values together and getting the central value using the average formula in PHP using PHPSpreadSheet. The goals of this tutorial are the following.

1. Get the average value of Cells B1 up to B10.

2. Get the average value of Cell B1 and B10 only.

3. Get the average value of Cell B1 and B2 only.

Requirements:

Composer
PHP 7.2 or newer

Step 1.

Setup dependencies.

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

composer.json

Step 2.

Install phpspreadsheet.

$ composer install

command line

Step 3.

Create a new PHP file, and start coding.

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

// Set the random values container
$randArray = [];

// Set cell B1-B10 values between 0,100 randomly
for ($i=1; $i<11; $i++) {
    // Generate random value
    $rand = rand(0, 100);

    // Set random value to the cell
    $sheet->setCellValue('B' . $i, $rand);

    // Store random value for reference later
    array_push($randArray, $rand);
}

/**
 * Get the average value of Cells B1 up to B10.
 */
$sheet->setCellValue('A12', 'Average value of Cells B1 up to B10.'); // description
$sheet->setCellValue('B12', '=AVERAGE(B1:B' . ($i-1) . ')'); // formula
$sheet->setCellValue('C12', '(' . implode('+', $randArray) . ')/' . count($randArray)); // add the values of B1 up to B10 and divide by 10

/**
 * Get the average value of Cells B1 and B10 only.
 */
$sheet->setCellValue('A13', 'Average value of Cells B1 and B10 only.'); // description
$sheet->setCellValue('B13', '=AVERAGE(B1,B' . ($i-1) . ')'); // formula
$sheet->setCellValue('C13', '(' . $randArray[0] . '+' . $randArray[count($randArray)-1] . ')/2'); // add the values of B1 and B10 and divide by 2

/**
 * Get the average value of Cells B1 and B2 only.
 */
$sheet->setCellValue('A14', 'Average value of Cells B1 and B2 only.'); // description
$sheet->setCellValue('B14', '=AVERAGE(B1,B2)'); // formula
$sheet->setCellValue('C14', '(' . $randArray[0] . '+' . $randArray[1] . ')/2'); // add the values of B1 and B2 and divide by 2

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-excel-files-with-average-formula.xlsx');

create-excel-files-with-average-formula.php

Test.

Run the following codes.

$ php create-excel-files-with-average-formula.php

command line

Result.

Open the generated file create-excel-files-with-average-formula.xlsx.