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

Lookup and Reference Examples

  • Get nth match with INDEX / MATCH in Excel
  • Approximate match with multiple criteria in Excel
  • How to use Excel VLOOKUP Function
  • How to use Excel INDIRECT Function
  • Last row number in range

Data Analysis Examples

  • How to create Checklist in Excel
  • Filter Data Based on Date in Excel
  • Excel Pie Chart
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to Create Area Chart in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • How To Create Drop-down List in Excel
  • Excel Data validation allow weekday only
  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days

Convert Negative Numbers to Zero in Excel

by

A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged.

Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps.

Worked Example:   Calculate total hours that fall between two times in Excel

1. Select cell C1.

2. Enter the formula =A1-B1

3. Click the lower right corner of cell C1 and drag the formula down.

Worked Example:   Maximum value if in Excel

4. To change the negative numbers to zero but leave the positive numbers unchanged, instead of =A1-B1, use =MAX(0,A1-B1) and drag the function down.

Worked Example:   nth largest value in Excel

Result:


Result:

Explanation: the MAX function returns the maximum of two values. If the result of the formula is negative, the function returns 0.

Post navigation

Previous Post:

How To Create Pareto Chart in Excel

Next Post:

Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

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
  • Convert text to numbers using VALUE function in Excel
  • Split dimensions into three parts in Excel
  • How to find and replace multiple values at same time in Excel
  • SUBSTITUTE function vs REPLACE function in Excel
  • How to extract multiple lines from a cell in Excel
  • How to calculate percent of year complete in Excel
  • How to calculate nth day of year in Excel
  • Count birthdays by month in Excel
  • Get work hours between dates in Excel
  • How to get year from date in Excel
  • Calculate interest rate for loan in Excel
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • FV function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles