Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • How to use Excel INDIRECT Function
  • Extract data with helper column in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel

Data Analysis Examples

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Data Series in Excel
  • Excel Line Chart
  • Remove Duplicates Example in Excel
  • Number and Text Filters Examples in Excel

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Data validation must not exist in list
  • Excel Data validation exists in list
  • Excel Data validation must contain specific text
  • Excel Data validation only dates between

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

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

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

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

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

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

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

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
  • Join Text Strings Using Concatenate or ‘& Operator’ in Excel
  • Add line break based on OS in Excel
  • LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation
  • How to extract name from email address in Excel
  • How to count specific characters in a range in Excel
  • Convert date string to date time in Excel
  • How to calculate workdays per month in Excel
  • How to calculate next day of week in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • How to get Holiday Date from Year in Excel
  • Bond valuation example in Excel
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • How to calculate present value of annuity in excel
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • Example of Future value of annuity in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles