This a post about a handy little formula I came up with on a job last week in order to test formulas from a client’s solution, against a list of specific ‘newish’ Microsoft 365 formulas like LAMBDA() and TOCOL() not present in his version. I needed to replace these formulas with older formulas. How I …
The new Dynamic Array formulas are now in Office 365 Excel! I have been playing around with some of them namely FILTER, UNIQUE and SORT which are awesome! Excel MVPs have helped us all with a ton of videos and posts out there introducing these new functions. Just Google Excel new Dynamic Array formulas and …
Taking the new Dynamic Array formulas out for a spin Read More »
Here is a quick but really useful coffee break dynamic sum formula tip. I used this handy INDEX + MATCH based formula the other day and thought it was a good one to share… Say you have this typical accounting situation where you want to give the user the flexibility to choose the 2 ‘From’ …
Dynamic sum formula for summing between two dynamic columns Read More »
In my recent post of Fix duplicated Conditional Formatting Rules , I mentioned you should be careful using Conditional Formatting in situations where you have thousands of rows of data or calculations as this can be a drain on performance. I also mentioned you can use alternatives in these situations and just thought I would …
I did this very thing today, so thought I would share this tip with you as it saved me a lot of time. Say you wanted to have this formula go into column 8 of the range you created in VBA for the number of dynamic rows you extracted using Advanced Filter. =CHOOSE( OR(C6={“EL”,”IL”})*1+(C6=”MT”)*2+OR(C6={“IP”,”EP”})*3+(C6=”SC”)*4+(C6=”AD”)*5, “Labour”,”Materials”,”Plant”,”SubContract”,”Overheads”) …
Use the macro recorder for VBA code for a relative formula Read More »
A few years ago I picked up on a gem of a tip from Dick Kusleika from his then blog Daily Dose of Excel, on using the Table Name prefix of “tbl”. This site seems to have stopped sadly for Excel nerds, I am not sure if that is temporary or for good, but it …
Update 11/08/21 I received an email from Clem who has written an awesome Add-In called Ribbon Icons which looks like below, to address the painful process I whinge about below for choosing and adding icons to your custom ribbon. I just can’t believe someone has nailed the solution to this horrible process a) you can …
My Custom Ribbon using Formulas to Create XML code Read More »
I have been putting off blogging about this topic because it’s unwieldy to explain and involves work done many years ago that I would do better now. I cringe a bit seeing an Offset formula now which I would only use these days as a last resort because it’s volatile. But let’s see if it …
Using Excel Templates to upload to an accounting software – Part A The Cash Book Read More »
I have blogged before about how much I love INDEX (INDEX what’s the Big Deal ). It’s powerful, nimble (non volatile so easy on performance load) and just makes so much sense to me given we are working with a grid and INDEX essentially returns the contents from the co-ordinates of the grid reference entered. …
Using INDEX to calculate a moving average dynamically Read More »
I have been recently blogging on Array Constants (see below links) and how useful they are for simpler, more maintainable and readable formulas. ttps://www.theexcelfactor.com/using-array-constants-formulas-logic/ https://www.theexcelfactor.com/using-array-constant-list-formulas/ Also I often use SUMIFS which from the top of my head came with Excel 2007. I did a job a few years ago that had another useful application of …