Skip over navigation
Guide and features
Guide and features
Science, Technology, Engineering and Mathematics
Featured Early Years Foundation Stage; US Kindergarten
Featured UK Key Stage 1&2; US Grades 1-5
Featured UK Key Stage 3-5; US Grades 6-12
Featured UK Key Stage 1, US Grade 1 & 2
Featured UK Key Stage 2; US Grade 3-5
Featured UK Key Stages 3 & 4; US Grade 6-10
Featured UK Key Stage 4 & 5; US Grade 11 & 12
Excel Technique: Triangular Arrays by Turning Off Zeros
Stage: 3 and 4
Make a line of zeros ( about 10 ).
Put zero in B2 and press Enter.
Then click and drag the bottom right-hand corner to the right.The contents of the cell (zero) will be replicated as far as you drag.
Click on B3 and type the formula:
= A2 + C2
This formula creates a sum using the two cells immediately above, left and right.
Press Enter, then click and drag the bottom right-hand corner as before. The whole row now has that kind of formula.
Highlight the row and, again using the bottom right-hand corner, drag down for about five rows.
What you now have is an array of connected values.
The first row is all set to zero, and all the cells in the rows below contain a formula which creates a sum using the two cells immediately above it, left and right.
Go to a middle cell in the first row and change the value to a 1. Press Enter and check the effect.
Finally go to the Tools drop down menu and choose Options.
Check you have the View tab selected, you will see the option to not show zero values.
Click OK and a triangular array will present itself.
Meet the team
The NRICH Project aims to enrich the mathematical experiences of all learners. To support this aim, members of the NRICH team work in a wide range of capacities, including providing professional development for teachers wishing to embed rich mathematical tasks into everyday classroom practice. More information on many of our other activities can be found here.
Register for our mailing list
Copyright © 1997 - 2014. University of Cambridge. All rights reserved.
NRICH is part of the family of activities in the
Millennium Mathematics Project