google sheets
I have a situation that needs to be handled. I have values in a column that are values of that particular instance in time I need the difference between each instant.
Input and output will be something like this
12760 12760 (Subtract with 0)
15464 2704 (15464 - 12760)
17162 1698
19808 2646
22757 2949
25450 2693
How would I achieve this with SpreadSheet? Is it even possible?
With the following formula you can accomplish that.
=QUERY(DATA!A1:B4, "SELECT A, B ORDER BY B DESC LIMIT 3")
The QUERY formula selects the two columns and orders them descendingly according to column B. Then it limits the result to three rows (top 3).
data
result
I've created an example file for you: Top 3 results
If the names of sheets were known, you'd be able to do something like
={filter(Sheet1!C2:C, len(Sheet1!C2:C)); filter(Sheet2!C2:C, len(Sheet2!C2:C)); ... }
to join all of those together.
But since new Sheets will be added, and you don't know what names they will have, you need a script. I can think of two approaches: (a) the script will collect the data from C-columns itself; (b) the script will update the formula (of the above form) that will do the rest. The second one seems easier to code so I'll go with that.
function updateFormula(e) {
if (e.changeType === 'INSERT_GRID' || e.changeType === 'REMOVE_GRID') {
var sheets = e.source.getSheets();
var formulaBits = [];
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() !== '🌴') {
var quotedName = "'"+sheets[i].getSheetName()+"'";
formulaBits.push('iferror(filter('+quotedName+'!C2:C, len('+quotedName+'!C2:C)))');
}
}
var formula = '={'+formulaBits.join('; ')+'}';
e.source.getSheetByName('🌴').getRange('C1').setFormula(formula);
}
}
To deploy:
You may want to add a sheet yourself to make the script run the first time; after that it will update the formula in cell C1 of sheet 🌴 whenever a new sheet is added or a sheet is removed.
In case of renamed sheets, the software takes care of renaming itself, adjusting the formula automatically.
Best Answer
It's not entirely clear what you're after, but I think I have the idea.
You have this data:
And you want column B to show the difference between the value in column A and the value in column A in the row above. That's really quite simple.
In B2 put the formula:
Then you can autofill that down the rest of column B as far as you need.
For the first row, since there's an implied zero, just put in B1
In the end, you'll have:
(If this isn't what you're after, then please edit your question to provide further clarity.)