What are the opening hours?

Monday 9am - 5pm Tuesday 9am - 5pm Wednesday 9am - 5pm Thursday 9am - 5pm Friday 9am - 5pm

AbleOwl

AbleOwl builds applications for all sorts of clients, small and large. Excel is the frontend and often the data is stored in the cloud, normally in SQL Server.

The advantages of using Excel as the frontend rather than a browser are many. We also train. Visit our website: https://ableowl.com
Email: [email protected]

About AbleOwl

AbleOwl are a centre of expertise in Microsoft Excel. With over quarter-century of spreadsheet experience, we can double your productivity and spreadsheet quality with our training, publications, service and support. Besides bei

ng the world's most-used spreadsheet, Excel is also the world's most-used software development tool, and a source of great user-empowerment. 99% of Excel users would be lost and unproductive in any other software, dependent on IT specialists for changes. We assist you to improve the software and systems you have, so there is no need to move to an alternative that may or may not deliver what you need. AbleOwl provide:

•AbleOwl Genie - Everything to make you an Excel genius..

•Development of professional, robust and maintainable custom Excel applications.

•Broadest range of Over 20 Excel courses ranging from beginner through to advanced level, including some specifically devoted to the application of Excel in particular professions. Tutorial-standard course material is available for all courses.

•Three monthly subscription Excel publications.

•ESP (Excel Standardisation Programme): A set of conventions, standard components, tools and training to rapidly develop Excel applications.

•Hotline support.

•Mentoring. AbleOwl have offices in Australia and New Zealand. Australia
AbleOwl Spreadsheets Ltd
Suite 2, 345 Pacific Highway, Lindfield
Sydney NSW 2070
Tel Sydney +61 (2) 9496 2330

New Zealand
AbleOwl XL Limited
PO Box 11069
Hillcrest, Hamilton 3251
Tel +64 (7) 854 9276

04/01/2025

Unwrap a table into a single column to then chart.

There is data by year and month in A1:K13 below. You need to create a chart as below of that data. In order to do that, you must unwrap the data so that it appears in a single column as in C15 and down in the image below.

Excel has WRAPROWS and WRAPCOLS functions to turn a single column or row range into a table, but there are no functions to UNWRAP.

However, you can use INDEX and some other functions to accomplish the unwrap.

Let's start with a simple example of INDEX. INDEX(B2:K13,2,3) returns 873,540, that is, the value from the second row and third column of the range.

The source data is in an Excel Table, which has a name dtbActual. dtbActual[[2015]:[2024]) is a table reference, which is the range B2:K13.

The second argument of INDEX is

MOD(ROW(B15)-ROW($B$15)+1,12)+IF(MOD(ROW(B15)-ROW($B$15)+1,12)=0,12,0)

ROW(B15) returns 15, the row number of B15.

You copy the formula in C15 to C16:C134. Therefore, in C16, the formula contains ROW(B16)-ROW($B$15)+1, the result of which is 2.

MOD returns the remainder after dividing by a number. MOD(2,12) returns 2 as does MOD(14,12). MOD(12,12) returns 0. However, you want the second argument of INDEX to return a number 1 to 12, that is, when the number is 12 or a multiple of 12, return 12 rather than 0.

Hence, the IF function adds 12 when the the number returned by MOD(ROW(B15)-ROW($B$15)+1,12) is zero. Else, IF adds zero.

The third argument of INDEX is

ROUNDUP((ROW(B15)-ROW($B$15)+1)/12,0))

For the first 12 cells, that is, C15:C26, it returns 1. For the next 12, it returns 2, etc.

ROUNDUP rounds up to a whole number and, so, 1/12, 11/12 and 12/12 all round up to 1. 13/12, 23/12 and 24/12 all round up to 2, etc.

There is data missing in 2015, that is, certain cells are blank. Rather than returning blank for blank cells, the formula returns zero. It's not that the data were zero in those months. You don't want the chart to plot those months as zero. You can get the chart to leave a point unplotted by having /A for the value; it is not possible to have a formula return a blank, but it can return /A.

So, you can add an IF function in the form of IF(X=0, /A,X), where X is the INDEX formula. To avoid repeating that long formula twice, you can make use of LET as in LET(X, long formula, IF(X=0, /A,X))

X is any letter or word you choose.

long formula is the INDEX formula.

https://github.com/adminableowl/ExcelFreetip1017

04/08/2024

List items in one list not in the other

There are lists A and B below. The task is to list the items that are in both lists, in B but not in A and in A but not in B. In T15, X15 and AA15, there are spill formulas that return those lists. The list A and B source data are in Excel Tables named dtbListA and dtbListB. If List A and List B changes in content or length, the output changes automatically.

The formulas use MATCH. In Q15, MATCH searches for Queso in dtbListA[Product], which is the range K15:K39. It finds a match in position 17, the 17th cell of the range searched. The first argument of MATCH, that which it searches for, is a range and, so, it returns a range of results, which spills into the cells below. Where there is no match, MATCH returns /A.

In Q15, INDEX returns the value from the cell in the17th position of dtbListA[Value], that is, from L31.

Note that the second argument is Q15 #. The # signifies that the reference is to the whole spill range that starts at Q15. You could instead include Q15:Q27, however, Q15 # is preferable because if the range becomes longer, Q15;Q27 remains as so, but Q15 # refers to the whole range.

You might think that you could make Q15:Q27 automatically extend if you made the output range Q14:R27 an Excel Table, but, alas, you cannot have spill formulas in a Table.

The /A rows are not wanted. The solution is to combine FILTER, ISNUMBER and MATCH as in T15. ISNUMBER returns an array of TRUEs and FALSEs. FILTER has two arguments. Where the corresponding value in the second argument is TRUE, it returns the row range of the first argument. Hence, T15 returns just the non- /A rows from Q15:R27.

V15 needs another spill formula. It has =INDEX(dtbListA[Value],FILTER(MATCH(dtbListB[Product],dtbListA[Product],0),ISNUMBER(MATCH(dtbListB[Product],dtbListA[Product],0))))

MATCH returns the match positions. FILTER returns only those that are not /A. INDEX returns the values of L15:L39 at the match positions.

04/02/2024

Tip:932 Use LET to make a formula easier to understand.

The task below is to extract into column L the middle name of the text in column K.

You can use a combination of MID and FIND functions to do that as in L15. As per the previous tip, you can make the formulas easier to follow by splitting the formula onto multiple lines with Alt+Enter.

MID returns a number of characters from a starting position. For example, MID("abcdef",2,3) returns bcd being 3 characters starting from position 2.

FIND searches for text and returns the position of the match. For example, FIND("c","abcdabcd") returns 3, the position of the first c. There is an optional third argument, which is the position to start searching from. For example, FIND("c","abcdabcd",4) returns 7, the position of the first c found from a search starting at position 4.

Unless you know FIND well, the formula is hard to understand. With LET, a relatively new function, you can make the formula easier to understand.

Let's start with simple examples of LET. The examples below are from the Excel help.
You can store values into what it calls variables. Example 1 stores 1 into a variable named x. The last argument is called the calculation, which in this case is the calculation of x+1. The result returned is 2.

Example 2, has two variables named x and y. LET stores 1 into each variable. The calculation is x+y and, so, the result is 2 again.

You can have up to 126 variables. In L16 above, there are three: FirstSpace, SecondSpace and MiddleName.

Into FirstSpace, FIND puts the position of the first space.
Into SecondSpace, FIND puts the position of the second space.
Into MiddleName, MID returns the text that starts from 1 character after the FirstSpace and which is SecondSpace-FirstSpace characters long, that is, the length of the middle name.

For the last argument, there is no further manipulation required; just return the MiddleName.

You could remove the two arguments that contain variable name MiddleName, but doing it this way makes it clear that the formula returns the middle name.

Excel file:
https://github.com/adminableowl/ExcelFreeTip932.git

28/03/2022

Make your nested IF formulas easier to understand
A formula like that below is hard to follow. It just wraps around in the Formula bar. It's a typical formula with nested IFs.

=IF(AO505=0,"N/A",IF(OR($AP$505="Tiles supplied by customer",AO498="No"),"Coping tiles supplied by customer.",IF($AR$505,"See Options", IF(OR($AP$505="", $AP$505="Not selected"), AP507,"Coping tiles supplied from "&AP505&" - "&AQ505))))

You can rearrange the formula as below to make it easier to follow. IF has three arguments: (1) Test (2) Value if test is true (3) Value if test is false. Note the layout below where the second argument is below the IF and indented by 4 spaces. The third argument is on the next line down and also indented by 4 spaces.

With the indentation below, you can see the four levels; the first IF contains another IF, which contains another, which contains another. The logic flow is now much easier to follow.

=IF(AO505=0,
"N/A",
IF(OR($AP$505="Tiles supplied by customer",AO498="No"),
"Coping tiles supplied by customer.",
IF($AR$505,
"See Options",
IF(OR($AP$505="", $AP$505="Not selected"),
AP507,
"Coping tiles supplied from "&AP505&" - "&AQ505))))

In creating a formula, to start a new line, press Alt+Enter
To indent, simply type spaces. We recommend you enter 4 spaces to indent a level.

11/07/2021

Excel tip #928 - Swings and roundabouts

Take a look at the image for this post. Each of the columns rounds the first Values column ... but the answers aren't all the same! See the orange rows.

So what's going on here?

Excel's ROUND() formula uses arithmetic rounding - that's the type we did in school where 5 was always rounded up. So, ROUND(1.125,2) yields 1.13.

The cell formatting system (something like: #, # #0.00;- #, # #0.00) also uses arithmetic rounding.

However, users of VBA will know that its Round() function uses bankers rounding - where 5 is rounded to the nearest even number. So, Round(1.125,2) in VBA would return 1.12.

For statisticians, the method of rounding is important, especially to avoid data being skewed by always rounding 5 up. Meteorologists, for example, have long debated how to round, say, fractions of rainfall.

For the rest of us, it's not so important ... as long as we're consistent. The problem comes when we're comparing data that has come from different sources (which use different rounding methods). And it's more common than you might think, because PowerQuery uses bankers rounding. Use PowerQuery to read invoices paid and Excel's ROUND() function to compare with invoices sent and, boom, you've got mysterious 0.01 differences.

If you know that you're going to need bankers rounding in your Excel formulas, then this one is often used:

=ROUND(cell,2)-(MOD(ROUND(cell*10^(2+1),0),20)=5)/10^2

This is the one used in the last column of the image above.

Alternatively, you could create your own VBA function:

Public Function BROUND(d As Double, n As Long) As Double
BROUND = Round(d, n)
End Function

For PowerQuery, always set the rounding method to match what you intend to use in Excel. There are a number of options, here's one:

Number.Round(1.125, 2, RoundingMode.AwayFromZero)

04/07/2021

Excel Tip #927 - Unreadable IFs

It doesn't take much for an IF statement to become cumbersome and difficult to read. This one, for example, just writes "1st", "2nd" or "3rd" for the results of a running race:

=IF(M15=1,"1st",IF(M15=2,"2nd",IF(M15=3,"3rd","")))

The more embedded the IF conditions, the harder it is to see which condition yields which result.

In those cases, the SWITCH function could be of help to you. Here, you just enter each condition for a particular cell and the desired return value. So the equivalent 1st, 2nd, 3rd function would look like this:

=SWITCH(M15,1,"1st",2,"2nd",3,"3rd")

The only issue with SWITCH is that it can only handle exact matches; that's to say you can't use operators like 'greater than', etc.

Suppose you also gave runners a medal, ribbon or certificate if their times were above certain values. This is where the IFS function comes to the rescue. It works in a similar way to SWITCH but you can apply operators:

=IFS(L15

24/06/2021

Excel Tip #926 - INDEX to return more than one item

The INDEX function can return more than one item. Usually we use the syntax INDEX(array,row_num,col_num) and the function would return a single item. For example, INDEX({10,20,30,40,50},2,1) would return 20.

However, you can pass an array as a row argument, and INDEX would return each item in that row array. INDEX(your range,{1,3,5}) would return the 1st, 3rd and 5th items, for example. The function returns a Spilled Range, so you just have to make sure that, in this case, there are three blank cells, and your INDEX formula just goes in the first of those cells.

It's a really handy capability, especially if you combine INDEX with the SEQUENCE function (which creates an array). It gives you the ability, for example, to select the top n items in a table. Suppose you were a woodworker with a project away from your workshop and could only take your 3 sharpest chisels with you, you'd simply combine the INDEX, SORT and SEQUENCE functions. The image below shows you how.

And if you find out you've room for one more chisel, then just change that 3 to a 4, and you now have your 4 sharpest chisels ... no change to formulas is required.

Of course, you could change the SORT parameter from -1 to 1, and now you've got yourself a weekend task of sharpening your n dullest chisels - better get those water stones ready!

Where I've found this feature really useful is in grabbing certain columns for a table, but not the entire table. I can simply enter the column indexes I'm after in an INDEX(col_array) call. Or as in the example above when I want to grab data from a dynamic filter.

Address

125 Matangi Road
Hamilton

Opening Hours

Monday	9am - 5pm
Tuesday	9am - 5pm
Wednesday	9am - 5pm
Thursday	9am - 5pm
Friday	9am - 5pm

Website

http://ableowl.com/

Alerts

Be the first to know and let us send you an email when AbleOwl posts news and promotions. Your email address will not be used for any other purpose, and you can unsubscribe at any time.

Contact The Business

Send a message to AbleOwl:

Shortcuts

Want your business to be the top-listed Computer & Electronics Service in Hamilton?