Thursday, March 17, 2011

Unhiding Column A - Happy St. Patty's Day!

Happy St. Patrick's Day!!!!!!!!!!!!!






Hide and  Unhide



Unhiding a column is usually pretty easy. All you do is select the column header to the right and to the left of the hidden column and then right-click and select Unhide.


 In the example below, Column B is hidden so I selected across the Column A header and Column C header (which means that Column B was also included) and then selected Unhide from the shortcut menu.











Unhiding is a little more difficult when you have hidden Column A. To unhide Column A you need to go to the Name Box located above Column A and B and type A1.


 Then press Enter.




This forces Excel to put the cursor into cell A1. At that point, you can right-click and select Unhide. It may look like nothing happened but Excel did unhide it. Excel tends to keep the width so small that you may still not be able to actually see Column A even though it is unhidden. If that is the case, move your cursor carefully until you get the cursor to look like a 4 headed cross which will allow you to widen Column A.


Thursday, March 10, 2011

Variance in a Range

Someone asked me how to find the variance of  the highest number and the lowest number in the same range of data.  The simplest way to do it is =Max(range)-Min(range).
In the simple below, I applied this formula =MAX(A3:A11)-MIN(A3:A11) so that Excel found the highest number in the range which was 90 and then subtracted it from the lowest number it found which was 5. So, we end up with a variance of 85.

Tuesday, March 8, 2011

Round and Rounds She Goes....

Guess how many rounding functions Excel offers............
Did you guess 10? If so, you are correct..there are at least 10.


ROUND(), ROUNDUP() and ROUNDDOWN() all round a number to the specified number of digits, either up or down depending upon the function selected. = Round() rounds up if the digit is equal to 5 or higher and rounds down if less than 5. ROUNDUP() always rounds up and I'm sure you have already guessed what ROUNDDOWN() does.
MROUND() is similar to ROUND() only it rounds to the specified multiple instead of the specified number of digits.

FLOOR() and CEILING() round to the nearest multiple. They are similar to ROUNDUP() and ROUNDDOWN() only instead of rounding to the specified number of digits, they round to the nearest multiple. You generally see FLOOR and CEILING being used in conjunction with currency- to round prices up or down.

EVEN() and ODD() round to the nearest even or odd integer.
INT() rounds down to the next lowest integer.
TRUNC() is similar to ROUNDDOWN () but rather then rounding to a specific number of digits, it simply cuts off the specified number of digits.


Below is a spreadsheet showing the results of these different rounding functions:

Click on the spreadsheet to make it larger and easier to see or to access the spreadsheet itself,  click here