Best Microsoft Excel Bloggers

Thursday, March 17, 2011

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


Hide and  Unhide




 Then press Enter.


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

Ms. Excel- Resident Excel Geek