Are you looking to give your spreadsheets some visual “pop” and make an impact to capture your audience’s attention? I think you are really going to like this.
Below you will see what your spreadsheet starts out like. The usual formatting, gives the numbers and the information that your audience needs. Bor-ing!
Now here is that same spreadsheet that gives a visual indication of your numbers and what direction your numbers are going in.
Nice, right?!
And this is how it is done my precious sparkle peeps.
Get The Worksheet
Just click the Conditional Formatting Worksheet (30) (or Conditional Formatting Worksheet - Excel 2003 Version (16)) to get the same worksheet to follow along.
Step By Step
- Select cells B3 through M5 by selecting cell B3, now hold down the Shift key and click on cell M5 to select that entire range.
- In the Home tab, Styles group, click on Conditional Formatting. Scroll down to Color Scales and select the color format you like. As you mouse over the selections, you will notice your selection take on the format characteristics for that range.
- Do the same in Steps 1 and 2 for range B9 through M11.
- Now this part is a bit more tricky. You want to select all the cells you want included in order to have an accurate reflection of all the data to be included. The cells you will need to select are: A15 through G15; A17 through G17; A19 through G19; A21 through G21; and A23 through G21. In order to do this you will need to hold down the Ctrl key as you select each of those cells.
- Process Step 2.
- Select cells N3 through N5.
- In the Home tab, Styles group, click on Conditional Formatting. Scroll down to Icon Sets and select the icon set you like. As you mouse over the selections, you will notice your selection take on the format characteristics for that range.
- Select cells N9 through N11.
- Process Step 7.
- Select cells H14 through H22.
- In the Home tab, Styles group, click on Conditional Formatting. Scroll down to Data Bars and select the data bar style you like. As you mouse over the selections, you will notice your selection take on the format characteristics for that range.
- Select cells J14 through J22.
- Process Step 7.
That’s it. You’re done.
There is a way you can enter the same information across multiple worksheets without having to go to each sheet individually and retype the contents.
The first is as follows:
- Press and hold the Ctrl key and click on each worksheet tab (if you are starting with a new, blank worksheet, you will see Sheet1, Sheet2, and Sheet3). This action will result with your title bar displaying the word [Group] after the name of your workbook.
- In your first sheet, click on a cell and enter your data. This data will display in all your selected worksheets in the same cell you entered the data.
- Click on any of the subsequent tab that you selected as part of the group and you will see the identical information. Also, by clicking on a worksheet tab, Excel has automatically ungrouped the worksheets.
This trick will incorporate the former technique described. However, when I have information that rolls across multiple worksheets and I need to make a change, I only want to do it the one time and have the change reflected throughout the workbook. Keep in mind, the arrangement of your worksheets for this information must replicate because the information shows up in the originating cell that you type it in. For example if you type the title in Sheet1, Cell A1, all population will be in Cell A1 across your selected (grouped) worksheets.
1. In your first worksheet, click on a cell and enter your data.
2. Now click on the first of your series of worksheet tabs, press and hold the Ctrl key and click on all the remaining worksheet tabs (to group them) you would like included.
3. Click on the cell that you want this information to be entered in the grouped worksheet tabs. You will want to type in the address of your first worksheet’s cell.
Example: The worksheet title is Sheet1 and you entered the information in Cell A1.
The address formula will be =Sheet1!A1.
4. Click on any of the worksheet tabs that was included in the “group” and you will see the identical information.
Now, whenever you make a change to the first worksheet, the formula on all worksheets you included in the group will mirror that change.
NOTE: This technique only duplicates the information in the cell. It does not apply any formatting that you may have done: color, bold, italics, cell size, etc.
Are there documents that you work with ALL the time? This tip will definitely help you out with current documents that you are working on and would like to quickly grab the next day or later in the week.
This tip only works with the 2007 version of Access, Excel, Powerpoint, and Word.
- To keep a file in your Recent Documents list, click on the Microsoft Office Button
and then click on the little pin 
(as you hover over the pin, it will say “Pin this document to the Recent Documents list”). - When the document is pinned to your Recent Documents list, the pin changes to view from the top of the push pin
.
Short, simple, easy.
We covered Word 2007 menu and toolbar cheat sheet, so it’s only fair to give you the Excel menu and toolbar cheat sheet. Yup, Excel has one too. Very nicely done by the way, both the Excel and Word.
This workbook is comprised of 41 tabs and that doesn’t include the Introduction. Once again, some functions have been removed from Excel 2007.
“So where can I now find the Excel workbook?” you ask. Easy.
Step 1. Click on the Microsoft Office Excel Help icon or press F1. This will execute Excel Help.
Step 2. In the Search box located on the top left-hand side type in “Reference: Locations of Excel 2003 commands in Excel 2007.” A results list will display. Click on the very first result called, “Reference: Locations of Excel 2003 commands in Excel 2007.” This will display interesting information regarding the 2007 interface and where you will find commands and toolbars that you used to find in 2003.
Step 3. Scroll down about mid-way to a heading called, “Menus, toolbars and other familiar elements.” There is a brief paragraph and within that paragraph is a hyperlink called Excel Ribbon mapping workbook. Click on that hyperlink to execute the Excel spreadsheet.
Here is a quick outline of the 41 tabs of menus and toolbars that show where to navigate Excel 2007 to find the function you are looking for:
Standard
Formatting
Pivot Table
Chart Toolbar
Reviewing
Forms
Stop Recording
External Data
Formula Auditing
Full Screen
Circular Reference
Visual Basic
Web
Control Toolbox
Exit Design Mode
Watch Window
Borders
Protection
Text to Speech
List
Compare Side by Side
Drawing
WordArt
Pictures
Drawing Canvas
Organization Chart
Diagram
Ink Drawing and Writing
Ink Annotations
Shadow Setting
3-D Settings
File
Edit
View
Insert
Format
Tools
Data
Chart Menu
Window
Help
Looks like we’ve got our hands full with some pretty interesting stuff we could be doing with Excel. So let’s see what kind of “How Tos” we can put together in Administrative Sparkle. I hope you will join and participate.
Here is a listing of database functions to add to our collection of the formulas reference.
You will find the following information for each function:
- Function name; and
- Function definition.
NOTE: In the description you will find the function syntax and a description of each argument. Arguments that appear in bold text are required; arguments that do not appear in bold text are option.
This list was compiled using several resources to give you the most complete information to put them to use, without over-complicating them.
|
Function |
Description |
|
DAVERAGE |
Averages the values in a column in a list or database that match conditions you specify and takes the form =DAVERAGE(database,field,criteria), where database is the range of cells that make up the list or database and the first row of the list contains labels for each column; field indicates which column is used in the function (by label name or by position); and criteria is the range of cells that contains the conditions you specify. |
|
DCOUNT |
Counts the cells that contain numbers in a column in a list or database that match conditions you specify and takes the form =DCOUNT(database,field,criteria); where database is the range of cells that make up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
DCOUNTA |
Same as DCOUNT, except that it also includes cells containing text, logical values, and error values |
|
DGET |
Extracts a singular value from a column in a list or database that matches conditions you specify and takes the form =DGET(database,field,criteria), where database is the range of cells that make up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
DMAX |
Returns the largest number in a column in a list or database that matches conditions you specify and takes the form =DMAX(database,field,criteria), where database is a range that makes up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
DMIN |
Returns the smallest number in a column in a list or database that matches conditions you specify and takes the same for and arguments as DMAX. |
|
DPRODUCT |
Multiplies the values in a list or database that match conditions you specify and takes the form =DPRODUCT(database,field,criteria), where database is a range that makes up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
DSTDEV |
Estimates the standard deviation of a population based on a sample, using the numbers in a column in a list or database that match conditions you specify, and takes the same form and arguments as DPRODUCT. |
|
DSTDEVP |
Calculates the standard deviation of a population based on the entire population, using the numbers in a column in a list or database that match conditions you specify, and takes the same form and arguments as DPRODUCT. |
|
DSUM |
Adds the numbers in a column in a list or database that match conditions you specify and takes the same for and arguments as DPRODUCT. |
|
DVAR |
Estimates the variance of a population based on a sample, using the numbers in a column in a list or database that match conditions you specify, and takes the form =DVAR(database,field,criteria), where database is the range of cells that make up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
DVARP |
Calculates the variance of a population based on the entire population, using the numbers in a column in a list or database that match conditions you specify, and takes the same form as DVAR. |
<!–[if gte mso 10]>
|
Function |
Description |
|
Averages the values in a column in a list or database that match conditions you specify and takes the form =DAVERAGE(database,field,criteria), where database is the range of cells that make up the list or database and the first row of the list contains labels for each column; field indicates which column is used in the function (by label name or by position); and criteria is the range of cells that contains the conditions you specify. |
|
|
Counts the cells that contain numbers in a column in a list or database that match conditions you specify and takes the form =DCOUNT(database,field,criteria); where database is the range of cells that make up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
|
Same as DCOUNT, except that it also includes cells containing text, logical values, and error values |
|
|
Extracts a singular value from a column in a list or database that matches conditions you specify and takes the form =DGET(database,field,criteria), where database is the range of cells that make up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
|
Returns the largest number in a column in a list or database that matches conditions you specify and takes the form =DMAX(database,field,criteria), where database is a range that makes up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
|
Returns the smallest number in a column in a list or database that matches conditions you specify and takes the same for and arguments as DMAX. |
|
|
Multiplies the values in a list or database that match conditions you specify and takes the form =DPRODUCT(database,field,criteria), where database is a range that makes up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
|
Estimates the standard deviation of a population based on a sample, using the numbers in a column in a list or database that match conditions you specify, and takes the same form and arguments as DPRODUCT. |
|
|
Calculates the standard deviation of a population based on the entire population, using the numbers in a column in a list or database that match conditions you specify, and takes the same form and arguments as DPRODUCT. |
|
|
Adds the numbers in a column in a list or database that match conditions you specify and takes the same for and arguments as DPRODUCT. |
|
|
Estimates the variance of a population based on a sample, using the numbers in a column in a list or database that match conditions you specify, and takes the form =DVAR(database,field,criteria), where database is the range of cells that make up the list or database; field indicates which column is used in the function; and criteria is the range of cells that contains the conditions you specify. |
|
|
Calculates the variance of a population based on the entire population, using the numbers in a column in a list or database that match conditions you specify, and takes the same form as DVAR. |
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=220ef8b3-fb5c-4e30-b7d7-27294ebd7dcc)
Have you ever gone to great lengths of creating a worksheet with all the data and links and then wondered if the layout would have been better suited with the information in the columns having been placed in the rows?
How do you determine what information you want displayed in the columns and what information you want in the rows when first starting out? Many times the spreadsheet starts out pretty simple. You have the basic idea of the information that you want, but are now expanding on the information you want to include. So the spreadsheet becomes much more complex in the information you want to include, view, print, and understand.
The easiest way to design a spreadsheet for viewability when working within the spreadsheet is to have the bulk of your information running down the rows. To build your worksheet shallow and wide would work for a landscape orientation, but you will find yourself navigating away from your titles in the leftmost column (unless you freeze your panes). Nevertheless, you will find navigating your spreadsheet much easier scrolling up and down, rather than left to right. You will also find printing information for page breaks easier when the data runs deep instead of wide.
You don’t have to redo the whole spreadsheet just to see if the spreadsheet works better had the information you wanted listing down the rows had worked better going across in columns. What assistant hasn’t lost her mind because her manager wants the entire worksheet formatted completely opposite? Or, how about the option to present the spreadsheet to your manager with the alternative option. Afterall, it’s difficult to see how the information is best presented until you have the finished product.
Excel 2007 has included a tool that transposes your data without losing the integrity of the spreadsheet links and formulas.
Instructions on How To Transpose Excel Data
- Select all the titles and data that you want included.
- With the information selected, on the Home bar, in the Clipboard group, click on Copy (or Ctrl-C).
- On the worksheet, select the first cell of the destination rows and columns where you want to rearrange the copied data (I would suggest a new worksheet in the tabs).
- On the Home bar, click the arrow below Paste to view the multiple options you can do with Paste, and click on Transpose.
That is all there is to it! You might need to adjust cell width and minor editing to the format, but the bulk of the work is done. Also, you can view a tutorial on the Microsoft website that shows you step-by-step how it is done right here.
If you find this helpful, please share. If you have stories or tips and tricks, leave a comment for others to learn. If you like the information you get here, subscribe. If you would like to make a suggestion on something you would like to see, feel free to drop me an email.
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=2222d056-115d-4295-ae5e-3406ddd67558)
Here you will find a list of functions designed specifically for statistical.
You will find the following information for each function:
1. Function name; and
2. Function definition.
NOTE: In the description you will find the function syntax and a description of each argument. Arguments that appear in bold text are required; arguments that do not appear in bold text are option. All function arguments must be entered in the order show and you must not add any spaces between or within arguments. If you need to enter spaces in a text argument or a reference, the argument must be enclosed in double quotation marks.
This list was compiled using several resources to give you the most complete information to put them to use, without over-complicating them. This is MATH, so you might want to refresh you math terminology to start.
| Function | Description |
| AVEDEV | Returns the average of the absolute deviations of data points from their mean, takes the form =AVEDEV(number1,number2,…), where the numbers can be names, arrays, or references that resolve to numbers, and accepts up to 30 arguments. |
| AVERAGE | Returns the arithmetic mean and the specified numbers and takes the form =AVERAGE(number1,number2,…), where the numbers can be names, arrays, or references that resolve to numbers. Cells containing text, logical values, or empty cells are ignored, but cells containing a zero value are included. |
| AVERAGEA | Same as AVERAGE, except that text and logical values are included in the calculation. |
| AVERAGEIF | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria and takes the form =AVERAGEIF(range,criteria,average_range). |
| AVERAGEIFS | Returns the average (arithmetic mean) of all cells that meet multiple criteria and takes the form =AVERAGEIFS(average_range,criteria_range,criteria,…). |
| BETADIST | Returns the beta cumulative beta probability density function and takes the form =BETADIST(x,alpha,beta,A,B), where x is the value between A and B at which to evaluate the function; alpha is a parameter to the distribution; beta is a parameter to the distribution; A is an optional lower bound to the interval of x; and B is an optional upper bound to the interval of x. |
| BETAINV | Returns the inverse of the cumulative beta probability density function and takes the form =BETAINV(probability,alpha,beta,A,B), where probability is a probability associated with the beta distribution. |
| BINOMDIST | Returns the individual term binomial distribution probability and takes the form =BINOMDIST(number_s,trials,probability_s,cumulative), where number_s is the number of successes in trials; trials is the number of independent trials; probability_s is the probability of success on each trial; and cumulative is a logical value that determines the form of the function. If TRUE, it returns the probability that there are at most number_s successes; if FALSE, it returns the probability that there are number_s successes. |
| CHIDIST | Returns the one-tailed probability of the chi-squared distribution (used to compare observed vs. expected values) and takes the form =CHIDIST(x,degrees_freedom), where x is the value at which you want to evaluate the distribution and degrees_freedom is the number of degrees of freedom. |
| CHIINV | Returns the inverse of the CHIDIST (one-tailed probability of the chi-squared distribution) and takes the form =CHINV(probability,degrees_freedom), where probability is a probability associated with the chi-squared distribution and degrees_freedom is the number of degrees of freedom. |
| CHITEST | Returns the test for independence and takes the form =CHITEST(actual_range,expected_range), where actual_range is the range of data that contains observations to test against expected values and expected_range is the range of data that contains the ratio of the product of row totals and column totals to the grand total. |
| CONFIDENCE | Returns the confidence interval for a population mean and takes the form =CONFIDENCE(alpha,standard_dev,size), where alpha is the significance level used to compute the confidence level (an alpha of 0.1 indicates a 90 percent confidence level); standard_dev is the population standard deviation for the data range and is assumed to be known; and size is the sample size. |
| CORREL | Returns the correlation coefficient array1 and array2 cell ranges and takes the form =CORREL(array1,array2), where arrays are ranges of cells containing values. |
| COUNT | Tells you how many cells in a given range contain numbers, including dates and formulas that evaluate to numbers, takes the form =COUNT(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values. |
| COUNTA | Same as COUNT, except that text and logical values are included in the calculation. |
| COUNTBLANK | Counts the number of empty cells in a specified range and takes the form =COUNTBLANK(range). |
| COUNTIF | Counts the number of cells within a range that match specified criteria and takes the form =COUNTIF(range,criteria), where range is the range you want to test and criteria is the logical test to be performed on each cell. |
| COUNTIFS | Counts the number of cells within a range that meet multiple criteria and takes the form =COUNTIF(criteria_ange,criteria,…). |
| COVAR | Returns covariance, the average of the products of paired deviations for each data point pair, and takes the form =COVAR(array1,array2), where arrays are cell ranges containing integers. |
| CRITBINOM | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
| DEVSQ | Returns the sum of squares of deviations of data points from their sample mean, takes the form =DEVSQ(number1,number2,…), where numbers can be names, arrays, or references that resolve to numbers, and accepts up to 30 arguments. |
| EXPONDIST | Returns the exponential distribution and takes the form =EXPONDIST(x,lambda,cumulative), where x is the value of the function; lambda is the parameter value; and cumulative is a logical value that indicates which form of the exponential function to provide (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function). |
| FDIST | Returns the F probability distribution and takes the form =FDIST(x,degrees_freedom1,degrees_freedom2), where x is the value at which to evaluate the function, degrees_freedom1 is the numerator degrees of freedom, and degrees_freedom2 is the denominator. |
| FINV | Returns the inverse of the F probability distribution a takes the form =FINV(probability,degrees_freedom1,degrees_freedom2), where probability is a probability associated with the F cumulative distribution; degrees_freedom1 is the numerator degrees of freedom; and degrees_freedom2 is the denominator degrees of freedom. |
| FISHER | Returns the Fisher transformation at x and takes the form =FISHER(x), where x is a value between -1 and 1 (not inclusive). |
| FISHERINV | Returns the inverse of the Fisher transformation =FISHERINV(y), where y is any numeric value. |
| FORECAST | Returns a single point along a trend line and takes the form =FORECAST(x,known_y’s,known_x’s). |
| FREQUENCY | Returns the number of times that values occur within a population and takes the form =FREQUENCY(data_array,bins_array). |
| FTEST | Returns the result of an F-test, the one-tailed probability that the variances in array1 and array2 are not significantly different, and takes the form =FTEST(array1,array2). |
| GAMMADIST | Returns the gamma distribution and takes the form =GAMMADIST(x,alpha,beta,cumulative), where x is the value at which you want to evaluate the distribution; alpha is a parameter to the distribution; beta is a parameter to the distribution; and cumulative is a logic al value that determines the form of the function (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function). |
| GAMMAINV | Returns the inverse of the gamma cumulative distribution and takes the form =GAMMAINV(probability,alpha,beta), where probability is the probability associated with the gamma distribution; alpha is a parameter to the distribution; and beta is a parameter to the distribution. |
| GAMMALN | Returns the natural logarithm of the gamma function and takes the form =GAMMALN(x), where x is a positive value. |
| GEOMEAN | Returns the geometric mean of any array or range of positive data and takes the form =GEOMEAN(number1,number2,…), where numbers are 1 to 30 positive integer values. |
| GROWTH | Returns values of points that tie along an exponential trend line and takes the form =GROWTH(known_ys,known_x’s,new_x’s,const). |
| HARMEAN | Returns the harmonic mean of a data set and takes the form =HARMEAN(number1,number2,…), where numbers are 1 to 30 positive values. |
| HYPGEOMDIST | Returns the hypergeometric distribution (the probability of a given number of sample successes, given the size of the sample and population, and the number of population successes) and takes the form =HYPGEOMDIST(sample_s,number_sample,population_s,number_population), where sample_s is the number of successes in the sample; number_sample is the size of the sample; population_s is the number of successes in the population; and number_population is the population size. |
| INTERCEPT | Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values and takes the form =INTERCEPT(known_y’s,known_x’s), where known_y’s is the dependent set of observations or data and known_x’s is the independent set of observations or data. |
| KURT | Returns the kurtosis of a data set (characterizes the relative “peaked-ness” or flatness of a distribution compared with the normal distribution), takes the form =KURT(number1,number2,…), and accepts up to 30 numeric arguments. |
| LARGE | Returns the kth largest value in an input range and takes the form =LARGE(array,k), where k is the position from the largest value in array you want to find. |
| LINEST | Calculates the statistics for a line using the least squares method to arrive at a slope that best describes the given data and takes the form =LINEST(known_y’s,known_x’s,const,stats). |
| LOGEST | Returns statistics describing known data in terms of an exponential curve and takes the form =LOGEST(known_y’s,known_x’s,const,stats). |
| LOGINV | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev, and takes the form =LOGINV(probability,mean,standard_dev), where probability is a probability associated with the lognormal distribution; mean is the mean of ln(x); and standard_dev is the standard deviation of ln(x). |
| LOGNORMDIST | Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev, and takes the form =LOGNORMDIST(x,mean,standard_dev), where x is the value at which to evaluate the function; mean is the mean of ln(x); and standard_dev is the standard deviation of ln(x). |
| MAX | Returns the maximum largest value in a range, takes the form =MAX(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values. |
| MAXA | Same as MAX, except that text and logical values are included in the calculation. |
| MEDIAN | Computes the median of a set of numbers, takes the form =MEDIAN(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values. |
| MIN | Returns the smallest value in a range, takes the form =MIN(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values. |
| MINA | Same as MIN, except that text and logical values are included in the calculation. |
| MODE | Determines which value occurs most frequently in a set of numbers, takes the form =MODE(number1, number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values. |
| NEGBINOMDIST | Returns the negative binomial distribution (the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s) and takes the form =NEGBINOMDIST(number_f,number_s,probability_s), where number_f is the number of failures; number_s is the threshold number of successes; and probability_s is the probability of a success. |
| NORMDIST | Returns the normal cumulative distribution for the specified mean and standard deviation and takes the form =NORMDIST(x,mean,standard_dev,cumulative), where x is the value for which you want the distribution; mean is the arithmetic mean of the distribution; standard_dev is the standard deviation of the distribution; and cumulative is a logical value that determines the form of the function (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability mass function). |
| NORMINV | Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation and takes the form =NORMINV(probability,mean,standard_dev), where probability is a probability corresponding to the normal distribution; mean is the arithmetic mean of the distribution; and standard_dev is the standard deviation of the distribution. |
| NORMSDIST | Returns the standard normal cumulative distribution function and takes the form =NORMSDIST(z). |
| NORMSINV | Returns the inverse of the standard normal cumulative distribution (with a mean of zero and a standard deviation of one) and takes the form =NORMSINV(probability), where probability is a probability corresponding to the normal distribution. |
| PEARSON | Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1 to 1 (inclusive) and reflects the extent of a linear relationship between two data sets, and takes the form =PEARSON(array1,array2) where array1 is a set of independent values and array2 is a set of dependent values. |
| PERCENTILE | Returns the member of an input range that is at a specified percentile ranking and takes the form =PERCENTILE(array,k), where array is the input range and k is the rank you want to find. |
| PERCENTRANK | Returns a percential ranking for any member of a data set and takes the form =PERCENTRANK(array,x,significance), where array specifies the input range; x specifies the value whose rank you want to obtain; and the optional significance indicates the number of digits of precision you want. If significance is omitted, results are rounded to three digits (0.xxx or xx.x%). |
| PERMUT | Returns the number of permutations for a given number of objects that can be selected from number objects and takes the form =PERMUT(number,number_chosen), where number is an integer that describes the number of objects and number_chosen is an integer that describes the number of objects in each permutation. |
| POISSON | Returns the Poisson distribution and takes the form =POISSON(x,mean,cumulative), where x is the number of events; mean is the expected numeric value; and cumulative is a logical value that determines the form of the probability distribution returned (if TRUE, returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if FALSE, returns the Poisson probability mass function that the number of events occurring will be exactly x). |
| PROB | Returns the probability that values in a range are between two limits and takes the form =PROB(x_range,prob_range,lower_limit,upper_limit), where x_range of numberic values of x with which there are associated probabilities; prob_range is a set of probabilities associated with values in x_range; lower_limit is the lower bound on the value for which you want a probability; and upper_limit is the optional upper bound on the value for which you want a probability. |
| QUARTILE | Returns the value in an input range that represents a specified quarter-percentile and takes the for =QUARTILE(array,quart). |
| RANK | Returns the ranked position of a particular number within a set of numbers and takes the form =RANK(number,ref,order). |
| RSQ | Returns the square of the Pearson product moment correlation coefficient through data points in the arrays known_y’s and known_x’s and takes the form =RSQ(known_y’s,known_x’s). |
| SKEW | Returns the skewness of a distribution (the degree of asymmetry of a distribution around its mean), takes the form =SKEW(number1,number2,…), and accepts up to 30 arguments. |
| SLOPE | Returns the slope of the linear regression line and takes the form =SLOPE(known_y’s,known_x’s). |
| SMALL | Returns the k-th smallest value in a data set and takes the form =SMALL(array,k), where k is the position from the smallest value in array you want to find. |
| STANDARDIZE | Returns a normalized value from a distribution characterized by mean and standard_dev and takes the form =STANDARDIZE(x,mean,standard_dev), where x is the value you want to normalize; mean is the arithmetic mean of the distribution; and standard_dev is the standard deviation of the distribution. |
| STDEV | Estimates standard deviation, assuming that the arguments represent only a sample of the total population, and takes the form =STDEV(number1,number2,…), accepting up to 30 arguments. |
| STDEVA | Same as STDEV, except that text and logical values are included in the calculation. |
| STDEVP | Calculates standard deviation, assuming that the arguments represent the total population, and takes the form =STDEVP(number1,number2,…). |
| STDEVPA | Same as STDEVP, except that text and logical values are included in the calculation. |
| STEYX | Calculates the standard error of a regression and takes the form _STEYX(known_y’s,known_x’s). |
| TDIST | Returns the percentage points (probability) for the the student’s t-distribution, where a numberic value (x) is calculated value of t for which the percentage points are to be computed, and takes the form =TDIST(x,degrees_freedom,tails), where x is the numeric value at which to evaluate the distribution; degrees_freedom is an integer indicating the number of degrees of freedom; and tails specifies the number of distribution tails to return (if 1, returns the one-tailed distribution; if 2, returns the two-tailed distribution). |
| TINV | Returns the inverse of the Student’s t-distribution as a function of the probability and the degrees of freedom, and takes the form =TINV(probability,degrees_freedom), where probability is the probability associated with the two-tailed Student’s t-distribution and degrees_freedom is the number of degrees of freedom to characterize the distribution. |
| TREND | Returns values of points that lie along a linear trend line and takes the form =TREND(known_y’s,known_x’s,new_x’s,const). |
| TRIMMEAN | Returns the mean of the interior of a data set (the mean taken by excluding a percentage of data points from the top and bottom tails of a data set), and takes the form =TRIMMEAN(array,percent), where array is the array or range of values to trim and average and percent is the fractional number of data points to exclude from the calculation. |
| TTEST | Returns the probability associated with a Student’s t-test and takes the form =TTEST(array1,array2,tails,type), where array1 is the first data set; array2 is the second data set; tails specifies the number of distribution tails (if 1, uses the one-tailed distribution; if 2, uses the two-tailed distribution); and type is the kind of t-test to perform (1 = paired; 2 = two-sample equal variance; 3 = two=sample unequal variance). |
| VAR | Computes variance, assuming that the arguments represent only a sample of the total population, and takes the form =VAR(number1,number2,…), accepting up to 30 arguments. |
| VARA | Same as VAR, except that text and logical values are included in the calculation. |
| VARP | Computes variance, assuming that the arguments represent the total population, and takes the form =VARP(number1,number2,…). |
| VARPA | Same as VARP, except that text and logical values are included in the calculation. |
| WEIBULL | Returns the Weibull distribution and takes the form =WEIBULL(x,alpha,beta,cumulative), where x is the value at which to evaluate the function; alpha is a parameter to the distribution; beta is a parameter to the distribution; and cumulative determines the form of the function. |
| ZTEST | Returns the two-tailed P-value of a z-test (generates a standard score for x with respect to the data set, array, and returns the two-tailed probability for the normal distribution), and takes the form =ZTEST(array,x,sigma), where array is the array or range of data against which to test x; x is the value to test; and sigma is the known population’s standard deviation. |
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=03aad441-8c8f-4bc3-a9a7-fc3eb372b8f3)
The most popular function in Excel is the Sum function, but did you know that Excel can do so much more than that? So be looking for the reference posts to various categories of the functions (formulas) feature in Excel.
Here you will find a list of functions designed specifically for math and trigonometry. How super fun is THAT!?
You will find the following information for each function:
1. Function name; and
2. Function definition.
NOTE: In the description you will find the function syntax and a description of each argument. Arguments that appear in bold text are required; arguments that do not appear in bold text are option. All function arguments must be entered in the order show and you must not add any spaces between or within arguments. If you need to enter spaces in a text argument or a reference, the argument must be enclosed in double quotation marks.
This list was compiled using several resources to give you the most complete information to put them to use, without over-complicating them. This is MATH, so you might want to refresh you math terminology to start.
| Function | Description |
| ABS | Returns the absolute value of a number and takes the form =ABS(number). If a number is negative, this function simply removes the sign, making it a positive number. |
| ACOS | Returns the arccosine of a number in radians and takes the form =ACOS(number), in which number is the cosine of an angle. |
| ACOSH | Returns the inverse hyperbolic cosine of a number and takes the form =ACOSH(number), in which number must be >=1. |
| ASIN | Returns the arcsine of a number in radians and takes the form =ASIN(number), where number is the sine of the angle you want and must be from -1 to 1. |
| ASINH | Returns the inverse hyperbolic sine of a number and takes the form =ASINH (number). |
| ATAN | Returns the arctangent of a number and takes the form =ATAN(number), where number is the tangent of an angle. |
| ATAN2 | Returns the arctangent from x- and y-coordinates, in radians, and takes the form =ATAN2(x_num,y_num), where x_num is the x-coordinate of the point and y_num is the y-coordinate of the point. A positive result represents a counterclockwise angle from the x-axis; a negative result represents a clockwise angle. |
| ATANH | Returns the inverse hyperbolic tangent of a number and takes the form =ATANH(number), where number must be between (not including) -1 and 1. |
| CEILING | Rounds a number to the nearest integer or to the nearest multiple of significance and takes the form =CEILING(number,multiple) where number and multiple must be numeric and have the same sign. If they have different signs, Excel returns the #NUM! error value. |
| COMBIN | Determines the number of possible group comginations that can be derived from a pool of items and takes the form =COMBIN(number number_chosen), where number is the total items in the pool and number_chosen is the number of items you want in each group. |
| COS | Returns the cosine of an angle. The complement of the SIN function. It takes the form =COS(number), where number is the angle in radians. |
| COSH | Returns the hyperbolic cosine of a number and takes the form =COSH(number), where number is any real number. |
| DEGREES | Converts radians to degrees and takes the form =DEGREES(angle), where angle represents an angle measured in radians. |
| EVEN | Rounds a number up to the nearest even integer and takes the form =EVEN(number). Negative numbers are correspondingly rounded down. |
| EXP | Computes the value of the constant e (approximately 2.71828183) raised to the power specified by its argument and take the form =EXP(number). The EXP function is the inverse of the LN function. |
| FACT | Returns the factorial of a number and takes the form =FACT(number), where number is a positive integer. |
| FACTDOUBLE | Returns the double factorial of a number and takes the form =FACT(number), where number is a positive integer. |
| FLOOR | Rounds a number down to the nearest given multiple and takes the form =FLOOR(number,multiple), where number and multiple must be numeric and have the same sign. If they have different signs, Excel returns the #NUM! error value. |
| GCD | Returns the greatest common divisor of two or more integers (the largest integer that divides both number1 and number2 without a remainder) and takes the form =GCD(number1,number2,…), where numbers are 1 to 30 positive integer values. |
| INT | Rounds numbers down to the nearest integer and takes the form =INT(number). When number is negative, INT also rounds that number down to the nearest integer. |
| LCM | Returns the least common multiple of integers (the smallest positive integer that is a multiple of all arguments), takes the form =LCM(number1,number2,…), and accepts up to 29 numeric integer arguments. |
| LN | Returns the natural (base e) logarithm of the positive number referred to by its argument and takes the form =LN(number). LN is the inverse of the EXP function. |
| LOG | Returns the logarithm of a positive number using a specified base and takes the form =LOG(number,base). If you don’t include the base argument, Excel assumes the base is 10. |
| LOG10 | Returns the base-10 logarithm of a number and takes the form =LOG10(number), where number is a positive real number. |
| MDETERM | Returns the matrix determinant of an array and takes the form =MDTERM(array), where array is a numeric array with an equal number of rows and columns. |
| MINVERSE | Returns the inverse matrix for the matrix stored in an array and takes the form =MINVERSE(array), where array is a numeric array with an equal number of rows and columns. |
| MMULT | Returns the matrix product of two arrays (resulting in an array with the same number of rows as array1 and the same number of columns as array2) and takes the form =MMULT(array1,array2). |
| MOD | Returns the remainder from division operation (modulus) and takes the form =MOD(number,divisor). If number is smaller than divisor, the result of the function equals number. If number is exactly divisible by divisor, the function returns 0. If divisor is 0, MOD returns the #DIV/0! Error value. |
| MROUND | Rounds any number to a multiple you specify and takes the form =MROUND(number,multiple), where number and multiple must both have the same sign. The function rounds up if the remainder after dividing number by multiple is at least half the value of multiple. |
| MULTINOMIAL | Returns the ratio of the factorial of a sum of values to the product of factorials and takes the form =MULTINOMIAL(num1, num2, …), where nums are up to 29 values for which you want to find the multinomial. |
| ODD | Rounds a number up to the nearest odd integer and takes the form =ODD(number). Negative numbers are correspondingly rounded down. |
| PI | Returns the value of pi, accurate to 14 decimal places (3.14159265358979), and takes the form –PI(). It takes no arguments, but you must still enter empty parentheses after the function name. To calculate the area of a circle, multiply the square of the circle’s radius by the PI function. |
| POWER | Returns the result of a number raised to a power and takes the form =POWER(number,power), where number is the base number and power is the exponent to which the base number is raised. |
| PRODUCT | Multiplies all the numbers referenced by its arguments, takes the form =PRODUCT(number1,number2,…), and can take as many as 30 arguments. Text, logical values, and blank cells are ignored. |
| QUOTIENT | Returns the integer portion of a division and takes the form =QUOTIENT(numerator,denominator), where numerator is the dividend and denominator is the divisor. |
| RADIANS | Converts degrees to radians and takes the form =RADIANS(angle), where angle represents an angle measured in degrees. |
| RAND | Generates a random number between 0 and 1 and takes the form =RAND() with no arguments, but you must still enter empty parentheses after the function name. The result changes with each sheet recalculation. |
| RANDBETWEEN | Generates random integer values between a specified range of numbers and takes the form =RANDBETWEEN(bottom,top), where bottom is the smallest and top is the largest integer you want to use, inclusive. |
| ROMAN | Converts an Arabic numeral to Roman, as text, and takes the form =ROMAN(number,form), where number is the Arabic numeral you want converted and form is a number specifying the type of Roman numeral you want (1, 2, or 3 = more concise notation; 4 or FALSE = simplified notation; TRUE = classic notation). |
| ROUND | Rounds a number to a specified number of decimal places and takes the form =ROUND(number,num_digits), where number can be a number, a reference to a cell that contains a number, or a formula that results in a number. num_digits can be any positive or negative integer and determines the number of decimal places. Enter a negative num_digits to round to the left of the decimal; enter zero to round to the nearest integer. |
| ROUNDDOWN | Rounds a number down, toward zero, to a specified number of decimal places and takes the same form and arguments as ROUND. |
| ROUNDUP | Rounds a number up, away from zero, to a specified number of decimal places and takes the same form and arguments as ROUND. |
| SERIESSUM | Returns the sum of a power series based on the formula and takes the form =SERIESSUM(x,n,m,coefficients), where x is the input value to the power series; n is the initial power to which you want to raise x; m is the step by which to increase n for each term in the series; and coefficients is a set of coefficients by which each successive power of x is multiplied. The number of values in coefficients determines the number of terms in the power series. |
| SIGN | Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative, and takes the form =SIGN(number), where number is any real number. |
| SIN | Returns the sine of an angle. The complement of the COS function, it takes the form =SIN(number), where number is the angle in radians. |
| SINH | Returns the hyperbolic sine of a number and takes the form =SINH(number), where number is any real number. |
| SQRT | Returns a positive square root of a number and takes the form =SQRT(number). |
| SQRTPI | Returns the square root of (number * pi) and takes the form =SQRTP(number). |
| SUBTOTAL | Returns a subtotal in a list or database and takes the form =SUBTOTAL(function_num,ref1,ref2,…), where function_num is a number that specifies which function to use in calculating subtotals (1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV, 8=STDEVP, 9=SUM, 10=VAR, 11=VARP), and refs are 1 to 29 ranges or references for which you want the subtotal. |
| SUM | Totals a series of numbers and takes the form =SUM(num1,num2,…), where nums (max 30) can be numbers, formulas, ranges, or cell references. Ignores arguments that refer to text values, logical values, or blank cells |
| SUMIF | Tests each cell in a range before adding it to the total and takes the form =SUMIF(range,criteria,sum_range), where range is the range you want to test; criteria is the logical test to be performed on each cell; and sum_range specifies the cells to be totaled. |
| SUMIFS | Tests each cell in a range that meet multiple criteria before adding it to the total and takes the form =SUMIFS(sum_range,criteria_range,criteria,…), where criteria_range is the range you want to test; criteria is the logical test to be performed on each cell; and sum_range specifies the cells to be totaled. |
| SUMPRODUCT | Multiples the value in each cell in a specified range by the corresponding cell in another equal-sized range and then adds the results. It takes the form =SUMPRODUCT(array1,array2,array3,…) and can include up to 30 arrays. Nonnumeric entries are treated as zero. |
| SUMSQ | Returns the sum of the squares of each specified value in a specified range, takes the form =SUMQ(number1,number2,…), and takes up to 30 arguments, or a single array or array reference. |
| SUMX2MY2 | Calculates the sum of the difference of the squares of the corresponding values in x and y and takes the form =SUMX2MY2(array_x,array_y), where x and y are arrays that contain the same number of elements. |
| SUMX2PY2 | Calculates the sum of the squares of the corresponding values in x and y and takes the form =SUMX2PY2(array_x,array_y), where x and y are arrays that contain the same number of elements. |
| SUMXMY2 | Calculates the sum of the squares of the differences of the corresponding values in x and y and takes the form =SUMX2MY2(array_x,array_y), where x and y are arrays that contain the same number of elements. |
| TAN | Returns the tangent of an angle and takes the form =TAN(number), where number is the angle in radians. |
| TANH | Returns the hyperbolic tangent of a number and takes the form =TAN(number), where number is any real number. |
| TRUNC | Truncates everything to the right of the decimal point, regardless of its sign, and takes the form =TRUNC(number,num_digits). Truncates everything after the specified num_digits to the right of the decimal point. |
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=6025dd17-ae55-49b2-8dd9-0c676a8673a0)
There is a lot to be done when you want to get from where you are right now to where you want to be. There are different programs you can use, I like to call “tools,” that can keep you on track. I don’t like to say that they necessarily simplify life, but it does keep your projects organized and you on track. And, that is what we are trying to achieve here.
I have been crazy-busy the past couple of weeks and even took a couple of weeks off from the gym. Suffered the consequences for that one, but I am pleased to say I am back on track. The fallout, and I am being extreme, is that I feel like I gained one pound for every day I took off. That is good enough reason or incentive to get back with conviction to the goal.
At the Office
I track my executive’s projects with an Excel spreadsheet. It’s a tool, it works, but it really is basic. Mostly it is about maintenance, otherwise it just doesn’t work. Like anything else. I cannot stress the importance of assigning Task Submission Dates. This is the key column with which you sort to view what goals are getting close to coming due and what are overdue. When they are overdue, write notes of what you plan to do in the Comments column and then update the Task Submission Date to reflect when you will have it done.
The spreadsheet that I use for my executive provides the following columns of information:
- Date Assigned: This tells the executive when he/she initially assigned it and is able to get an idea of how long the project has been around.
- Title of Assignment: You will want to give it a relevant and informative title.
- Primary Manager: The manager assigned responsibility for the project.
- Assigned To: The manager usually has someone on his/her team or staff that is doing work on the project.
- Submission Date: This is the date my executive wants to receive the task or details of the project in her office.
- Comments: The executive’s directives and comments by the managers detailing what is happening with the project. It provides a little bit of the history, what is holding it up, or what still needs to be done.
- Assignment Tracking Number: This is a specific tracking number that identifies the assignment. This is also tracked in my Outlook “Tasks,” along with any email and written documentation pertaining to the project.
An Auto Filter is applied to the column titles, which its use will be explained. Information is sorted and presented to the executive in different ways, depending on the use.
- A running account of all active projects. This is Sorted by Submission Date, so the most pressing and urgent are seen and attended to first.
- Individual manager’s meetings, a spreadsheet that only reflects that manager’s active projects. This is filtered by Primary Manager and then Sorted by Submission Date. This way the executive does not have to visually sort through all projects that is on the spreadsheet that do not apply to that specific manager. In my executive’s case, we are running close to 75 projects. As an added perk, the managers are able view and print projects that only relate to them.
NOTE: I tried finding a project spreadsheet on Microsoft’s Template website to no avail. Maybe I should submit mine. However, I am considering doing a little tweaking on it to make it even more informative. Not by adding more information, but visually and trying the Groups feature to see how that works in comparison to the Filter. I will let you know.
At Home
I use MindJet MindManager. This program allows me to work in one program and have the various information populate into my Outlook Notes, Calendar, Timeline, Contacts, and Tasks. I am also able to attach various documentation from all my Microsoft programs, including Word, Excel, PowerPoint, Project, Visio and execute from MindJet MindManager. I can make it available for others to have access to it outside my network. The best feature, is the ability to see it as a MindMap. There is a map view and an outline view, allowing me both the visual aspect to see how some things are interrelated and viewing it in running points.
There is a lot more to this program that makes it hyper-intensive in the amount of information that it can hold, including the Internet and as its own Project program assigning deadlines. The program will automatically flag items that show Past Due and At Risk.
MindJet MindManager has helped me mange finding an apartment. I had a certain criteria and I was able to define what needed to be done to achieve my desired goal. Some features keep me on track. For example, after researching several apartments that met my criteria and selecting the one I wanted to move into, I had forms that needed to be filled out and submitted, processing fees and dates that they need them. I was able to follow-up with the apartment management team to ask what I need to do, what I need to provide them, and when it is due.
Right now in that task I will have a populating calendar item to pay the deposit on April 1 and the rent to be paid upon moving in on the 7th of May. I also have a calendar item for the move in date of May 7. I can now make a list of what I need for the move, such as boxes and create a schedule of prepacking. I can also make a list of resources I need for the move, vehicles, equipment, and people who can help. This would also include ideas for incentives such as pizza and beer. You get the idea.
That is just one of the projects I am currently working on. Obviously, Health and Diet is another and Budget.
MindMapping allows you to expand on all the little details required to make it fun and manageable. So wish me luck with the move. I just might need it.
Whether you use an Excel spreadsheet, Outlook, or an outside program such as MindJet MindManager, the goal is to account for it, SET DEADLINES, and take ACTION. That is all it takes, but you have to write it out. I know there are a lot of people who say they have it in their head of what they need to do, “It’s all up here” (motioning to their noggin), but unless you get it down on something, you will reduce your chances of success.
Here is to your success! Please let the rest of us know what you use to track and manage your projects and goals. I am sure we all could use the ideas and suggestions. I know I would. I’m always looking for a better way of doing things. I look forward to hearing from you.
To your SUCCESS!
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=8cfb28d1-241a-4992-a566-21f4b7d8b88b)
I learned about a new tutorial program thanks to Administrative Arts and, because I love to share new information that I am fortunate enough to come across to my readers, this is a great new add-on feature to learn Office 2007. Programs are continually evolving and changing to some degree or another. Amazingly, Office is coming out with a 2010. I just can’t keep up!
Ribbon Hero. What makes this tutorial program different is it is downloaded and incorporated into your Office suite. You collect learning skill points as you progress through the different features in Word, Excel, and Powerpoint directly from your program.
You will love how Microsoft has brought learning their programs to a whole nother level. Check it out here.
What websites or tutorials do you know and use, provide great information, and keep you abreast of technology? Would love to hear from you!
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=e5aa4ba8-6ec9-4f7a-8317-e8373ed13aac)
