February 06, 2011

Networkdays Per Week Between 2 Dates

Gee, time flies when you are moping around not posting on your Excel blog. In 2009 I posted a blog entry called Days Per Week Between 2 Dates. At the end I noted that the formula did not take weekends or public holidays into account. Recently I made a formula that did.

Here it is in all of it's unwieldy glory. Note that Cell A2 is the Start Date, Cell B2 is the End Date and Cells in Row 1 refer to the first day in the weeks that you wish to calculate the workdays. The named range refers to a Dynamic List that contains public holidays. The formula can be entered in Cell C2 and dragged to the right to populate cells in the second row - D2, E2, F2 and so on.


And a pic of it in action. Note that 26 January is a holiday - Australia Day - and it's on a Wednesday. So the week starting on 22 January only has 4 working days instead of the usual 5. (I conveniently ignored the fact that 1 January is New Year's Day though!)

Now, I just know that somebody is going to dazzle me with a much shorter version (or at least, I hope they are). Have at it ladies and gentlemen! I await your formula to add at the bottom of this blog post. Good luck ;-)


Posted by andrewe at 10:05

October 31, 2010

Calculate Time

Adding or subtracting days to a date in Excel is easy but, unless I am missing something, adding hours to a time is a bit harder. Yes, you could use inbuilt functions TIME, with HOUR, MINUTE and SECOND to do it but it's still not the same if you just want to add time in decimals, eg the original time + 2.75 hours. You have to convert the decimal part to minutes and even though it's a simple enough formula to write, it is a bit unwieldy. So I decided to make a custom function that would do the job.

Here is the code,

Function CalculateTime(dOriginTime As Date, dbChangeTime As Double, Optional sFormat As Variant)
    Dim iHour As Integer
    Dim iMinute As Integer
    Dim iChangeHour As Integer
    Dim dbChangeMinute As Double

    iHour = Hour(dOriginTime)
    iMinute = Minute(dOriginTime)
    iChangeHour = Int(dbChangeTime)
    dbChangeMinute = dbChangeTime - Int(dbChangeTime)
    dbChangeMinute = dbChangeMinute * 60

    CalculateTime = TimeSerial(iHour + iChangeHour, iMinute + dbChangeMinute, 0)

    If Not IsMissing(sFormat) Then
        CalculateTime = Format(CalculateTime, sFormat)
    End If

End Function

Here is how it works,

My original time is in cell A1. I want to add 2.75 hours (I'm not concerned with the seconds), and I decide to add an optional format of "hh:mm AM/PM" (don't include this argument if you want to use NumberFormat instead)

So my formula is written as,

=CalculateTime(A1,2.75,"hh:mm AM/PM")

And here is how it looks,

Hope it comes in handy.

Posted by andrewe at 11:12

September 03, 2009

Days Per Week Between 2 Dates

Out of all the formulas (formulae to the more educated than me) that I work with, I would say that Date formulas are the hardest to get right. Maybe not so surprising considering how weeks don't divide nicely into months. It really is very naughty of them.

