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

Data Analysis

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to sum a total in multiple Excel tables
  • How to create running total in an Excel Table
  • Calculate Conditional Percentile ‘IF’ in table in Excel

References

  • Left Lookup in Excel
  • How to use Excel MATCH Function
  • Extract data with helper column in Excel
  • How to get first row number in range in Excel
  • Approximate match with multiple criteria in Excel

Data Validations

  • Excel Data validation no punctuation
  • Excel Data validation require unique number
  • Excel Data validation date in next 30 days
  • How To Create Drop-down List in Excel
  • Excel Data validation must not contain

How to test for all values in a range are at least in Excel

by

To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function.

Formula

=NOT(COUNTIF(range,"<65"))

Explanation

In the example shown, the formula in G5 is:

=NOT(COUNTIF(B5:F5,"<65"))

How this formula works

At the core, this formula uses the COUNTIF function to count any cells that fall below a given value, which is hardcoded as 65 in the formula:

COUNTIF(B5:F5,"<65")

In this part of the formula, COUNTIF will return a positive number if any cell in the range is less than 65, and zero if not. In the range B5:F5, there is one score below 65 so COUNTIF will return 1.

The NOT function is used to convert the number of from COUNTIF into a TRUE or FALSE result. The trick is that NOT also “flips” the result at the same time:

  • If any values are less than 65, COUNTIF returns a positive number and NOT returns FALSE
  • f no values are less than 65, COUNTIF returns a zero and NOT returns TRUE

This is the equivalent of wrapping COUNTIF inside IF and providing a “reversed” TRUE and FALSE result like this:

=IF(COUNTIF(B5:F5,"<65"),FALSE,TRUE)

Post navigation

Previous Post:

How to generate random date between two dates in Excel

Next Post:

Popularly Used Excel Functions and their examples

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

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

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function
  • Nested IF function example in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add business days to date in Excel
  • Dynamic date list in Excel
  • Get days between dates ignoring years in Excel
  • Calculate days remaining in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to create dynamic named range with INDEX in Excel
  • Excel Autofill Cell Ranges, Copy, Paste
  • Check if multiple cells have same value in Excel
  • How to get amount with percentage in Excel
  • Count cells that contain errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning