Skip to main content
SheetTutor.
← All tasks↓ Sample workbook

Compare formula operators and their evaluation order

Read-only

Step-by-step guide · read-only · Selected J2

Loading your interactive spreadsheet…

Preparing the worksheet and formulas…

Data & tools
Task requirements

Apply J2 once as a three-row SEQUENCE, leaving J3:J4 as calculated children. Enter G2:G25 and L3 separately; these report rows do different jobs and must not be filled from one formula. B2:D4 are whole numbers -100..100 with C2 and D2 nonzero. B6 is whole percent 0..100, B7 exponent 0..5, B8 literal printable ASCII label text 1..30 characters and B9 whole sequence step -100..100. Exclude 0^0. Compare multiply-before-add with grouping, subtraction/product/division, negation-before-power with negating the square, controlled power and actual percent syntax, all six numeric Boolean comparisons and literal text concatenation. G18 sums the rectangle, G19 uses a parenthesized disjoint-area union, G20 the overlapping rectangle intersection, G21 the declared No shared cells fallback for the empty intersection. G22 sums the entire actual J2# spill, G23/G24 compare left-to-right division/multiplication with a grouped denominator, and G25 applies same-precedence powers left to right. L3 uses @ on B2:B4 and must select the original row-3 value. Preserve the written operators and real references; independent source/probe checks protect the complete numeric/text/Boolean report and exact J2:J4 helper extent.

Follow the highlighted cells. Switch to Your turn to edit. Answer cells: J2, J3, J4, G2, G3, G4, G5, G6, G7, G8, G9, G10, G11, G12, G13, G14, G15, G16, G17, G18, G19, G20, G21, G22, G23, G24, G25, L3. Browser calculations may differ from your Excel version.

Inspect current operator results, helper cells and formulas
CellCurrent valueApplied formula
J2—No formula
J3—No formula
J4—No formula
G2—No formula
G3—No formula
G4—No formula
G5—No formula
G6—No formula
G7—No formula
G8—No formula
G9—No formula
G10—No formula
G11—No formula
G12—No formula
G13—No formula
G14—No formula
G15—No formula
G16—No formula
G17—No formula
G18—No formula
G19—No formula
G20—No formula
G21—No formula
G22—No formula
G23—No formula
G24—No formula
G25—No formula
L3—No formula

Change an actual source value

B2:D4 are whole numbers −100..100; C2 and D2 stay nonzero for division. B6 is a whole percent 0..100, B7 an exponent 0..5, B8 nonempty printable ASCII label text up to 30 characters, B9 a whole sequence step −100..100. Zero to the zeroth power is outside this finite numeric report. Enter the sequence once in J2 and leave J3:J4 as spill children.

Current B2: Number 3

This draft matches the current cell.

Selection & formula search

Practice stays on this device. No worksheet data is uploaded.

Notes & examples
About this lesson
Formula foundations25 min·Growing

Compare all four operator classes on real inputs: numeric order, Boolean comparisons, text joining and range/union/intersection references, plus an actual spill helper and row-dependent implicit intersection.

THE QUICK ANSWER01

Compare all four operator classes on real inputs: numeric order, Boolean comparisons, text joining and range/union/intersection references, plus an actual spill helper and row-dependent implicit intersection.

