Card Tricks

Posts Tagged ‘function’

See how you can use the non-Ctrl+Shift+Enter function AGGREGATE in place of SMALL in an array formula. Link for file: flightline.highline.edu

See how to create a dynamic frequency table and histogram chart using Defined Name formulas for dynamic ranges that use the INDEX function. See other formulas that use the COUNTIF, IF and ROWS functions that help to make it fully dynamic. This dynamic chart and table will update anytime that new data is added to column A.

See how to use Data Validation List Drop-down and the hyperlink function to create a list of hyper links. See how to link to a web site or a cell on a different worksheet.

See how to count with multiple criteria! See how to do it with the new Excel 2007 COUNTIFS function and see how to do it in Excel 2003 with the SUM & IF functions in an Array formula (Don’t forget Ctrl + Shift + Enter). See that the new Excel 2007 COUNTIFS function is a much easier solution than was available in earlier versions of Excel. See how to get a count of the number of sales for a department AND a Product. Count values between Upper & Lower Value. Count values between an upper and lower bound. Count Values based on Two Conditions. Count Values based on two criteria. Count Values based on multiple conditions criteria. Count values between an upper and lower Limit.

See how to streamline filtering when you have categories of criteria using VLOOKUP, Data Validation List and the OR function.