Excel Formulas to Fluidtable Reference Guide
clacla
Fluidtable is a modern spreadsheet application. Instead of just replicating Excel features, the goal is to think about the problem that they are trying to solve and came up with the best solution.
It means that you will have to learn a new way to do things. But don’t worry. It may be different, but not hard. And I am personally committed making it simpler and simpler.
Please, comment on the things that are not clear enough or too complex. I will prioritise those.
Note 1
In Excel, the columns are referenced by letters. Meanwhile, the rows are referenced by numbers starting from 1.
In Fluidtable both rows and columns are referenced by numbers starting from 0.
Note 2
In Fluidtable, you can add formulas/code by selecting a cell, then click the **Add Code**
button. Else, you can write directly into the selected cell, by prefixing your code with =js
.
Table of Contents
- Arithmetic operations
- Get values
- Get a cell value
- Get a row values
- Get a column’s values
- Get a range of values
- Get the current cell information
- Sum values
- Sum IF values pass a condition
- Count values
- Count non-empty values
- Count numeric values
- Count IF pass a condition
- Average
- Average IF passes a condition
- Does a value exists?
- Index of a value
- Is a value duplicate?
- Get duplicate values indexes
- Unique values
- Maximum value
- Minimum value
- Round a number
- Capitalize words
- IF statement
- Nested IF statements
- Index/Match
- Vlookup
- Hlookup
- Concatenate multiple values
- Length of a string
- Trim a string
- OR: Is any condition met?
- AND: Are all conditions met?
Arithmetic operations
Arithmetic operations are the same, but you have to prefix them by the return
keyword.
// Excel=123 * 2=(3 * 2) / (1 + 2)
// Fluidtablereturn 123 * 2return (3 * 2) / (1 + 2)
Get values
Get a cell value
Get a value from any table in the current document.
// Excel
\=B3
\=Sheet1!B5
\=A1 + C2
// Fluidtable
// Use Table.value(rowIndex, columnIndex, tableName)
return Table.value(2, 2)
return Table.value(4, 1, "Sheet1")
return Table.value(0, 0) + Table.value(1, 3)
Get a row values
Get a row of values from any table in the current document.
// Excel
\=2:2
\=Sheet1!5:5
// Fluidtable
// Use Table.row(rowIndex, tableName)
return Table.row(2)
return Table.row(5, "Sheet1")
Get a column’s values
Get a column of values from any table in the current document.
// Excel
\=B:B
\=Sheet1!C:C
// Fluidtable
// Use Table.column(columnIndex, tableName)
return Table.column(1)
return Table.column(2, "Sheet1")
Get a range of values
Get a 2D range of values (also known as a matrix) from any table in the current document.
// Excel
\=B1:C1
\=B1:B3
\=A1:C3
\=Sheet1!A1:B2
// Fluidtable
// Use Table.range(startRow, startColumn, endRow, endColumn, tableName)
return Table.range(0, 1, 0, 2)
return Table.range(0, 1, 2, 1)
return Table.range(0, 0, 2, 2)
return Table.range(0, 0, 1, 1, "Sheet1")
Get the current cell information
Get the current cell’s row, column and table name.
// Excel
\=ADDRESS(ROW(), COLUMN()) // -> $B$4
// Fluidtable
// Use Table.currentCellInfo()
return Table.currentCellInfo() // -> { row: 3, column: 1, tableName: "Table 1" }
return Table.currentCellInfo().row // -> 3
return Table.currentCellInfo().column // -> 1
return Table.currentCellInfo().tableName // -> Table 1
Sum values
Sum a range of values. Non-numeric values will automatically be filtered out.
// Excel
\=SUM(1, 2, 3)
\=SUM(A1, B1)
\=SUM(A1:B5)
// Fluidtable
// Use Values.sum(valuesRange)
return Values.sum(\[1, 2, 3\])
const values = \[1, 2, 3\]; return Values.sum(values)
return Values.sum(Table.column(2))
return Values.sum(Table.row(1))
Sum IF values pass a condition
Sum a series of values if they pass a given condition.
// Excel
\=SUMIF(A1:C1, "<1") // Sum values smaller than 1
// Fluidtable
// Use Values.sumIf(valuesRange, conditionFunction)
// Sum only the even numbers
return Values.sumIf(\[1, 2, 3\], val => val %2 === 0) // -> 2
// Sum row 0 values smaller than 1
return Values.sumIf(Table.row(0), val => val < 1)
Count values
Count the values in a range.
// Excel
\=COUNTA("ciao", null, 3) // -> 3
\=COUNTA(A1:A5) + COUNTBLANK(A1:A5) // -> 5
\=COUNTA(A1:B3) + COUNTBLANK(A1:B3) // -> 6
// Fluidtable
// Use Values.count(valuesRange)
return Values.count(\["ciao", null, 3\]) // -> 3
const values = \["ciao", null, 3\]; return Values.count(values); // -> 3
return Values.count(Table.row(2))
return Values.count(Table.column(1))
return Values.count(Table.range(0, 0, 0, 4)) // -> 5
return Values.count(Table.range(0, 0, 2, 2)) // -> 9
Count non-empty values
Count the non-empty values in a range.
// Excel
\=COUNTA("ciao", null, 3) // -> 1
\=COUNTA(A1:A5)
// Fluidtable
// Use Values.countNonEmpty(valuesRange)
return Values.countNonEmpty(\["ciao", null, 3\]) // -> 2
const values = \["ciao", null, 3\]; return Values.countNonEmpty(values); // -> 2
return Values.countNonEmpty(Table.column(2))
return Values.countNonEmpty(Table.row(2))
return Values.countNonEmpty(Table.range(0, 0, 2, 2))
Count numeric values
Count the numeric values in a range.
// Excel
\=COUNT("ciao", null, 3) // -> 1
\=COUNT(A1:A5)
// Fluidtable
// Use Values.countNumbers(valuesRange)
return Values.countNumbers(\["ciao", null, 3\]) // -> 1
const values = \["ciao", null, 3\]; return Values.countNumbers(values); // -> 1
return Values.countNumbers(Table.column(2))
return Values.countNumbers(Table.row(2))
return Values.countNumbers(Table.range(0, 0, 2, 2))
Count IF pass a condition
Count the values in a range that pass a given condition.
// Excel
\=COUNTIF(A1:C1, ">10") // Count values bigger than 10
\=COUNTIF(A1:C3, "b\*") // Count values starting with letter b
// Fluidtable
// Use Values.countIf(valuesRange, conditionFunction)
// Count values bigger than 10
return Values.countIf(Table.row(0), val => val > 10)
// Count values starting with letter b
return Values.countIf(Table.row(0), val => val.substring(0, 1) === "b")
// Count values of type string
const condition = (val) => { typeof val === ‘string’ }; return Values.countIf(Table.row(0), condition)
Average
Calculate the average of a range of numeric values. Empty and non-numeric values are automatically filtered out.
// Excel
\=AVERAGE(2, 4, 6) // -> 4
\=AVERAGE(A1:A3)
// Fluidtable
return Values.average(\[2, 4, 6\]) // -> 4
const values = \[2, 4, 6\]; return Values.average(values) // -> 4
return Values.average(Table.column(2))
return Values.average(Table.range(0, 0, 2, 2))
Average IF passes a condition
Calculate the average of a range of numeric values, if they pass a condition. Empty and non-numeric values are automatically filtered out.
// Excel
\=AVERAGEIF(A1:A3, ">2")
\=AVERAGEIF(B1:B5, "<1")
// Fluidtable
// Use Values.averageIf(valuesRange, conditionFunction)
return Values.averageIf(\[2, 4, 6,\], val => val > 2) // -> 5
// Average of the number who are between 10 and 100
return Values.averageIf(Table.row(0), val => val > 10 && val < 100)
Does a value exists?
Check if a value exists in a range of values.
// Excel
\=COUNTIF($A$2:$A2, $A2) > 0 // True if exist, false otherwise
// Fluidtable
// Use Values.exists(searchValue, valuesArray)
return Values.exists("Mia", \["Joe", "Ann", "Mia"\]) // -> true
return Values.exists(Table.value(0, 0), Table.column(2))
return Values.exists(Table.value(0, 0), Table.row(2))
Index of a value
Find the index of a given value in a range of values.
// Excel
\=MATCH("Alex", A2:A5, 0) // -> Index of row with value Alex
// Fluidtable
// Use Values.indexOf(searchValue, valuesArray)
return Values.indexOf("Alex", \["Ann", "Alex", "Joe"\]) // -> 1
return Values.indexOf(Table.value(0, 0), Table.column(2))
return Values.indexOf(Table.value(0, 0), Table.row(2))
Is a value duplicate?
Check if a given value is a duplicate
// Excel
\=COUNTIF($A$2:$A2, $A2) > 1 // True if duplicate, false otherwise
\=IF(COUNTIF($A$2:$A2, $A2) > 1, "Duplicate", "Unique")
// Fluidtable
// Use Values.isDuplicate(searchValue, values)
return Values.isDuplicate("Johnny", \["Ann", "Johnny", "Joe", "Johnny"\]) // -> true
return Values.isDuplicate(Table.value(0, 0), Table.column(0))
return Values.isDuplicate(Table.value(1, 3), Table.row(1))
Get duplicate values indexes
Get an array of indexes of the duplicates values.
// Excel
// Repeat for each row
\=COUNTIF($A$2:$A2, $A2) > 1
// Fluidtable
// Use Values.duplicatesIndexes(values)
return Values.duplicatesIndexes(\["Ann", "Johnny", "Joe", "Johnny", "Ann"\]) // -> \[3, 4\]
return Values.duplicatesIndexes(Table.column(0))
return Values.duplicatesIndexes(Table.row(3))
Unique values
Get a unique list of values from another list.
// Excel
\=INDEX(list, MATCH(0, COUNTIF($D$4:D4, list), 0))
// Fluidtable
// Use Values.uniques(inputValues)
return Values.uniques(\["Ann", "Johnny", "Joe", "Johnny", "Ann"\]) // -> \["Ann", "Johnny", "Joe"\]
return Values.uniques(Table.column(1))
return Values.uniques(Table.row(3))
Maximum value
Get the biggest value in a range of values.
// Excel
\=MAX(2, 4, 6) // -> 6
\=MAX(A1, A2, A3)
// Fluidtable
// Use Values.max(valuesArray)
return Values.max(\[2, 4, 6\]) // -> 6
return Values.max(Table.column(1))
return Values.max(Table.row(3))
Minimum value
Get the smallest value in a range of values.
// Excel
\=MIN(2, 4, 6) // -> 2
\=MIN(A1, A2, A3)
// Fluidtable
// Use Values.min(valuesArray)
return Values.min(\[2, 4, 6\]) // -> 2
return Values.min(Table.column(1))
return Values.min(Table.row(3))
Round a number
Round a number to a given number of decimal points.
// Excel
\=ROUND(1.1234, 2) // -> 1.12
\=ROUND(1.1234, 0) // -> 1
\=ROUND(A1, 1)
// Fluidtable
// Use Values.round(value, decimalsCount)
return Values.round(1.1234, 2) // -> 1.12
return Values.round(1.1234, 0) // -> 1
return Values.round(Table.value(0, 2), 1)
Capitalize words
Capitalize the first letter all the words in a text
// Excel
\=PROPER("johnny machete") // -> Johnny Machete
\=PROPER(A2)
// Fluidtable
// Use Values.capitalizeWords(string)
return Values.capitalizeWords("boe machete") // -> Boe Machete
return Values.capitalizeWords(Table.value(1, 2))
IF statement
Return a value depending on the result of a given condition.
// Excel
\=IF(8 > 6, "Passed", "Failed")
\=IF(C3 > D3, "Passed", "Failed")
\=IF(B2 > 1000, "Goal reached", "Goal missed")
// Fluidtable
// Use Javascript’s if statements short syntax
return Table.value(0, 0) > Table.value(2, 3) ? "Yes" : No"
return Table.value(1, 2) > 1000 ? "Goal reached" : "Goal missed"
// Use Javascript’s if statements standard syntax
if (Table.value(1, 2) > 1000) {
return "Goal reached";
} else {
return "Goal missed";
}
Nested IF statements
Return a value depending on the result of multiples given conditions. There are multiple syntaxes that you can use. Choose the one that you like the most. They are all correct.
// Excel
\=IF(C3 > D3, IF(A3 > 100, "Passed++", "Passed"), "Failed")
// Fluidtable
// Syntax A
if (Table.value(1, 2) > Table.value(3, 2)) {
if (Table.value(2, 0) > 100) {
return "Passed++";
} else {
return "Passed";
}
} else {
return "Failed";
}
// Syntax B
if (Table.value(1,2)>Table.value(3,2) && Table.value(2,0)>100) {
return "Passed++";
} else if (Table.value(1, 2) > Table.value(3, 2)) {
return "Passed";
} else {
return "Failed";
}
// Syntax C
return Table.value(1, 2) > Table.value(3, 2)
? Table.value(1, 2) > Table.value(3, 2) ? "Passed++" : "Passed"
: "Failed"
Index/Match
Find the index of a given value and then return another value on the same column or row.
// Excel
\=MATCH("Alex", A2:A5, 0) // -> Index of row with value Alex
\=INDEX(C2:C5, 3) // -> 3rd value in range C2:C5
\=INDEX(C2:C5, MATCH("Alex", A2:A5, 0)) // -> 3rd value in C2:C5
// Fluidtable
// Use Values.indexOf(searchValue, valuesArray)
// in combination with Table.value(rowIndex, columnIndex, tableName)
return Table.value(Values.indexOf("Anna, Table.column(1)), 2)
return Table.value(2, Values.indexOf(100, Table.row(0)))
const searchRowIndex = Values.indexOf("Anna", Table.column(1));
if (searchRowIndex === null) { return "Value not found"; }
return Table.value(searchRowIndex, 2);
const searchColumnIndex = Values.indexOf(100, Table.row(1));
if (searchColumnIndex === null) { return "Value not found"; }
return Table.value(2, searchColumnIndex);
Vlookup
Look for a value in a given column, then return another value on the same row. The solution is the same as for index/match.
// Excel
\=VLOOKUP(B18, $A$1:$B$13, 2, FALSE)
// Fluidtable
// Use Values.indexOf(searchValue, valuesArray)
// in combination with Table.value(rowIndex, columnIndex, tableName)
const searchRowIndex = Values.indexOf("Anna", Table.column(1));
if (searchRowIndex === null) { return "Value not found"; }
return Table.value(searchRowIndex, 2);
Hlookup
Look for a value in a given row, then return another value on the same column. The solution is the same as for index/match.
// Excel
\=HLOOKUP(B18, $A$1:$F$2, 2, FALSE)
// Fluidtable
// Use Values.indexOf(searchValue, valuesArray)
// in combination with Table.value(rowIndex, columnIndex, tableName)
const searchColumnIndex = Values.indexOf(100, Table.row(1));
if (searchColumnIndex === null) { return "Value not found"; }
return Table.value(2, searchColumnIndex);
Concatenate multiple values
Combine two or more values into one.
// Excel
\=CONCATENATE("Name: ", A1, " ", B1) // -> Name: Johnny Machete
// Fluidtable
// Use Javascript standard string concatenation
return "Name: "+ Table.value(0, 0) + " " + Table.value(0, 1)
const row = Table.row(0); return "Name: "+ row\[0\] +" "+ row\[1\]
// Use Javascript standard string template
return \`Name: ${Table.value(0, 0)} ${Table.value(0, 1)}\`
const row = Table.row(0); return \`Name: ${row\[0\]} ${row\[1\]}\`
Length of a string
Get the length of a string value.
// Excel
\=LEN(A1)
// Fluidtable
return Table.value(0, 0).length
const value = Table.value(0, 0); return value.length
Trim a string
Remove the white spaces from the beginning and the end of a string.
// Excel
\=TRIM(A1)
// Fluidtable
return Table.value(0, 0).trim()
const value = Table.value(0, 0); return value.trim()
OR: Is any condition met?
Check if any of the conditions are true.
// Excel
// True if A1 is bigger than A2 or B1 is bigger than 100
\=OR(A1 > A2, B1 > 100)
// Fluidtable
// Use Javascript standard || operator (double pipe)
return valueOne || valueTwo
return Table.value(0, 0) || Table.value(0, 3)
return Table.value(0, 0) > Table.value(0, 1) || Table.value(0,3)
AND: Are all conditions met?
Check if all the conditions are true.
// Excel
// True if A1 is bigger than A2 and B1 is bigger than 100
\=AND(A1 > A2, B1 > 100)
// Fluidtable
// Use Javascript standard && operator (double commercial e)
return valueOne && valueTwo
return Table.value(0, 0) && Table.value(0, 3)
return Table.value(0, 0) > Table.value(0, 1) && Table.value(0,3)
More to come
Please let us know which formulas would you like to have implemented in Fluidtable and added to this list. Leave a comment, or ask in the Community space.