Anyway, I put together a formula to work how many days exist in each week between 2 dates. For it to work as expected, the following must be true -

  • The end date must be equal or higher than the start date (D'oh!)

  • The start date must be equal to or higher than the first week's date

  • The end date must be equal to or lower than the last week's date + 6

    Weeks that are not within the date range show as blank. Note the formula also includes the start date as being a day, so if we consider the total days, the calculation would be

    End Date - Start Date + 1 Day

    And the formula? With the Start Date and the End Date being in Cells A2 and B2 respectively, select the appropiate cells starting from Cell C2, enter this formula, then push Ctrl + Enter simultaneously. (Alternatively, enter the formula into Cell C2 and drag to the right)


    Can you shorten it? Great if you can, don't hurt your head though :-)

    There is room for improvement though. As is, holidays are not calculated, neither weekends or public holidays. If you come up with a way, (perhaps and another formula or 2 in rows below?), let me know and I will append it to the bottom of this post!

    Sébastien Labonne of The Financial Modeler has come up with a much shorter formula! (About a third the length of mine and far fewer cell references!)


    Well done Sébastien! :-)
  • Posted by andrewe at 19:18

    October 24, 2007

    Dynamic Ranges

    I use dynamic ranges at work quite a lot. There's rarely a workbook I work on that doesn't have them already from a previous developer or from me adding some of my own. As they expand and contract to include data in cells, they are perfect for formulas, lists and charts, etc.

    Assuming we have a list starting in A1, if it is vertical, we can use this frmula. (Go to Insert, Name, Define and enter it at the bottom where it says Refer to: with an appropiate name at the top)


    And if horizontal, we can use this.


    Both formulas are more or less the same, the bottom one just has an extra comma and references the corresponding row (of Cell A1) rather than the column.

    The thing is, when I have several of this formulas to enter, it takes time to enter these names manually. So I wrote some code to do it a little more quickly.

    For vertical ranges
    Sub AddDynamicRangeVertical()
        On Error Resume Next
        Dim sRangeName As String
        Dim n As Name

        If ActiveWorkbook Is Nothing Then Exit Sub

        sRangeName = InputBox("Enter a range name, then push OK. ", _
        "Add Vertical Dynamic Range")

        If sRangeName = "" Then Exit Sub

        sRangeName = Replace(sRangeName, " ", "_")

        ActiveWorkbook.Names.Add Name:=sRangeName, _
        RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
        & ActiveCell.Address & ",,,COUNTA(" & ActiveSheet.Name _
        & "!" & Columns(ActiveCell.Column).Address & "))"

        For Each n In ActiveWorkbook.Names
            If n.Name = sRangeName Then Exit Sub
        Next n

        MsgBox Err.Description, , "Invalid Name"

        On Error GoTo 0
    End Sub

    For horizontal ranges
    Sub AddDynamicRangeHorizontal()
        On Error Resume Next
        Dim sRangeName As String
        Dim n As Name

        If ActiveWorkbook Is Nothing Then Exit Sub

        sRangeName = InputBox("Enter a range name, then push OK. ", _
        "Add Horizontal Dynamic Range")

        If sRangeName = "" Then Exit Sub

        sRangeName = Replace(sRangeName, " ", "_")

        ActiveWorkbook.Names.Add Name:=sRangeName, _
        RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
        & ActiveCell.Address & ",,,,COUNTA(" & ActiveSheet.Name _
        & "!" & Rows(ActiveCell.Row).Address & "))"

        For Each n In ActiveWorkbook.Names
            If n.Name = sRangeName Then Exit Sub
        Next n

        MsgBox Err.Description, , "Invalid Name"

        On Error GoTo 0
    End Sub

    Had to bunch the code a bit to fit my blog :-)

    Well now we have some code, we need a way to run it. I use it often enough that I pasted the code into a module of my personal workbook and use some custom macro buttons from a builtin toolbar. It saves a lot of time.  
    Posted by andrewe at 20:44

    February 21, 2007

    Decimal Time

    The other day I was asked for a formula to convert time into decimals. I'm not quite sure if it is fit the bill or not but this is what I came up with.


    You can see how it turns out.

    Well, okay, I don't work with time that often, it's usually dates that I worry about.....anyway I thought this formula that rounds time to 15 minute increments might be useful, at least the payroll where I used to work was calculated this way.


    Does seem a little better I guess.

    But you will notice that I just show the decimal part (the minutes), not the hours. What is the norm here?

    And, finally, here is a simple formula to show hours as 1 - 12 regardless of AM or PM.


    How about you? What time formulas do you use? Don't forget I've got quite a few date and time formulas here. I'm always happy to add to them and give you credit so don't be shy ;-)  
    Posted by andrewe at 14:48

    February 05, 2006

    Avoiding Weekends

    I live for the weekends but in some jobs you have to avoid them. Say you are in a delivery business, these formulas might be useful...

    To calculate the preceding Friday,


    Change the 5 to a 6 to get preceding Saturdays.

    And to calculate the following Monday,


    Change the 5 to a 6 to avoid Sundays only. (I can't believe I'm saying this)

    Posted by andrewe at 20:47

    September 25, 2005

    Matching More Than One Cell

    There are several functions in Excel for referencing cells to get values. Here is one way to reference values in multiple cells.

    In the picture below, 2005 has been entered in Cell D2. I've entered this formula in cell E2 to get the corresponding value of "K".


    What I want now is all other values in Column C until the next value in Column B (2006) so in cell E3 I enter this formula and drag down to E18.


    What is happening here is that I'm looking for blank cells under 2005 in column B. If these cells are blank and the cell above in Column E is not blank, the true part of the IF formula will show the corresponding cell values in Column C. When 2006 is encountered, the false part the IF formula will cause a blank will show. This results in cells below in Column E to also show as blank as a result of the OFFSET function.

    One problem is that I'm left with zeroes for cells that are beyond Row 18. I could go to Tools, Options, View and uncheck Zero values, but I decided to use a custom format instead (Format, Cells, Number, Custom).


    Note: I did make a User Defined Function using the MergeArea property to count merged rows (adaptable for columns), but as demonstrated above regular Excel functions can be used, and they also work with both merged or unmerged cells.  
    Posted by andrewe at 19:38

    August 22, 2005

    New Calendar Formula

    After some "prompting" on my Japanese blog, I spent a bit of time cleaning up my old calendar formula. Well, it was just meant to be a one-formula version of something I did a long time ago, being a lazy person I had pretty well left it as is...

    Anyway, while I was at it, I wondered if I could make it an array formula like J-Walk's at the top of this post. A couple of things to remember about array formulas,

    1. Select the entire range in which the formula is to be entered, then push Ctrl, Shift and Enter at the same time instead of just Enter. A set of curly brackets will appear at either end automatically, don't try to add them yourself. in the case of a calendar formula you will need 7 columns x 6 rows.

    2. Commas and semi-colons can be used for different ways. For example, here's a picture of commas used in a vertical array.

    Now here's the same array with semicolons. See the difference?

    The opposite is true for horizontal arrays. We can use a combiantion of the two to make calendar array formulas like this one for the current month.


    Which can be shortened to something like this.


    Yes, it's shorter, but it can't be adjusted for different years or months like the one above. (You can adjust the first formula by replacing YEAR(NOW()) and MONTH(NOW()) with numbers as in 2005 for the year and 8 for August)

    Here's another version by the person who did the prompting who goes by the name of Kir San.


    I like this formula because it uses just one IF. (I wanted to use AND or OR, but they don't seem to work in this case so I took the easy way out :-))

    Anyway, true credit goes to the orginator I think. Tinkering with something already invented is okay, but if I had never seen J-Walk's formula, I never would have tried using an array in the first place. Kir San gets my thanks for some rather nifty formulas, I would have been content to wallow in the mire if he didn't give me a push and a shove.

    PS. If you want weeks to start from Mondays, give this a go.


    Happy head hurting!  
    Posted by andrewe at 19:51

    August 01, 2005

    ISO Calendar for Excel

    I use the ISO Calendar at work a lot. It's very useful in a number of fields such as Manufacturing, Logistics, Shipping etc. To get the week number of a date, you can use this handy formula by Evert van den Heuvel. (Date is in Cell A1)


    I was bored yesterday so I decided to take a closer look at how ISO Calendars work. (In between bouts of surfing the web and sleeping)

    This formula will calculate the ISO New Year where the date in Cell A1 is January 1 of that year.


    or you can use this simplified version.


    This formula will calculate how many weeks are in the year (again Cell A1 is Janaury 1 of that year) Most years have 52 weeks, but every now and then there is a leap week which makes 53 weeks in total.


    I also made a ISO Calendar based on my previous formula. Enter a year and the calendar weeks and dates will adjust automatically. (Default year if left blank is the current year) It also includes the day numbers underneath the dates.

    Here's the download link. Hope it's useful ;-)  
    Posted by andrewe at 21:40

    July 03, 2005

    Instant Calendar

    One of the most amazing formulas I've ever seen is this one.

    As posted by John Walkenbach (aka J-Walk) on Dick Kusleika's Daily Dose of Excel, Ugly Formulas...

    Enter as array formula in 6 rows x 7 columns (Push Ctrl, Shift and Enter simultaneously) You may have to format as days by right-clicking the selection, choosing Format Cells, Number, Custom, entering "d" and pushing OK.


    I couldn't believe this when I first saw it. A monthly calendar using just one formula!?

    Well, it so happens I made a perpetual calendar early last year (pre-blogging days). It used LOOKUP to refer to a table in which each month's days where placed. It was a matter of selecting the correct month, then hiding days that exceed the last day of the month.

    Anyway, I had a close look at it last week and thought...yeah, I can make this into just one formula too, so here it is...

    No formatting is required and it can be entered pushing Enter and Ctrl but it must be entered into range A1:G6. (You can get around this by subtracting ROW() and COLUMN() so that they both equal 1 for the top left cell, so for Cell C25 this would mean ROW() becomes ROW()-24 and COLUMN becomes COLUMN()-2)


    or you can use the second half of the formula,


    and then use Conditional Formatting as below (format the font color to match the background)

    Hmm, not as good but not too bad considering it wasn't my intention to make a one formula calendar in the first place. Anyway, the moral of this story is...what you thought was impossible then, might be possible now...anyone can learn some new stuff, so make the most of it ;-)

    P.S. I made a simple perpetual calendar sheet to download here. Don't forget to check out Mark Wielgus's AutomateExcel blog too about the same subject. Seems that everyone's doing it :-)

    Update: Here's a shorter version I wrote after some prompting on my Japanese blog.


    Adjusting it a lot easier too. ;-)  
    Posted by andrewe at 17:36

    May 31, 2005

    Topsy Turvy

    Sometimes I want to reverse a list. Here's one of many ways.

    Upside Down
    Enter this in the first row and drag down. (Add numbers to ROW() as in ROW() + 2 if you want to start in say Row 3)


    (Original list shown in Column D)

    Right to Left
    Enter this in the first column and drag right. (Add numbers to COLUMN as in COLUMN() + 2 if you want to start in say Column C)


    Note both formulas will automatically adjust if your list does not start in the first row (first formula) or column (second formula) accordingly.

    Randomize a List
    Remember this? Here's a way to randomize a list. First enter RAND in a suitable column and drag down.


    Then this formula in another column.


    (Original list shown in Column D)

    Same for columns. Enter RAND in a suitable row, then this formula in another. (Change your references to suit)


    Blank cells in a list will show as zeroes. Get rid of them like this.  
    Posted by andrewe at 22:30

    May 09, 2005

    Ordinal Numbers

    I haven't written a formula for a while so here's a nice "easy" one for ordinal numbers (as in 1st, 2nd, 3rd etc) I'm kind of surprised it works with single digits, maybe it's a "good" bug.


    Has this been done before? Probably. But, you know me, I prefer to try my own way first, then look at what others have done later. That way your chances of learning something new increases even more ;-)  
    Posted by andrewe at 22:13

    March 23, 2005

    Refer by Month

    The other day, there was a question at the JMT Forum about referring to certain sheets by month.

    My solution was this. First, the sheet names are set up with the first three letters of the months and the last two digits of the current year, with the exception of the last sheet which is used to refer to the month sheets in question.

    Then I use this formula (modified to work with a Combo Box, this way you can show various monthly summaries without having to constantly change sheets)

    =INDIRECT(CHOOSE($D$2,"Jan","Feb","Mar","Apr","Jun","Jul","Aug ","Sep","Oct","Nov","Dec")&TEXT(YEAR(TODAY()),"yy")&"!"&CELL("address",B2))

    The cell B2 is used to refer to a corresponding cell in each month name sheet and the cell D2 is being used with a Combo Box like below to select month names shown in cells F3:F5.

    Note the dollar signs in the formula which mean that cell D2 is always referred to, what's called an absolute reference, whereas cell B2 with a relative reference can be dragged or copied into adjoining cells to match their counterparts in the sheet that is being selected.

    You can find more information about absolute and relative referencing under "About cell and range references"in your Help files.  
    Posted by andrewe at 22:04

    February 07, 2005

    Random Numbers 5

    I've been meaning to write about this for a while. The other day a friend here in Japan showed me a neat trick to get unique random numbers.

    1. Enter =RAND() into a range as below.

    2. In the next column, use RANK like this.

    Now, the odds of getting the same number more than once are very remote, but here's a way to make sure. Change the formula like this,

    And then,

    Finished! I thought it was pretty good ;-)  
    Posted by andrewe at 21:16

    November 29, 2004

    Delete Blank Rows 3

    Last night when I tried to post, the blog site was down for maintenance. My apologies to anyone who visited during that time.

    Back in September I posted about deleting Blank Rows. Here is another way to it with array formulas.

    Get the Cell Address
    Enter this formula into same number of cells as your range and push to Ctrl,
    Shift and Enter. (Braces at either end will appear automatically as in the picture below)


    This formula returns the row number for non-blank cells in descending order with the
    SMALL function. The ADDRESS function does the rest. Adjust accordingly to get the right column or use this self -adjusting version.


    Get the Values
    Once you have the addresses, it's not to hard to get the values.


    Or you could just get the values from the start with this array formula.


    This version ajusts for using different rows.


    If you don't like those nasty #N/A errors, you might want to consider using something like this to chase them away. (Conditional Formatting can also be used)  
    Posted by andrewe at 08:38

    November 21, 2004

    Random Numbers 4

    When I was making my Battleships game, I used random numbers to position the ships in different locations each time. As I didn't want to use any special add-ins, this prompted me to think about getting random numbers by just using regular functions.

    Sequential Non-Repeating Random Numbers
    This was quite straightforward. All that is needed is for the first number in a range to be random then add a simple formula to get the sequence.

    For numbers 1 to 10, I enter this formula in let's say cell B2,


    Then in cell B3, I enter this formula and drag down to B11 (a range of 10 cells)


    This will give you a series sequential numbers but from constantly changing starting points (Odds are you may get the same series of numbers more than once, use extra random formulas if you want to stop this happening as much as possible)

    Non-Sequential Non-Repeating Random Numbers
    This was a little harder but I remembered seeing some VBA code once before that gave me the following idea.

    This time in cell B2, I enter 1234567890, then in cell C2 this formula and drag down to cell C11,


    and in cell B3, this formula and drag down to B11,


    There are 2 things happening. First, the MID, RAND and LEN formulas in Column C selects one number from the range of numbers to the left in Column B. And, the SUBSTITUTE formula directly below in Column B deletes this number which in turn decreases the range of numbers to be chosen from.

    Pretty neat. And if you want bigger numbers you can just use the same range C2 to C11 again and again like this formula which will give you numbers from 0 to 999.

    In cell D2,


    and drag down to cell D11.

    Not the simplest but it works.  
    Posted by andrewe at 19:55

    November 10, 2004


    This is the second time I've written about Hyperlinks.

    The first time was in my post, Quick Navigation, Part 1 which was about inserting Hyperlinks from the Insert Hyperlink dialog box.

    It works quite well, but it does have some limitations as the destination address is fixed. As an alternative, you can use the HYPERLINK function to link to an address that is determined by a formula.

    First, let's look at this function which gives us the last cell address in a column, (in this case Column C)


    Note that the number 3 is used to refer to the third column.

    Now here is a HYPERLINK formula to go there automatically.

    =HYPERLINK("#Sheet1!"&ADDRESS(MATCH(9.99999999999999E+307,C:C),3),"Last Row")

    The text "Last Row" can be changed to anything you prefer.

    Speaking of text, the above formulas will only work with numbers, but here is alternative formula that can be used with text as below.

    =HYPERLINK("#Sheet1!"&ADDRESS(MATCH(REPT("z",255),C:C),3),"Last Row")

    Note: The above HYPERLINK formulas should work, but if you get stuck please refer to your Help files for more details.  
    Posted by andrewe at 18:40

    October 30, 2004

    Floating Dates 2

    Last Weekday
    Sometimes floating dates are calculated as the second Sunday in May or perhaps the third Thursday in November. But sometimes they are also calculated as the last weekday such as the final Saturday in October. Here is a formula that can be used in this case, where A1 is the Year, A2 is the Month, and A3 is the Weekday (Weekdays are entered as numbers, Sunday = 1, Monday = 2...Saturday = 7)


    Here is a formula you don't want to specify the year, it always uses the current year.


    Shortened Formula
    Nobody suggested any shortened versions of the formula in my previous post but I had a quick look myself and saw that the first part of the formula is redundant with regards to the MONTH function and changed it to this.


    And here is the version that always use the current year.


    Not much shorter, but getting there... ;-)

    Don't forget you can use these formulas to find the first or last days in a month also.  
    Posted by andrewe at 18:35

    October 27, 2004

    Floating Dates

    Recently I tried making a formula to calculate floating dates. Here goes.

    Cell A1 is the year, Cell A2 is the month and Cell A3 is the day. (Starting from the first Sunday which is Day 1, see the table below)


    To get the day number, consider that the first Sunday of the month is Day 1 and the first Monday is Day 2 etc.

    So if you have a month like this month that has 5 Saturdays, the maximum day number will be 35. (October 31 is actually Day 29)

    Here's an adaption if you don't want to specify the year, just the month and day numbers.


    Just a little long! There is also something else to consider but I'll post about that next time :-)

    If you know any alternative floating date formulas or see a way to shorten mine (it was a rush job done at work!), let me know or just write about it in the Comments section. (There is a VBA one I have seen but not tried yet).  
    Posted by andrewe at 20:52

    October 24, 2004

    Dynamic Ranges 2

    Just a follow up from my previous post...

    I was experimenting to see what happened if the formula was modified to include criteria.

    It didn't work as expected. At least not with entire columns. But it does with entire rows such as,


    For columns, you can remove the top row. This seems to work okay.


    I wonder why it doesn't work with entire columns. I just might ask someone to find out.  
    Posted by andrewe at 19:11

    October 21, 2004

    Dynamic Ranges

    Actually, I'm not really sure whether this should be called a Dynamic Range formula but for the time being please bear with me :-)

    A while ago, I posted showed a LARGE function array in my post Duped by Duplicates. (Array formulas must be entered by pushing Ctrl, Shift and Enter simultaneously instead of just Enter)

    Since then I've been thinking that arrays of this type are rather limited so I've been doing some experimenting.

    Let's look at the below picture. In Column B, I have fifteen numbers, and in Columns C, D, E and F I have various formulas to show them from largest to smallest.

    Column C

    Column D

    Column E

    Column F

    Note that all of these formula work but they have different attributes.

    The formula in Column C is simple and straightforward. The formula in Columns D and E have an advantage in that they are easy to input. Just enter them in the same number of rows as Column B and they will adjust automatically, no counting or calculating rows is necessary.

    Then we have Column F. Of all four formulas, I would say that this formula uses the most memory but is still quite easy to input once you get used to it.

    There's an added advantage. Watch what happens when I drag the range in Column B.

    Only the values shown in Column F have changed. And it shows the true top fifteen values in Column B.

    Now I try to expand the array formulas in Columns C, D, E and F by selecting (not dragging) the same number of rows as Column B, place the cursor anywhere within the formula as shown in the Formula Bar, then re-entering the array by pressing Shift, Ctrl and Enter.

    Here are the results.

    Once again the formula in Column F has adjusted where the others have not, they are limited by the fact that they refer to the first fifteen values of Column B. On the other hand, the formula in Column F refers to all of the values in Column B. So that's what I mean by a Dynamic Range formula.  
    Posted by andrewe at 20:21

    October 14, 2004

    Random Numbers 3

    If I post about random numbers, I really should include the MRAND function that is a part of the Morefunc.xll add-in by Laurent Longre. (I've mentioned this add-in before on Duped by Duplicates

    Entered as an array function (push Ctrl, Shift and Enter instead of just Enter), it will give you unique random numbers as opposed to RAND or RANDBETWEEN which include duplicates. It's syntax is {=MRAND(maximum,start,quantity,volatile or static)}

    Maximum is the highest number

    Start can be from either 1 or 0 (but please look at the third, fourth and fifth examples to see how this can be changed to something like RANDBETWEEN with unique numbers)

    Quantity is how many numbers you want to show between the Maximum and Start numbers. (If not specified, the default will be the same as the Maximum)

    Volatile means the numbers will constantly change when you enter data in other cells or
    re-open the file. Static means that the numbers will remain constant once the formula is entered. The default is Volatile (FALSE or 0), for Static use TRUE or 1.

    Here's a few examples of how it can be used.

    For numbers 1 to 10 in random order. (Replace the 1 with 0 if you want to start from 0). As there are 10 numbers, the formula must be entered in 10 rows.


    For random numbers between 1 and 20 (Note the Maximum criteria has been left out, the Quantity of random numbers now depends on how many rows you enter the formula, e.g. for 3 numbers, enter the number in 3 rows)

    {=MRAND( ,1,20)}

    For random numbers between 10 and 20. If both 10 and 20 are included, the number of rows is 11 (not 10) so you must enter the formula in 11 rows.


    For random numbers starting from 5 that remain constant.




    Note: If you are not sure about using add-ins, please refer to your Help files. The information there should answer any questions you may have.  
    Posted by andrewe at 00:09

    October 11, 2004

    Random Numbers 2

    Last time I mentioned a trick about using random numbers. Let's make that a couple of tricks.

    Finding Formulas
    A Conditional Format to highlight all cells that have formulas - this is one of the best tips I have ever seen and it's not surprising to learn that I saw it on The Spreadsheet Page by John Walkenbach. (Here's the actual link to get a better idea)

    First you need to define the formula using Insert, Name, Define from the top menu. Here is the formula and a picture of the Define Name dialog box.


    And here's a picture of it being used in Conditional Formatting.

    Why does this have to do with random numbers? Well, let's say you want some random numbers but not infinitely changing random numbers, and you intend to save them as values before you close the Workbook. This is a very handy way to tell at a glance that you haven't forgotten any cells that might get overlooked.

    This is a good way to make sure you get a good "spread" of random numbers. (To make sure you don't get too many of some numbers and not enough of others)

    First I set the numbers and enter the FREQUENCY formula as an array (Push Ctrl, Shift and Enter to enter the formula, not just Enter). Note that the FREQUENCY array is entered into the same number of cells as there are values.

    Next, I enter a formula like below (I have a total of 10 numbers so there is going to be more of one value than the others). When I use them at work, I only need to concentrate on the minimum value so an even simpler formula works fine for me.


    Now what? Now, I just keep pushing F9 to re-calculate the sheet (this randomizes the numbers) until I get TRUE, then paste as values by selecting the range of cells, then right clicking to access the Paste Special dialog box.

    I know this is doing things the hard way - if you have a huge range of numbers, the odds are that you might be clicking F9 for a very long time, but then again, I always feel like I've won the jackpot!  
    Posted by andrewe at 00:25

    October 08, 2004

    Random Numbers

    Random Numbers can be quite useful. Here's a few tricks I've picked up using them.

    The RAND function will give you random numbers between 0 and 1 as a decimal. To get whole numbers between 1 and 10, please use this formula, (thanks to Juan Pablo Gonzalez of Mr. Excel.com for showing me this way when I made my Mastermind game)


    You can increase or decrease the maximum allowable number by replacing the 10 for whatever number you prefer. (You can also use TRUNC instead of INT)

    This returns whole numbers between any two numbers that you specify. For examples =RANDBETWEEN(5,10) will give you numbers between (and including) 5 and 10.

    This formula requires that the Analysis Toolpak add-in which will cause problems if it is not installed on any computer that may be used to open a file with this formula.

    The above RAND formula can be modified to do the same thing.


    Think of it like this =INT(RAND()* maximum number - minimum number + 1) + minimum number

    Keep in mind that RAND and RANDBETWEEN are volatile, they'll keep changing indefinitely unless you do something like copy the range and paste as values. I found a neat trick to help work with random numbers that I'll write about next time.  
    Posted by andrewe at 01:45

    October 03, 2004

    Duplicate Invoices 2

    Taking off from where I finished last time, let's look at how I solved the second scenario where we have sequential invoices for each customer. Here's another way that works on the same principle but does not use a helper column and it also makes it easy to follow the next part of my post.

    Here is my conditional format.


    What is happening is that the SUMPRODUCT function is being used in the same way as COUNTIF, but I am concatenating (joining) values such as B2 and C2 within the formula instead of another cell.

    Multiple Criteria
    Consider that a company may not just ship or sell to more than one customer, but also sell different products and send them by different means of transport.

    You might have two or more different products in the same shipment and two or more shipments in one day, but you can never have more than one invoice for the same customer for two or more different shipments.

    This makes things a little more complicated with just one SUMPRODUCT so let's try two of them instead. (I'll also throw in a little something to handle blank cells, otherwise they will be included as duplicates also)

    Here's the formula I used for below.


    Let's have a look to see how it works.

    The first SUMPRODUCT does the same as the above formula - it counts duplicates. The second SUMPRODUCT joins all of the conditions except the parts AB or CD (two or more products is okay in any particular shipment).

    As mentioned above, we can have multiple invoices for the same customer, even if they are on the same day, but not by means of different transport. Neither can we have the same invoice on two different days. This means the number of any existing duplicate invoices should match invoices with exactly the same conditions.

    Okay, I'd also like to point out the absolute and relative references. (Please look at Switch between relative, absolute, and mixed references in your Excel Help files if you are not familiar with these terms)

    Because I used Condtional Formatting for entire rows, I have used what is called Absolute Column and Relative Row references with just the one $ mark in front of the column letter for the values to be counted, (the horizontal postion remains constant but "flows down" vertically into cells below). At the same time I have used Absolute references for the range where the actual counting takes place. I don't want any vertical (or horizontal) movement in this case unless I insert a row, that's why I added an extra row at the bottom in case I want to add a row there too. Note that if a row is inserted between the top and bottom of the range, the range will expand automatically to include this new row.

    I have to admit the hardest part of the problem is understanding what needs to be solved, then thinking of a solution. Lot's of sleep works pretty well in this regard ;-)  
    Posted by andrewe at 21:08

    September 30, 2004

    Duplicate Invoices

    "Duplicate invoices - This seems to be problem for a lot of people and it affects me at times too so I thought it worth a post or two.

    For ease of understanding, I'll use Conditional Formatting to highlight duplicates where they occur. (Data Validation can be used to prevent duplicates from being entered in the first place)

    Sequential Numbers for All Customers
    This is the easiest scenario. Here is a formula that will work shown in the Conditional Formatting dialog box.


    Sequential Numbers for Each Customer
    A little more difficult. I get around this by joining (concatenating) the company names and invoice numbers (=B2&C2) in an adjoining column.


    You can add a number of conditions and just keep concatenating them to make sure you have just one invoice for each "unique" concatenated set of conditions.

    Sound simple? Well, there are times when this won't work so I'll show an alternative way next time.  
    Posted by andrewe at 01:06

    September 27, 2004

    Multiple Lookup 2

    Sometime ago I posted about using SUMPRODUCT or SUM and IF as a multiple lookup. Here's a shot of SUMPRODUCT being used to find a person's age with the criteria of first, middle (initials) and last names.

    These functions work well but not if the lookup values are text.

    An alternative is to use INDEX and MATCH in an array (push Ctrl, Shift and Enter simultaneously when you enter the formula instead of just Enter and matching braces will appear, don't try to add them yourself)

    It also works fine in the case that the lookup values are numbers, (such as age) also.

    Just make sure if there is at least one unique criteria to compensate in the case that 2 or more people have similar names.  
    Posted by andrewe at 22:18

    September 16, 2004

    Rankled by Rank

    RANK is another one of those functions that is duped by duplicates.

    Taking off from where I started last time, I have some numbers in cells B2:B21. In the adjoining column, I have the same numbers without the duplicates using my previous "duplicate ignoring" formula.

    See Duped by Duplicates.

    In the next column, I use this formula to return the rank in D2 and drag down.


    Here's how it looks.

    Well, that's great, but what I really want is all of the original numbers to be ranked, so in the next column I add this formula.


    Which gives me this. All numbers are ranked correctly.

    Working with Criteria
    Just working with the numbers may be fine but usually you will want them to relate to something like a name so you know had last month's highest sales figures or how many points were scored by your favorite sports team.

    So let's modify my LARGE formula to refer to some names chosen at random (and laziness) in cells A2:A21. The name I want to match is "abc" in cell A2.


    It's an array formula so don't forget to push Ctrl, Alt and Enter at the same time when you enter it.

    While I'm at it, I also enter the RANK function as per the below picture. (The LARGE formula has been entered in Column E only to make it look a bit neater)

    Okay, that's it for a few days. I want to cruise the forums for a while and learn some new tricks. I gotta study too ;-)

    Comments? Check here for details.  
    Posted by andrewe at 01:12

    September 13, 2004

    Duped by Duplicates

    I've noticed that certain functions don't return what I call "true ranking" when there are duplicates in a range.

    Here's is an example. In cells B2:B21 I have 20 numbers and I want to sort them from the largest values first. To do this I have used an array formula which is =LARGE( B2:B21,ROW(INDIRECT("1:20"))). Note the braces at either end of the formula in the picture. You don't enter them yourself, they appear automatically when you push Ctrl, Shift and Enter which is how array formulas are entered (as opposed to just using Enter). Select your range first, then enter the formula as mentioned above.

    So far so good. The problem is that any duplicates will cause inaccurate results. For example, I've now entered 200 into cell B7, which causes 200 to appear twice in Column C. Not good, the first and second largest values should definitely not be the same.

    How to get around this? One way would be to use the wonderful Morefunc.xll add-in by Laurent Longre which contains 41 useful functions. For example, I have used the UNIQUEVALUES function here in an array to help solve the duplicates problem.

    Well, this solved the problem very nicely, but unfortunately the same add-in must be installed in every computer where the file is used, so if this is the case, you might find yourself wondering if there is an alternative solution.

    Here's a way that I thought up (probably not the best but it seems to work) First I'll enter a "duplicate ignoring" formula in cell C2 and drag down.


    Here's how it looks.

    Now I enter the same LARGE array formula as before like this.

    Hmm... I don't like that #NUM! error at the bottom so I'll rewrite it like this.


    And here is the picture.

    Well, that seems to do the job. Works for me!

    By the way, you can read more about Mr. Longre and other Excel wizards at my friend Colo's site, Colo's Excel Junk Room, on the Cell Masters page.

    Posted by andrewe at 23:49

    August 25, 2004

    Sum Labels

    You can define ranges and formulas as Names from the Insert Menu. (Here's a link to a previous post)

    You can also use Labels to define a range in a table as shown in this picture. (Besides SUM, you can also use certain other functions such as INDEX)

    Here are some advantages.

    1. After entering a formula into a single cell, you can also drag it into neighboring cells.

    2. When writing the formula, you can use either upper case or lower case.

    3. If you change the Label name, the formula will adjust automatically.

    Note: Be careful that you don't include the totals as part of yet another formula. (You might want to move the totals away from the table to prevent this)

    To make it work, make sure that Accept labels in formulas from Tools, Options, Calculation is checked.

    And now, back to watching the Olympics, see you next time!  
    Posted by andrewe at 23:17

    August 23, 2004

    Show Extra Rows 2

    It's been mentioned on both my English and Japanese blogs that simplifying formulas by using more cells can be a good thing. Apart from being easier to enter and spot or prevent errors, it can also be more flexible if certain criteria need to be changed.

    In my previous post, I used a logical formula to return either TRUE or FALSE in one column, and OFFSET in another columns to select rows. (By "logical", I don't mean the WEEKDAY function, I refer to returning TRUE or FALSE, I'm talking about the formula, not the function name)

    Anyway, my logical formula was straightforward enough but the OFFSET formula to select rows was a bit sloppy, so I thought about it a while and came up with this alternative.

    Instead of TRUE or FALSE, I decided to use 1 or 0. How do you make TRUE into 1 and FALSE into 0? Just add some brackets and 2 minus marks as in =--(WEEKDAY(B5,2)=$C$2).

    Then in the next column, I used a tidier version of OFFSET combined with SUM as in =SUM(OFFSET(C5,-1,0,3,1)).

    So how does it all work?

    I'm using SUM to count the 1's in the range provided by the OFFSET function.


    In the case of =SUM(OFFSET(C5,-1,0,3,1)), C5 is the reference, and you can increase rows on either side by adjusting rows by multiples of 1 and height by multiples of 2.

    And the double minus marks? Putting it simply, the first minus mark changes TRUE or FALSE to their numerical value but as a negative, the second minus mark changes the numerical value back to positive.

    Please let me know if you have any interesting alternatives.  
    Posted by andrewe at 00:56