Dollar Signs in formulas

Let's discuss using dollar signs in formulas.  What are they used for and how do we use them?

There are three different uses of dollars signs in formulas.  In cell B1 we will insert the different "$" formula references to cell A1:

1. =$A$1
2. =$A1
3. =A$1

What do these mean? 

1. =$A$1 - This means that if you copy this formula to any cell (example: D3) in any other column or row, it will still be and always be a reference to A1.
2. =$A1 - This means that the column reference will always be "A", but the row will change based on the row of the cell you're placing the formula in.  So, if you copy it to D3, then the value with show what is in $A3.
3. =A$1 - This means that the row reference will always be "1", but the column will change based on the row of the cell you're placing the formula in.  So, if you copy it to D3, then the value with show what is in D$1.

What do we use these for? Let's take a look:

In column "A" I have placed the values 1,2,3,4 in cells A1, A2, A3, A4.  The values in cells B1 to C4 will be the result of copying that formula to each of those cells.

=$A$1 

    No matter where the formula is copied (B1:C4), the result is the value in "A1"

=$A1

    The copied formula will always show a value from column "A", but the row will be based on the cell the formula is copied to.

For this next example, I changed the data a bit to show how the formula works.  The typed values are in cells A1, B1, C1, D1.  The formula will be copied to cells A2 to D5.

=A$1

    As you can see, the formula maintains the values in Row 1 (Row has the "$"), but changes with the columns (Column "A" does NOT have a "$").

In conclusion, the use of the dollar sign "$" is very useful when you are planning on having to copy the formula across multiple cells.  A properly placed "$" will allow you to ensure formula integrity.  

VBA Tip: Option Explicit

When I first started dabbling in writing my own macros I often saw the words "Option Explicit" before any code that was demonstrated on help sites.  I wondered what it was.

So what does "Option Explicit" mean?  Well, if you plan on using variables within your VBA code and inevitably you will, option explicit requires you to declare each variable.

How do I declare a variable?  The syntax for declaring a variable within VBA is:

Dim [your variable name] as [Type of variable]

An example:

Dim MyVar as Integer

So what do I get out of using Option Explicit you ask? When the code compiles prior to running, you will receive an error message if you have a variable that has not been declared.  This protects you from accidental typos of variables.

Using the example above, if I typed MVyar instead of MyVar and was not using Option Explicit -  as long as the typo is not one of the VBA reserved words, methods or properties - VBA would by default assign MVyar as a new variable.  This can make troubleshooting your code very difficult.

In conclusion, best practice states we should just set Option Explicit as a default setting.  Here's How (Using Excel 2013):

• Open an Excel Workbook > Open VBA (alt + F11)
• Tools > Options > "Editor" Tab
• Check "Require Variable Declaration"  (See picture below)

Question at work....

Okay folks, I found a winner yesterday while at work.  I was asked by a colleague if there was a way to sum the value of multiple items from an array off to the side of a data table. In other words, he wanted to find the values of items a,b,c within a data table that contains multiple occurrences of items a-z and their values.  It looks something like:

So, I Googled "Sum", "Criteria", "Array"

The formula that I eventually came up with was found @ http://www.mrexcel.com/forum/excel-questions/593233-sumif-criteria-array.html

The formula looks like: =SUM(SUMIF(A3:A11,{"a","b"},B3:B11))

The interesting thing about this formula, is that the criteria portion of the SumIf() formula is in {} and listed as an array.  However, it's important to note that so far as I've been able to find, you can't use cell references within that array - it will just give you an error and pop up a message. So, the values you're looking for, must be actually typed out and within quotes.

Something else I noticed as I was toying around with this formula was that if you leave off the outer "Sum() formula, the sumif() formula only returns the sum of the "a" values (=50).  When the Sum() is added around the SumIF() formula, all values within the criteria are added (=63).

Anyway, if anyone has further light to shed on this subject, please do so!  As I've said before we're all learning together.

Named Ranges: Dynamic Range for Chart Data Series

=OFFSET(Calcs!$I$1,1,0,(Counta(Calcs!$I$!:$I$1000)-COUNTIF(Calcs!$I$1:$I$1000,"")))

WHAT IS THIS?!?  Well, let's see...

I use this formula as the value for a named range on a spreadsheet that is nothing but formulas that are pulling data together from a "Raw Data" spreadsheet.

The formula essentially looks within the range I1:I1000 and defines the range that represents all cells with data in them, or are not equal to "" or blank.

Let's break it down by each formula used:

• =Offset(reference, rows, cols, [height], [width]) - Straight from the formula description: "Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference returned can be a single cell or a range of cells." 
• =Counta(value1, value2...) - Used to count the number of cells within a range that contain text/formulas
• =Countif(range, criteria) - a conditional statement that allows you to count the number of cells that meet a single criteria that you've specified.

If we were to read the formula in simplified layman's terms we would say: Starting at cell I1 and staying within the same column, count the number of rows/cells that contain a value that is not blank or "".  Then return the overall range as the final calculated value.

This is why we can use this named range as a data series in a chart.  If the data grows, the formula recognizes that because another cell is no longer blank, and since the chart is using the named range with the formula, the data series reflects that.

One caveat to this formula, if you have a null value in the original data set, the cell value on the spreadsheet with the subset of data cannot = "" or blank.  It will need to read "Null" or "NA" or something of the like - otherwise the above formula stops when it finds the blank and will not reflect well in the chart.

Hope this helps!

VBA Tip for the day: Screen Updating

VBA - Screen Updating.

Have you ever created a macro that bounces between spreadsheets and causes the screen to flash because it's trying to keep up with the macro switching worksheets?  Well, there's a quick fix for that:

Option Explicit
Sub StopFlashing()
Application.ScreenUpdating = False

[Your code Here]

Application.ScreenUpdating = True
End Sub

This will not only stop the screen from flashing and causing your users to have seizures, but it will also help to optimize the macro and allow it to run faster!

Formula for the Day: =Sum(number1, number2 . . .)

"=sum( )"

The sum function in Excel is wonderful!

1. In the formula bar, type "=sum("
2. Select your range (what are you adding?) - Click on a cell or multiple cells, or a whole row/column
3. Close the formula with ")".
4. Press Enter

This will add all of the numerical data within the cell range(s) that you provided.  It's very simple and much faster than having to type =A1+A2+A3...

*You can also add items from multiple spreadsheets! after you select your first cell reference to add, simply type a comma "," to indicate that the next item is a new reference.  Select the spreadsheet tab at the bottom, select the cell with the number you're looking to add and presto! You've added two cells on two different spreadsheets.  - This method is especially handy if you're creating a summary page to provide a quick glance at high level data.

Hello World!

My name is Dan and I'm an Excel addict.  I absolutely love learning how to do new things in Microsoft Excel.  My specialties are in creating templates, dashboards, scorecards, automation of processes, analysis, and writing macros in VBA. Before I continue though, I'd like to share some of my favorite sources I've used over the years for my questions:

www.mrexcel.com
www.ozgrid.com
www.peltiertech.com

My goal for this blog is not to have a forum for users to ask for help, but to simply share some of the experiences and knowledge that I've gained over the years.  I will also add new formulas and how-to's for every level of user.  This is not a problem solving forum, so if you need help with a specific problem, try the sites listed above.

My journey toward Excel greatness began very simply... 10 years ago, I had a job I didn't like!  I was a unit clerk on a hospital unit and I wasn't very good at it and didn't really enjoy it.  For those that may be sympathizing at this point, there are a few choices available when you feel like you're backed into a corner and can't get out:

1. Curl up into a ball, cry, and blame everyone and everything around you.
2. Continue with the current situation
3. Get educated, make yourself better, and get the heck out of there!

Personally, I prefer #3 so I began by using the following process:
1. Finding a question
2. Asking the question
3. See it - Do it - Teach it.

1. Finding a Question:  Well, for my first "Big" Excel question, I wanted to know how to make the cells below my formula have a similar formula but for the cells in the row below (Sound familiar?)... In other words, I wanted to automatically fill ("AutoFill") the cells below my current, active cell with the same formula but have the references change with the row.  Yes, I wanted to use Auto-Fill.  No, I had never used it.

2. Asking the question: I've found the the biggest part of solving a problem is asking the right question. When I started on my quest for knowledge ten years ago, I knew enough to look in the help files - not really helpful, but enough to get me where I needed to go.  Nowadays, I prefer to consult "The Google" and see what results come back.  A tip for a good search is to determine what the key words are.  In the case of my first question, I would use the words Excel, Automatic, fill, and Formula [Give it a try and see what you get!]

3. See it, Do it, Teach it:  This is a method of learning that seems to work very well for me.  Everyone is different, but I find this is a pretty solid way to learn things.  See it: Watch how to do it, or read how to do it. Do it: Try it out! Teach it: Teaching someone else often requires a lot of forethought and detail oriented instruction.  If you can teach someone else to do it, you've got it figure out.

In closing, I'm hoping this blog will help others in their quest for knowledge.  I know that through hard work and dedication we can all become AWESOME in whatever we're trying to do.

Cheers!