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)



=IF(A2<>MONTH(DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3)),DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3)-7,DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3))

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

=IF(A2<>MONTH(DATE(YEAR(TODAY()),A2+1,0)-(WEEKDAY(DATE(YEAR(TODAY()),A2+1,0))-A3)),DATE(YEAR(TODAY()),A2+1,0)-(WEEKDAY(DATE(YEAR(TODAY()),A2+1,0))-A3)-7,DATE(YEAR(TODAY()),A2+1,0)-(WEEKDAY(DATE(YEAR(TODAY()),A2+1,0))-A3))

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.

=IF(A2<>MONTH(IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))),"",IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3)))

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

=IF(A2<>MONTH(IF(WEEKDAY(DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))<WEEKDAY(DATE(YEAR(TODAY()),A2,1)),DATE(YEAR(TODAY()),A2,1)+(A3-WEEKDAY(DATE(YEAR(TODAY()),A2,1)))+7,DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))),"",IF(WEEKDAY(DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))<WEEKDAY(DATE(YEAR(TODAY()),A2,1)),DATE(YEAR(TODAY()),A2,1)+(A3-WEEKDAY(DATE(YEAR(TODAY()),A2,1)))+7,DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3)))

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)



=IF(MONTH(DATE(A1,A2,1))<>MONTH(IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))),"",IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3)))

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.

=IF(MONTH(DATE(YEAR(TODAY()),A2,1))<>MONTH(IF(WEEKDAY(DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))<WEEKDAY(DATE(YEAR(TODAY()),A2,1)),DATE(YEAR(TODAY()),A2,1)+(A3-WEEKDAY(DATE(YEAR(TODAY()),A2,1)))+7,DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))),"",IF(WEEKDAY(DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3))<WEEKDAY(DATE(YEAR(TODAY()),A2,1)),DATE(YEAR(TODAY()),A2,1)+(A3-WEEKDAY(DATE(YEAR(TODAY()),A2,1)))+7,DATE(YEAR(TODAY()),A2,1)-(WEEKDAY(DATE(YEAR(TODAY()),A2,1))-A3)))

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,

{=LARGE(IF(1:1="criteria",2:2,""),ROW(INDIRECT("1:"&COUNTA(1:1))))}

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

{=LARGE(IF(A2:A65536="criteria",B2:B65536,""),ROW(INDIRECT("1:"&COUNTA(A:A))))}

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
{=LARGE(B2:B16,ROW(INDIRECT("1:15")))}

Column D
{=LARGE(B2:B16,ROW(INDIRECT("1:"&COUNTA(B:B))))}

Column E
{=LARGE(B2:B16,ROW(INDIRECT("1:"&ROWS(B2:B16))))}

Column F
{=LARGE(B:B,ROW(INDIRECT("1:"&COUNTA(B:B))))}

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 17, 2004

Protect Formulas

I've posted a few times about Protection (Please look here, here and here)

Here's another very quick way to (partially) protect formulas.

Go to Data, Validation and select Custom from the Validation Criteria dropdown box on the Settings tab. Then enter the following into the Formula text box.

=""



Simple. If you try to overwrite the formula, an error message will appear like below (you can choose a different style on the Error Alert tab)



So, what's happening here? Well, ="" means "equals nothing", so if you try to enter "something" an error will occur.

However, this is not 100% reliable as it won't prevent deletion by using the Back Space or Delete Keys, and the cell can be removed by deleting the Row or Column also.

At the same time, it will prevent accidental overwriting in most cases, so it's a good protection alternative to remember. It's easy to use and every little bit helps.  
Posted by andrewe at 20:09

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.

{=MRAND(10,1)}

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.

{=MRAND(11,0)+10}

For random numbers starting from 5 that remain constant.

{=MRAND(,0,,TRUE)+5}

or

{=MRAND(,0,,1)+5}

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.

=GET.CELL(48,INDIRECT("rc",FALSE))



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.

FREQUENCY
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.

=IF(AND(OR(D2=3,D2=4),OR(D3=3,D3=4),OR(D4=3,D4=4)),TRUE,FALSE)



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.

RAND
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)

=INT(RAND()*10)+1

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)

RANDBETWEEN
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.

=INT(RAND()*6)+5

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.

=SUMPRODUCT(($B$2:$B$21=B2)*($C$2:$C$21=C2))>1



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.

=AND($E3<>"",SUMPRODUCT(($B$3:$B$24=$B3)*($E$3:$E$24=$E3))<>SUMPRODUCT(($B$3:$B$24=$B3)*($C$3:$C$24=$C3)*($D$3:$D$24=$D3)*($E$3:$E$24=$E3)))



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