J2=SEQUENCE(3,1,B2,$B$9)
G2=B2+C2*D2
G3=(B2+C2)*D2
G4=B2-C2
G5=B2*C2
G6=B2/C2
G7=-B2^2
G8=-(B2^2)
G9=B2^$B$7
G10=B2*$B$6%
G11=B2=C2
G12=B2<>C2
G13=B2>C2
G14=B2<C2
G15=B2>=C2
G16=B2<=C2
G17=$B$8&": "&B2
G18=SUM(B2:D4)
G19=SUM((B2:B4,D2:D4))
G20=SUM(B2:D3 C3:D4)
G21=IFERROR(SUM(B2:B4 D2:D4),"No shared cells")
G22=SUM(J2#)
G23=B2/C2*D2
G24=B2/(C2*D2)
G25=B2^2^3
L3=@B2:B4
For the example data
J2 3J3 5J4 7G2 13G3 16G4 -2G5 15G6 0.6G7 9G8 -9G9 27G10 0.75G11 falseG12 trueG13 falseG14 trueG15 falseG16 trueG17 Batch: 3G18 54G19 34G20 14G21 No shared cellsG22 15G23 1.2G24 0.3G25 729L3 4

Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.

UNDERSTAND THE IDEA

How it works

Operator choice changes both value and type

Arithmetic produces numbers, comparisons produce Booleans, and concatenation produces text. Reference operators choose cells before their values are consumed. The same visible characters inside a quoted label are literal text.

Parentheses document a calculation policy

Group an addition, denominator or powered expression when that is the intended calculation. Equal-precedence operators follow their declared left-to-right order; a familiar algebra convention is not a substitute for observing the actual formula.

A spill helper is one formula with multiple results

Leave the declared child cells clear. Check the live three-row extent as well as the numeric report. Current formula and values-only copies retain the applied sources, typed results and helper values; the formula copy retains its real anchor and array extent.

Read the full step-by-step walkthrough
  1. Start from stored inputs and a real spill

    B2:D4 are numeric cells. J2 creates three values from B2 and the B9 step. J3:J4 are results of that one formula, not separately written answers. Change a source in Practice to see the whole helper recalculate.

  2. Compare normal order with explicit grouping

    G2 multiplies before adding. G3 groups the addition first. G23 follows equal-precedence division/multiplication left to right; G24 groups the entire denominator. These are different formulas, not display rounding.

  3. Separate unary negation, percent and exponentiation

    Excel applies negation before power: -B2^2 differs from -(B2^2). Percent converts B6 to a hundredth before multiplication. G9 uses the exponent control; G25 evaluates successive powers left to right.

  4. Read six comparisons as logical values

    Equality, inequality and the four ordered comparisons return actual Booleans. Equality belongs to both >= and <=. Try the Equal population or change B2/C2 together; do not replace TRUE/FALSE with their text spelling.

  5. Join a readable label without changing its source

    The ampersand joins B8, a literal separator and the numeric B2 value as text. The original B8 spelling stays intact, including spaces and punctuation.

  6. Choose a rectangle or a union of separate areas

    A colon selects a continuous rectangle. The parenthesized comma union in G19 selects the first and third input columns, retaining every cell of each area. It is one reference argument to SUM, distinct from commas separating function arguments.

  7. Select only shared cells with a space intersection

    The shared rectangle of B2:D3 and C3:D4 is C3:D3. G20 sums those actual cells. The two columns in G21 do not intersect, producing #NULL! inside the expression; IFERROR supplies the declared label. Spaces inside quoted text are not reference operators.

  8. Reference the complete spill and observe implicit intersection

    G22 uses J2# to follow the whole three-cell spill. L3 uses @B2:B4 and selects B3 because the formula is in row 3. These are different from an explicit space intersection. Moving an implicit-intersection formula can change its selected source.

  9. Recheck current results after a source change

    In Your turn change a matrix value, sequence step or label. Inspect the same native worksheet and formula bar, then check the complete original/changed report. A typed result, wrong grouping or blocked spill must not earn a pass.

WHEN SOMETHING LOOKS OFF

Things to watch

Ordinary spaces are confused with reference intersections

A space between two valid reference expressions selects common cells. A quoted space belongs to text; spaces around ordinary arithmetic can just be whitespace.

An area union becomes a box covering the gap

The disjoint B and D columns exclude the C column. The source tests change excluded values independently.

Negation follows a different programming-language rule

Observe the actual spreadsheet result of -B2^2 and compare it with -(B2^2), instead of assuming both mean the same expression.

A helper child is edited or the spill is too large

Restore the one J2 sequence and leave exactly J3:J4 as its children. Shape and live current results must agree before source variants are assessed.

About this example

Original bounded numeric/ASCII operator comparison. Native-client recalculation, international coercion and unrestricted reference grammar remain separately unverified.

Browser: testedExcel: not verifiedGoogle Sheets: not verified

Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.

Reference material