Arrays

Home/Arrays

Tricking Non-Dynamic Array Formulas into Spilling

Tricking Non-Dynamic Array Formulas into Spilling by Jeff Robson   Tricking non-dynamic array formulas into spilling isn't something you'd naturally think of doing. Dynamic Arrays are great but sometimes the formulas that refer to them behave in unexpected ways, and sometimes these don't spill ... without any obvious reason. For example, the DATE function spills, [...]

2020-07-02T12:43:16+08:00Arrays, Functions|

Corkscrew Calculations using Dynamic Array Functions

by Jeff Robson Corkscrew Calculations using Dynamic Array Functions If you've come to this page expecting information about how to open your wine bottles, you're going to be very disappointed!  Sorry! The corkscrew calculations we're talking about here are those you'd use when calculating a balance sheet account in a financial model. Accounts Receivable Example [...]

Financial Modelling using Dynamic Array Functions – no Copying & Pasting!

by Jeff Robson Financial Modelling using Dynamic Array Functions Best practice financial modelling has always been to enter your formulas in blocks: enter a formula, then copy this across and possibly down also, making sure you have your absolute and relative references set correctly. This is very useful because it is faster to build a [...]

Multi-Criteria SUMIF

Multi-criteria SUMIF I use SUMIF to sum a range based on one condition. Is there a way to sum a range based on two or more conditions? By Neale Blackwood One way is to use SUMPRODUCT. The following formula will sum the range C1:C10 based on the contents of A1:A10 and B1:B10. Cell E1 [...]

2017-03-08T03:53:56+08:00Arrays|