SUMX2PY2 function

PRO BASIC

In Jspreadsheet Formulas Pro, the SUMX2PY2 function is a convenient tool that calculates the combined total of the squares of numbers in two distinct groups or ranges. This function works by first squaring each individual number in both ranges and then adding up those squared results. You would use it when you need to perform this specific mathematical operation on two datasets or ranges in your Jspreadsheet project. It's a quick and efficient way to perform this operation without manually squaring and summing each value.

Documentation

Returns the sum of squares of corresponding values in two arrays or ranges.

Syntax

SUMX2PY2(array_x, array_y)

Parameter	Description
`array_x`	The first array or range of values.
`array_y`	The second array or range of values.

Behavior

The SUMX2PY2 function calculates the sum of the squares of corresponding values in two arrays. The behavior of the function with different types of data is as follows:

Empty cells: If any cell in either array is empty, the function treats it as 0.
Text: If any cell in either array contains text, the function returns an error.
Booleans: If any cell in either array contains a boolean value (TRUE or FALSE), the function treats TRUE as 1 and FALSE as 0.
Errors: If any cell in either array contains an error, the function itself will return an error.
Array length: The arrays need to be of the same length. If they are not, the function will return an error.

Common Errors

Error	Description
#VALUE!	This error is returned if the two array arguments provided to the function do not have the same length.
#N/A	This error is returned if any cell in either array is not available.

Best practices

Always ensure that the arrays you input into the SUMX2PY2 function have the same length. If they don't, the function will return an error.

Avoid including non-numeric values in the arrays you input into the function. If a cell contains text or an error, the function will return an error.

Be mindful of boolean values when using this function. Remember that the function treats TRUE as 1 and FALSE as 0.

If you're dealing with large arrays, consider using the function with range references rather than typing out the array elements individually. This will make your formula easier to read and manage.

Usage

A few examples using the SUMX2PY2 function.

SUMX2PY2([1, 2, 3], [4, 5, 6])  
→ Returns 91, since (1²+4²) + (2²+5²) + (3²+6²) = 17 + 29 + 45 = 91.  

SUMX2PY2(A2:A4, B2:B4)  
→ Squares each value in columns A and B, adds them pairwise, and then sums the results.  

SUMX2PY2([2, 3], [TRUE, FALSE])  
→ Returns (2²+1²) + (3²+0²) = 5 + 9 = 14.  

SUMX2PY2([10, 20], [30, 40])  
→ Returns (10²+30²) + (20²+40²) = 1000 + 2000 = 3000.

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('NDQ3ZDhkMTA2NDdiZDVjMzAwZTUyNTg4NTgwMjljMTY5Yjc0NjY3MmEzMjI3OTg2NWU5YWI2NGMwNjA4OTliMjRiM2ZiNzM2NzI2Yzc3Mzk5ZTQ5N2RkZDFjZGNhODE1MGNhMTRiODBiZmRjZjM2NjRlM2EwMWZjYTY1ZDI2OWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "X Values",
        "Y Values",
        "Sum of Squares"
    ],
    [
        2,
        3,
        "=SUMX2PY2(A2:A4,B2:B4)"
    ],
    [
        4,
        1
    ],
    [
        6,
        5
    ]
]
  }]
});
</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('NDQ3ZDhkMTA2NDdiZDVjMzAwZTUyNTg4NTgwMjljMTY5Yjc0NjY3MmEzMjI3OTg2NWU5YWI2NGMwNjA4OTliMjRiM2ZiNzM2NzI2Yzc3Mzk5ZTQ5N2RkZDFjZGNhODE1MGNhMTRiODBiZmRjZjM2NjRlM2EwMWZjYTY1ZDI2OWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "X Values",
        "Y Values",
        "Sum of Squares"
    ],
    [
        2,
        3,
        "=SUMX2PY2(A2:A4,B2:B4)"
    ],
    [
        4,
        1
    ],
    [
        6,
        5
    ]
];

    // 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('NDQ3ZDhkMTA2NDdiZDVjMzAwZTUyNTg4NTgwMjljMTY5Yjc0NjY3MmEzMjI3OTg2NWU5YWI2NGMwNjA4OTliMjRiM2ZiNzM2NzI2Yzc3Mzk5ZTQ5N2RkZDFjZGNhODE1MGNhMTRiODBiZmRjZjM2NjRlM2EwMWZjYTY1ZDI2OWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "X Values",
        "Y Values",
        "Sum of Squares"
    ],
    [
        2,
        3,
        "=SUMX2PY2(A2:A4,B2:B4)"
    ],
    [
        4,
        1
    ],
    [
        6,
        5
    ]
]

        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('NDQ3ZDhkMTA2NDdiZDVjMzAwZTUyNTg4NTgwMjljMTY5Yjc0NjY3MmEzMjI3OTg2NWU5YWI2NGMwNjA4OTliMjRiM2ZiNzM2NzI2Yzc3Mzk5ZTQ5N2RkZDFjZGNhODE1MGNhMTRiODBiZmRjZjM2NjRlM2EwMWZjYTY1ZDI2OWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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: [
    [
        "X Values",
        "Y Values",
        "Sum of Squares"
    ],
    [
        2,
        3,
        "=SUMX2PY2(A2:A4,B2:B4)"
    ],
    [
        4,
        1
    ],
    [
        6,
        5
    ]
]
            }]
        });
    }
}

Edit this page on GitHub