Products

Documentation keyboard_arrow_right Formulas keyboard_arrow_right Functions keyboard_arrow_right Abs

ABS function

PRO BASIC

The ABS function in Jspreadsheet Formulas Pro is a fundamental mathematical tool that calculates absolute values, making it essential for various professional applications:

Financial Analysis:

  • Portfolio performance tracking
  • Trading loss calculations
  • Budget variance analysis
  • Cash flow monitoring
  • Risk assessment metrics

Engineering Applications:

  • Tolerance measurements
  • Stress analysis
  • Vibration amplitude
  • Signal processing
  • Error margins

Scientific Research:

  • Data deviation analysis
  • Experimental error calculation
  • Statistical significance
  • Measurement precision
  • Threshold detection

Quality Control:

  • Manufacturing tolerances
  • Process variation
  • Specification compliance
  • Performance metrics
  • Calibration checks

Business Analytics:

  • Sales performance tracking
  • Inventory discrepancies
  • Customer satisfaction metrics
  • Efficiency measurements
  • Trend analysis

The function converts negative values to positive while preserving positive values, enabling accurate magnitude calculations regardless of direction or sign. This makes it invaluable for professional applications requiring precise measurement of differences, deviations, or distances from reference points.

Documentation

Returns the absolute value of a number.

Category

Math and trigonometry

Syntax

ABS(x)

Parameter Description
x The number for which to return the absolute value.

Behavior

The ABS function is designed to return the absolute value of a number. The absolute value of a number is the number without its sign. Therefore, ABS transforms negative numbers into positive ones, but leaves positive numbers and zero unchanged. Here is how it handles various inputs:

  • Empty cells: The ABS function treats empty cells as zero.
  • Texts: If the ABS function is used on a cell containing text, it will result in a #VALUE! error because the function only processes numerical values.
  • Booleans: Boolean values are treated as numbers in this context. TRUE is considered as 1 and FALSE as 0.
  • Errors: If the cell reference or the argument in the ABS function is an error, the function will return that error.

Common Errors

Error Description
#VALUE! This error occurs when the cell reference or argument in the ABS function is text. The ABS function only processes numerical values.
#REF! This error occurs when the cell reference is not valid. For example, you may have deleted a cell that was being referenced by the ABS function.

Best practices

  • Always check the cell reference or argument in the ABS function to ensure it contains numeric values. Using ABS on text or non-numeric values will result in a #VALUE! error.
  • Use the ABS function when you are interested in the magnitude of a number regardless of its sign. It is particularly useful in mathematical calculations where negative values can distort the results.
  • Be cautious when using the ABS function with boolean values. In spreadsheets, TRUE is treated as 1 and FALSE as 0.
  • When working with large data sets, ensure that the ABS function is not referencing any error cells, as it will return the same error.

Usage Examples

Here are comprehensive examples demonstrating the ABS function in various professional scenarios:

1. Financial Analysis:

// Investment Performance
=ABS(current_value - initial_investment)
// Track absolute portfolio gains/losses

// Trading Analysis
=ABS(exit_price - entry_price)
// Calculate price movement magnitude

// Risk Management
=ABS(portfolio_return - benchmark_return)
// Track tracking error

// Budget Control
=ABS(actual_expense - budgeted_amount)
// Monitor spending variations

// Revenue Analysis
=ABS(SUM(actual_revenue) - SUM(projected_revenue))
// Assess forecast accuracy

2. Engineering Applications:

// Structural Analysis
=ABS(measured_stress - allowable_stress)
// Check structural safety margins

// Vibration Analysis
=ABS(peak_amplitude - baseline)
// Monitor vibration levels

// Circuit Analysis
=ABS(measured_voltage - nominal_voltage)
// Check voltage deviation

// Thermal Analysis
=ABS(operating_temp - design_temp)
// Monitor temperature variations

// Dimensional Control
=ABS(actual_dimension - nominal_dimension)
// Verify manufacturing tolerances

3. Scientific Research:

// Experimental Analysis
=ABS(experimental_value - theoretical_value)
// Calculate experimental error

// Statistical Analysis
=ABS(sample_mean - population_mean)
// Measure sampling error

// Measurement Precision
=ABS(measured_value - calibration_standard)
// Assess measurement accuracy

// Data Validation
=ABS(new_reading - previous_reading)
// Detect significant changes

// Error Analysis
=ABS(observed_value - expected_value)/expected_value
// Calculate relative error

4. Quality Control:

// Process Control
=ABS(measured_value - target_value)
// Monitor process deviation

// Product Testing
=ABS(test_result - specification_limit)
// Check specification compliance

// Performance Metrics
=ABS(actual_performance - benchmark)
// Evaluate performance gaps

// Quality Metrics
=ABS(defect_rate - acceptable_limit)
// Monitor quality levels

// Equipment Calibration
=ABS(instrument_reading - standard_value)
// Verify calibration accuracy

5. Business Analytics:

// Sales Analysis
=ABS(actual_sales - sales_target)
// Track sales performance

// Inventory Management
=ABS(physical_count - system_count)
// Identify inventory discrepancies

// Customer Satisfaction
=ABS(satisfaction_score - target_score)
// Monitor service quality

// Efficiency Metrics
=ABS(actual_time - standard_time)
// Measure process efficiency

// Cost Analysis
=ABS(actual_cost - standard_cost)
// Track cost variances

6. Advanced Applications:

// Multi-dimensional Analysis
=ABS(SUMPRODUCT(actual_values - target_values))
// Calculate total deviation across dimensions

// Trend Analysis
=ABS(current_period - previous_period)/previous_period
// Calculate percentage change

// Composite Metrics
=ABS(weighted_score - benchmark_score)
// Evaluate weighted performance

// Statistical Control
=ABS(value - AVERAGE(range))/STDEV(range)
// Calculate z-score magnitude

// Error Propagation
=SQRT(SUM(ABS(partial_errors^2)))
// Calculate combined error

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjJiZmM0NWU0Yzc1YzU5ZmRmZDQ1ZDM2ZDMyNzI2MTliYjhhZWVlYjlkMmI5NmFkMWYzMzVhN2NlMDRhNGNlMGYyNGRlY2MxN2RjOTk0MzQyZjc5YzYwMDdmMDM2ZTFhNzljNjUwN2E3ZDdlODA3YjI5ZGI1ODBiNzZiN2EyZDEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Temperature Change",
        "Absolute Change"
    ],
    [
        -12.5,
        "=ABS(A2)"
    ],
    [
        8.3,
        "=ABS(A3)"
    ],
    [
        -4.7,
        "=ABS(A4)"
    ],
    [
        0,
        "=ABS(A5)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('ZjJiZmM0NWU0Yzc1YzU5ZmRmZDQ1ZDM2ZDMyNzI2MTliYjhhZWVlYjlkMmI5NmFkMWYzMzVhN2NlMDRhNGNlMGYyNGRlY2MxN2RjOTk0MzQyZjc5YzYwMDdmMDM2ZTFhNzljNjUwN2E3ZDdlODA3YjI5ZGI1ODBiNzZiN2EyZDEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Temperature Change",
        "Absolute Change"
    ],
    [
        -12.5,
        "=ABS(A2)"
    ],
    [
        8.3,
        "=ABS(A3)"
    ],
    [
        -4.7,
        "=ABS(A4)"
    ],
    [
        0,
        "=ABS(A5)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('ZjJiZmM0NWU0Yzc1YzU5ZmRmZDQ1ZDM2ZDMyNzI2MTliYjhhZWVlYjlkMmI5NmFkMWYzMzVhN2NlMDRhNGNlMGYyNGRlY2MxN2RjOTk0MzQyZjc5YzYwMDdmMDM2ZTFhNzljNjUwN2E3ZDdlODA3YjI5ZGI1ODBiNzZiN2EyZDEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Temperature Change",
        "Absolute Change"
    ],
    [
        -12.5,
        "=ABS(A2)"
    ],
    [
        8.3,
        "=ABS(A3)"
    ],
    [
        -4.7,
        "=ABS(A4)"
    ],
    [
        0,
        "=ABS(A5)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjJiZmM0NWU0Yzc1YzU5ZmRmZDQ1ZDM2ZDMyNzI2MTliYjhhZWVlYjlkMmI5NmFkMWYzMzVhN2NlMDRhNGNlMGYyNGRlY2MxN2RjOTk0MzQyZjc5YzYwMDdmMDM2ZTFhNzljNjUwN2E3ZDdlODA3YjI5ZGI1ODBiNzZiN2EyZDEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNVGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    [
                        "Temperature Change",
                        "Absolute Change"
                    ],
                    [
                        -12.5,
                        "=ABS(A2)"
                    ],
                    [
                        8.3,
                        "=ABS(A3)"
                    ],
                    [
                        -4.7,
                        "=ABS(A4)"
                    ],
                    [
                        0,
                        "=ABS(A5)"
                    ]
                ]
            }]
        });
    }
}

Edit this page on GitHub