08 6210 8500
by Wyn Hopkins Wordle for Power BI Fans We've updated the Wordle Game for all our Power BI Fans out there. All the words are related to Power BI in some way. Click on the file below to play along! *Word of caution though, the file won't open on iPhones. Download a copy of [...]
by Wyn Hopkins Learning Dynamic Array formulas in Excel by building Wordle This video shows the use of Dynamic Array formulas # references, XLOOKUP, SEQUENCE, MID, TODAY and some conditional formatting. Click on the file below to play along! Download a copy of the file we used below:
by Jeff Robson Fixed vs Variable Loan Products ... which is best? It can be difficult to compare fixed vs variable loan products. Banks often market loans with fixed interest rates using the selling point of certainty: "you'll always know what your repayments will be". Check the fine print! While certainty can be somewhat attractive, [...]
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, [...]
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 [...]
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 [...]
Last Cell used on a Row Find the reference of the last cell used in a row By Neale Blackwood One of the articles on our site allows you to find the cell reference of the last used cell in a column. You can see it below in the related articles. Someone asked if there [...]
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 [...]
SUMIF - more than one condition Q: I know how to use SUMIF with one matching criteria, but how can I use more than one? By Jeff Robson Short Answer: you can't. SUMIF only accepts one criteria. Use the SUMIFS formula instead as it allows this (Excel 2007+) Long Answer: you can still use [...]
Last Used Cell in a Row Find the reference of the last used cell in a row By Neale Blackwood One of the articles on our site allows you to find the cell reference of the last used cell in a column. You can see it below in the related articles. One of our [...]
Formula for the last used cell in a column Is there a formula to find the last used cell in a column? By Neale Blackwood You can use an array formula to find the last used cell reference. The following formula will find the last used cell in column A, except if the last [...]
Reach out to the Access Analytic team Monday to Friday on (08) 6210 8500 or submit your contact information and we will be in touch.
Get Ahead of the Game – Book Your Training Now!
Wyn has been awarded MVP status by Microsoft every year since 2017 and is the author of the book “Power BI for the Excel Analyst”. He is known worldwide for sharing his knowledge via the Access Analytic YouTube channel with over 5 million views and 90,000+ subscribers.
He gained his Chartered Accountant qualification at PwC in 2000 and has extensive Business Analyst experience with HBOS and Barclays in the UK plus multinational clients in Perth.
Wyn is skilled in Excel and Power BI development, Power Query automation, management reporting and is an experienced Excel and Power Bl trainer.
Pay my invoice
