google sheetsworksheet-function
I have a Google Spreadsheet in which I am comparing projectors. Basically it has formulas to divide the bulb life/cost and contrast ratio/cost. Then it adds the total of the two and gives me a number. Using the max function I can determine which is the largest number in the set, but I would like to know if there is any formula to select the column header at the top based on the number.
If the highest number is under an Epson 2000. I want a formula to make it say Epson 2000.
Simply copy your formula from that cell to each cell below. Copying formulas with relative references (those without $) adjusts them automatically.
I think you have your coordinates backwards, though. Cell C2 contains "1000" and cell B3 contains "50", so your formula in C3 should be =MINUS(C2,B3) (or =C2-B3).
The following little piece of code gives you the result, without prior knowledge about the character code number.
function mySplit(range, delimiter) {
var output = [], maxCol = range[0].length;
for(var i=0, iLen=range.length; i<iLen; i++) {
var colCounter=0, counter=0;
for(var j=0, jLen=range[0].length; j<jLen; j++) {
if(range[i][j] == "") {
colCounter++;
} else {
if(range[i][j].split(delimiter).length > 1) {
counter++;
}
}
}
output.push(counter + "/" + (maxCol - colCounter));
}
return output;
}
I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.
Best Answer
You can do this with a combination of the
INDIRECTandMATCHfunctions.In the last column of your table add the following formula in line with the data that you would like to determine the max of:
Google Spreadsheet
Excel
Where
[Name_Row]is the index of the row where your projector names are (e.g. 1 if the projector names are in row 1),[Data_Span]is the span of values that you're trying to find the max of, and[Column_Offset]is the distance of your first data value from column A (e.g. 1 if your data starts in column B).See picture
.