Excel Formulas to Fluidtable Reference Guide

Fluidtable is a modern spreadsheet application. Instead of just replicating Excel features, the goal is to think about the problem that…

clacla

April 28, 2019

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.

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.

Tugulab Blog.