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.
6examples in this category
21supported categories
5implementation paths
Monolithic library
Formulas examples with copy-ready, production-minded PHP.
Each example identifies the exact methods it uses and can be opened independently from the right-side index.
- 6
- examples
- 3
- documented methods
MnbExcel::formula()currencyColumns()<?php
declare(strict_types=1);
/*
Summary:
Write a SUM formula as a typed cell value. A cached result keeps previews useful before Excel
recalculates.
Implementation note:
Formula strings may include or omit the leading equals sign.
*/
use Mnb\PHPExcel\MnbExcel;
$rows = [
['Month' => 'January', 'Sales' => 1_200],
['Month' => 'February', 'Sales' => 1_650],
['Month' => 'March', 'Sales' => 2_050],
['Month' => 'Total', 'Sales' => MnbExcel::formula('SUM(B2:B4)', 4_900)],
];
MnbExcel::fromArray($rows)
->withHeader()
->currencyColumns(['Sales'], '$')
->save('sum.xlsx');MnbExcel::formula()<?php
declare(strict_types=1);
/*
Summary:
Calculate the average of a numeric range and store a cached result for non-calculating viewers.
Implementation note:
Excel recalculates the formula when the workbook opens.
*/
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');MnbExcel::formula()<?php
declare(strict_types=1);
/*
Summary:
Use COUNT for numeric cells and COUNTA for all non-empty cells.
Implementation note:
COUNT ignores text and blanks; COUNTA counts every non-empty value.
*/
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');MnbExcel::formula()<?php
declare(strict_types=1);
/*
Summary:
Generate row-level status values from a condition.
Implementation note:
Use double quotes inside the formula for Excel text literals.
*/
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');MnbExcel::formula()<?php
declare(strict_types=1);
/*
Summary:
Combine several business conditions in a single formula.
Implementation note:
AND requires every condition to be true; OR requires at least one.
*/
use Mnb\PHPExcel\MnbExcel;
$rows = [
[
'Order' => 1_001,
'Amount' => 2_400,
'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');MnbExcel::formula()MnbExcel::fromWorkbookArray()<?php
declare(strict_types=1);
/*
Summary:
Return a friendly fallback when a lookup or calculation produces an Excel error.
Implementation note:
IFERROR catches all Excel error types. Use IFNA when only a missing lookup should be handled.
*/
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');No matching example found.Try a method name, task, or result.
Was this guide useful?Use GitHub issues for corrections, missing examples, or unclear behavior.
Open an issue