MNB PHPExcelDeveloper Guide
v2.0.5

Formulas examples

Formulas Excel examples, explained clearly.

Open a focused example to see the implementation path, copy-ready PHP, expected workbook behavior, and the production boundary that matters.

Open first example View all 92 examples
6examples in this category
21focused categories
5implementation paths
← All 92 examples
01
SUM

Write a SUM formula as a typed cell value. A cached result keeps previews useful before Excel recalculates.

EasyExcel formula
MnbExcel::formula()currencyColumns()
sum.php
<?php

use Mnb\PHPExcel\MnbExcel;

$rows = [
    ['Month' => 'January', 'Sales' => 1200],
    ['Month' => 'February', 'Sales' => 1650],
    ['Month' => 'March', 'Sales' => 2050],
    ['Month' => 'Total', 'Sales' => MnbExcel::formula('SUM(B2:B4)', 4900)],
];

MnbExcel::fromArray($rows)
    ->withHeader()
    ->currencyColumns(['Sales'], '$')
    ->save('sum.xlsx');
02
AVERAGE

Calculate the average of a numeric range and store a cached result for non-calculating viewers.

EasyExcel formula
MnbExcel::formula()
average.php
<?php

use Mnb\PHPExcel\MnbExcel;

$rows = [
    ['Agent' => 'Ava', 'Score' => 88],
    ['Agent' => 'Noah', 'Score' => 94],
    ['Agent' => 'Mia', 'Score' => 82],
    ['Agent' => 'Average', 'Score' => MnbExcel::formula('AVERAGE(B2:B4)', 88)],
];

MnbExcel::fromArray($rows)
    ->withHeader()
    ->save('average.xlsx');
03
COUNT & COUNTA

Use COUNT for numeric cells and COUNTA for all non-empty cells.

EasyExcel formula
MnbExcel::formula()
count-counta.php
<?php

use Mnb\PHPExcel\MnbExcel;

$rows = [
    ['Value' => 10, 'Description' => 'Approved'],
    ['Value' => 20, 'Description' => 'Pending'],
    ['Value' => null, 'Description' => 'Manual review'],
    ['Value' => 40, 'Description' => 'Approved'],
    ['Value' => MnbExcel::formula('COUNT(A2:A5)', 3), 'Description' => 'Numeric count'],
    ['Value' => MnbExcel::formula('COUNTA(B2:B5)', 4), 'Description' => 'Non-empty count'],
];

MnbExcel::fromArray($rows)
    ->withHeader()
    ->save('count-counta.xlsx');
04
IF Function

Generate row-level status values from a condition.

MediumExcel formula
MnbExcel::formula()
if-function.php
<?php

use Mnb\PHPExcel\MnbExcel;

$rows = [
    ['Student' => 'Ava', 'Score' => 84, 'Result' => MnbExcel::formula('IF(B2>=70,"Pass","Review")', 'Pass')],
    ['Student' => 'Noah', 'Score' => 62, 'Result' => MnbExcel::formula('IF(B3>=70,"Pass","Review")', 'Review')],
];

MnbExcel::fromArray($rows)
    ->withHeader()
    ->save('if-function.xlsx');
05
AND / OR

Combine several business conditions in a single formula.

MediumExcel formula
MnbExcel::formula()
and-or.php
<?php

use Mnb\PHPExcel\MnbExcel;

$rows = [
    [
        'Order' => 1001,
        'Amount' => 2400,
        'Paid' => true,
        'Ready' => MnbExcel::formula('AND(B2>=1000,C2=TRUE)', true),
        'Escalate' => MnbExcel::formula('OR(B2>=5000,C2=FALSE)', false),
    ],
];

MnbExcel::fromArray($rows)
    ->withHeader()
    ->save('and-or.xlsx');
06
IFERROR

Return a friendly fallback when a lookup or calculation produces an Excel error.

MediumExcel formula
MnbExcel::formula()MnbExcel::fromWorkbookArray()
iferror.php
<?php

use Mnb\PHPExcel\MnbExcel;

$orders = [
    ['SKU' => 'P-100', 'Product' => MnbExcel::formula('IFERROR(VLOOKUP(A2,Products!A:B,2,FALSE),"Not found")', 'Laptop')],
    ['SKU' => 'P-999', 'Product' => MnbExcel::formula('IFERROR(VLOOKUP(A3,Products!A:B,2,FALSE),"Not found")', 'Not found')],
];

$products = [
    ['SKU' => 'P-100', 'Product' => 'Laptop'],
    ['SKU' => 'P-200', 'Product' => 'Monitor'],
];

MnbExcel::fromWorkbookArray(['Orders' => $orders, 'Products' => $products])
    ->withHeader()
    ->save('iferror.xlsx');