AVERAGEIF can be used to average cells based on dates, numbers, and text. Whenaverage_rangeis not provided, AVERAGEIF will average values in therangeargument. Whenaverage_rangeis provided, AVERAGEIF will average values inaverage_range. However, when alogical operatoris included with a number, the number and operator must be enclosed in quotes. Note the difference in the two examples below. Double quotes are also used for text values. Notice the less thanoperator(which is text) is enclosed in quotes....

Each condition is provided with a separaterangeandcriteria. However, the syntax used to apply conditions is a bit tricky because it is unusual in Excel. See below for details. The syntax for the AVERAGEIFS function depends on the criteria being evaluated. Each separate condition will require arangeandcriteria. The second argument,range1, is the range to which the first condition should be applied. The third argument,criteria1, contains the condition that should be applied torange1, along with anylogical operators....

A bar chart is one of Excel’s primary chart types and a good choice for categorical data. Also, because of the horizontal layout, bar charts have room to accommodate longer category names. Bar charts are also versatile.

The Bar of Pie Chart is a built-in chart throw in in Excel. Pie charts work best to display data with a small number of categories (2-5).

Base is specified with theradixargument. The BASE function takes threearguments:number,radix, andmin_length.Numbershould be an integer between 1 and 2^53. Ifnumberis negative, BASE returns a #NUM! The optionalmin_lengthargument is the minimum string length that BASE should return. Whenmin_lengthis provided, BASE will pad the output with zeros as needed to achieve the length specified. Examples Theradixargument specifies base and the output from the BASE function is atext string. For example, the DECIMAL function can convert the binary number 1101 into the decimal number 13....

Explanation The example on this page shows a simple array formula. See the video links below for a quick demo. MAX ignores empty cells, the logical values TRUE and FALSE, and text values. MIN Function The Excel MIN function returns the smallest numeric value in the data provided. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values. What are Dynamic Array formulas?

COUNTIF can be used to count… Related videos How to use the COUNTIF function

AVERAGE function The AVERAGE function calculates the average (arithmetic mean) of numbers provided asarguments. The count used by AVERAGE depends on the data. However, zero (0) values are included as you might see in row 13. Related videos How to calculate an average value How to use the AVERAGEIFS function

Explanation In this example, the IFERROR function is used to trap and suppress the #DIV/0! error that takes place if there is no value for Orders (column D). Without IFERROR, the formula C5/D5 would display a #DIV/0! error in E6 and E9. The second argument is only used if the first argument throws an error. When the formula returns a normal result, the result is displayed. When the formula returns #DIV/0!...

When the state in H4 is changed, the formula should return a new set of records. FILTER function TheFILTER function"filters" a range of data based on supplied criteria. The result is anarrayof matching values from the original data. you’re able to customize this message as you like. Supply an empty string ("") to display nothing. If you omitif_emptyaltogether, FILTER will return a #CALC! error when no data is returned. When the formula above is entered, theincludeargument is evaluated....

Explanation The REPT function simply repeats values. In this example, we use the CHAR function to output a character with a code of 110. This character, when formatted with the Wingdings font, will output a solid square. This has the effect of outputting one full square per 100 dollars of sales. Increase or decrease the divisor to suit the data and available space. For example, =REPT(“x”,5) returns “xxxxx”. CHAR Function The Excel CHAR function returns a character when given a valid character code....

Explanation This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock. In the example, colors are treated as unique item identifiers imagine a product available in one size only in just three colors: red, blue, or green. The key to this approach is to useExcel Tables, because Table ranges automatically expand to handle changes in data. This means we can get a total of all incoming red items with:...

This works perfectly as long as numeric values are unique. This means the expression will return zero for each numeric value until a duplicate is encountered. This effectively breaks ties, and allows the formula to generate a sequential list of numbers with no gaps. Once the formula is in place, data can be sorted by thehelper column. It can also be retrieved with INDEX using the values in the helper column....