The RANDARRAY function returns an array of random numbers. Users can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values. For example:
Argument
Required
Description
[rows]
N
The number of rows to be returned
[columns]
N
The number of columns to be returned
[min]
N
The minimum number you would like returned
[max]
N
The maximum number you would like returned
[whole_number]
N
Return a whole number or a decimal value: true for a whole number, false for a decimal number.
Note:
The RANDARRAY function is only valid when allowDynamicArray is true.
The RANDARRAY function will return an array, which will spill if it's the final result of a formula.
If you don't input a row or column argument, RANDARRAY will return a single value between 0 and 1.
If you don't input a minimum or maximum value argument, RANDARRAY will default to 0 and 1 respectively.
The minimum number argument must be less than the maximum number, otherwise RANDARRAY will return a #VALUE! error.
If you don't input a whole_number argument, RANDARRY will default to FALSE, or decimal value.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
export function AppFunc() {
const initSpread = (spread) => {
spread.options.allowDynamicArray = true;
var sheet = spread.getActiveSheet();
spread.suspendPaint();
spread.suspendCalcService();
sheet.name('RANDARRAY');
sheet.defaults.colWidth = 80;
sheet.setColumnWidth(0, 20);
sheet.setColumnWidth(1, 20);
sheet.setText(0, 2, 'The RANDARRAY function allows you to generate a list of random numbers from 0 to 1 in an array. Syntax: RANDARRAY([rows],[columns])');
var row = 2;
var col = 2;
var col2 = col + 5;
var formula = 'SEQUENCE(1, 5)';
sheet.setText(row, col, formula);
sheet.setFormula(++row, col, formula);
row++;
applyTableStyleForRange(sheet, row + 1, col, 5, 5);
formula = 'DATE(YEAR(TODAY()), C' + row + ':G' + row + ' , 1)';
var formatter = 'mmmm';
sheet.setText(row + 1, col2, ' <--- ' + formula + ' with ' + formatter);
sheet.setFormula(++row, col, formula);
sheet.getRange(row, col, 1, 5).formatter(formatter);
row++;
formula = 'RANDARRAY(4,5)';
formatter = '0.000000'
sheet.setText(row, col2, ' <--- ' + formula + ' with ' + formatter);
sheet.setFormula(row, col, formula);
sheet.getRange(row, col, 4, 5).formatter(formatter);
row += 6;
applyTableStyleForRange(sheet, row, col, 4, 5, { showHeader: false });
formula = 'INT(RANDARRAY(4,5)*100)';
sheet.setFormula(row, col, formula);
sheet.setText(row, col2, ' <--- ' + formula);
row += 6;
applyTableStyleForRange(sheet, row, col, 11, 1);
applyTableStyleForRange(sheet, row, col + 2, 11, 1);
sheet.setText(row - 1, col + 1, 'Random order: SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))');
sheet.setText(row - 1, col + 7, 'Sort range by birthday column: SORTBY(G20:H27,H20:H27)');
formula = 'SEQUENCE(10)'
sheet.setText(row, col, 'Units');
sheet.setText(row, col + 2, 'SortBy');
row++;
sheet.setFormula(row, col, formula);
formula = 'SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))';
sheet.setFormula(row, col + 2, formula);
col += 4;
applyTableStyleForRange(sheet, row - 1, col, 9, 2);
applyTableStyleForRange(sheet, row - 1, col + 3, 9, 2);
sheet.setArray(row - 1, col, [['Name', 'Birthday', '', 'Name', 'Birthday']]);
sheet.setArray(row, col, ["Fritz", "Xi", "Amy", "Sravan", "Tom", "Fred", "Hector", "Sal"]);
sheet.setFormula(row, col + 1, 'RANDARRAY(COUNTA(C20:C27))*40000');
sheet.setFormula(row, col + 3, 'SORTBY(G20:H27,H20:H27)');
formatter = 'mm/dd/yyyy';
sheet.getRange(row, col + 1, 8, 1).formatter(formatter);
sheet.getRange(row, col + 4, 8, 1).formatter(formatter);
spread.resumeCalcService();
spread.resumePaint();
}
const applyTableStyleForRange = (sheet, row, col, rowCount, colCount, options) => {
var tableName = "tmpTable";
var TableThemes = GC.Spread.Sheets.Tables.TableThemes;
// use table to help set style then remove like convert table to range in Excel
sheet.tables.add(tableName, row, col, rowCount, colCount, TableThemes.medium7, options);
sheet.tables.remove(tableName, 2 /* keep style */);
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>;
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>;
}
initSpread(spread) {
spread.options.allowDynamicArray = true;
var sheet = spread.getActiveSheet();
spread.suspendPaint();
spread.suspendCalcService();
sheet.name('RANDARRAY');
sheet.defaults.colWidth = 80;
sheet.setColumnWidth(0, 20);
sheet.setColumnWidth(1, 20);
sheet.setText(0, 2, 'The RANDARRAY function allows you to generate a list of random numbers from 0 to 1 in an array. Syntax: RANDARRAY([rows],[columns])');
var row = 2;
var col = 2;
var col2 = col + 5;
var formula = 'SEQUENCE(1, 5)';
sheet.setText(row, col, formula);
sheet.setFormula(++row, col, formula);
row++;
this.applyTableStyleForRange(sheet, row + 1, col, 5, 5);
formula = 'DATE(YEAR(TODAY()), C' + row + ':G' + row +' , 1)';
var formatter = 'mmmm';
sheet.setText(row + 1, col2, ' <--- ' + formula + ' with ' + formatter);
sheet.setFormula(++row, col, formula);
sheet.getRange(row, col, 1, 5).formatter(formatter);
row++;
formula = 'RANDARRAY(4,5)';
formatter = '0.000000'
sheet.setText(row, col2, ' <--- ' + formula + ' with ' + formatter);
sheet.setFormula(row, col, formula);
sheet.getRange(row, col, 4, 5).formatter(formatter);
row += 6;
this.applyTableStyleForRange(sheet, row, col, 4, 5, { showHeader: false });
formula = 'INT(RANDARRAY(4,5)*100)';
sheet.setFormula(row, col, formula);
sheet.setText(row, col2, ' <--- ' + formula);
row += 6;
this.applyTableStyleForRange(sheet, row, col, 11, 1);
this.applyTableStyleForRange(sheet, row, col + 2, 11, 1);
sheet.setText(row - 1, col + 1, 'Random order: SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))');
sheet.setText(row - 1, col + 7, 'Sort range by birthday column: SORTBY(G20:H27,H20:H27)');
formula = 'SEQUENCE(10)'
sheet.setText(row, col, 'Units');
sheet.setText(row, col + 2, 'SortBy');
row++;
sheet.setFormula(row, col, formula);
formula = 'SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))';
sheet.setFormula(row, col + 2, formula);
col += 4;
this.applyTableStyleForRange(sheet, row - 1, col, 9, 2);
this.applyTableStyleForRange(sheet, row - 1, col + 3, 9, 2);
sheet.setArray(row - 1, col, [['Name', 'Birthday', '', 'Name', 'Birthday']]);
sheet.setArray(row, col, ["Fritz", "Xi", "Amy", "Sravan", "Tom", "Fred", "Hector", "Sal"]);
sheet.setFormula(row, col + 1, 'RANDARRAY(COUNTA(C20:C27))*40000');
sheet.setFormula(row, col + 3, 'SORTBY(G20:H27,H20:H27)');
formatter = 'mm/dd/yyyy';
sheet.getRange(row, col + 1, 8, 1).formatter(formatter);
sheet.getRange(row, col + 4, 8, 1).formatter(formatter);
spread.resumeCalcService();
spread.resumePaint();
}
applyTableStyleForRange(sheet, row, col, rowCount, colCount, options) {
var tableName = "tmpTable";
var TableThemes = GC.Spread.Sheets.Tables.TableThemes;
// use table to help set style then remove like convert table to range in Excel
sheet.tables.add(tableName, row, col, rowCount, colCount, TableThemes.medium7, options);
sheet.tables.remove(tableName, 2 /* keep style */);
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
