Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Understanding Anova in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How to Create Column Chart in Excel
  • How To Perform and Interpret Regression Analysis in Excel

References

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Two-column Lookup in Excel
  • Count rows that contain specific values in Excel
  • Two-way lookup with VLOOKUP in Excel
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation must begin with
  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text
  • Excel Data validation whole percentage only

Tag: LARGE function

LARGE function: Description, Usage, Syntax, Examples and Explanation

by

What is LARGE function in Excel? LARGE function is one of Statistical functions in Microsoft Excel that returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score. Syntax …

Continue Reading

Highlight top values in Excel

by

This tutorial shows how to Highlight top values in Excel using the example below; Formula =A1>=LARGE(data,N) Explanation To highlight the highest values in a set of data with conditional formatting, you can use a formula based on the LARGE function. In the example shown, the formula used for conditional formatting is: =B4>=LARGE(data,input) Note: Excel contain a …

Continue Reading

Large with criteria in Excel

by

This tutorial shows how to calculate Large with criteria in Excel using the example below; Formula {=LARGE(IF(criteria,values),n)} Explanation To return the largest values in a set of data with criteria, you can use the a formula based on the LARGE and IF functions. In the example shown, the formula in F5 is: {=LARGE(IF(B5:B11=”B”,C5:C11),2)} Note: this …

Continue Reading

nth largest value in Excel

by

This tutorial shows how to calculate nth largest value in Excel using the example below; Formula =LARGE(range,nth) Explanation To get the 2nd largest value, 3rd largest value, 4th largest value, and so on, from a set of data, you can use the LARGE function. In the example shown, the formula in J5 is: =LARGE($C5:$G5,2) How …

Continue Reading

nth largest value with criteria in Excel

by

This tutorial shows how to calculate nth largest value with criteria in Excel using the example below; To get the 2nd largest value, 3rd largest value, 4th largest value, and so on, where each value matches supplied criteria, you can use an array formula that uses the LARGE and IF functions. Formula {=LARGE(IF(range=criteria,values),n)} Explanation In …

Continue Reading

Get last match in Excel

by

This tutorial shows how to Get last match in Excel using the example below; Formula {=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1))} Explanation To get the position of the last match (i.e. last occurrence) of a lookup value, you can use an array formula based on the IF, ROW, INDEX, MATCH, and MAX functions. In the example shown, the formula in …

Continue Reading

Sum top n values in Excel

by

This tutorial shows how to Sum top n values in Excel using the example below; To sum the top values in a range, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. Formula =SUMPRODUCT(LARGE(range,{1,2,N})) Explanation In the generic form of the formula (above), a range of cells that contain numeric values …

Continue Reading

Average top 3 scores in Excel

by

This tutorial shows how to work  Average top 3 scores in Excel using the example below; Formula =AVERAGE(LARGE(range,{1,2,3})) Explanation To average the top 3 scores in a data set, you can use a formula based on the LARGE function. In the example shown, the formula in G6 is: =AVERAGE(LARGE(B6:F6,{1,2,3})) How this formula works The LARGE function …

Continue Reading

How To Use AGGREGATE function to sum a range with errors in Excel

by

Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this. 1. For example, Excel returns an error if you use the SUM function to sum a range with errors. 2. Use the AGGREGATE function to sum a range with errors. Explanation: …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert Excel time to Unix time in Excel
  • Basic timesheet formula with breaks in Excel
  • How to get workdays between dates in Excel
  • How to get same date next year or previous year in Excel
  • Sum race time splits in Excel

Grouping

  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel

General

  • How to calculate percent variance in Excel
  • Create dynamic workbook reference to another workbook in Excel
  • How to add sequential row numbers to a set of data in Excel
  • Share Excel data with Word documents
  • Count cells less than in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning