Tag Info

Hot answers tagged

18

If there's a difference between 50 and 050, what you have are strings (i.e. text). Changing the cell format to Plain Text via the Format>Numbers>Plain Text will keep leading zeros.


9

=SUMIF(D:D,"bob",C:C) in C column are numbers, in D column are ("bob" or others) if you wish do not sum all column you can use for example =SUMIF(D2:D6,"bob",C2:C6) Note: Text is case sensitive (Bob is not equal bob)


5

This will work, based on a table with upper-left corner in A1, modify to suit actual: =INDEX(A1:E7, MATCH("Course 3", A:A, 0), MATCH("Pablo", 1:1, 0))


5

I bashed up a comparison sheet for the formulas between MS Excel 2010 and Google Spreadsheet. Comparison was based on the names only. See snapshot of the current situation: If you want to make sure that Google Spreadsheet files are properly converted and still work in MS Excel 2010, then don't use the following formulas: ADD ARRAYFORMULA CONCAT CONTINUE ...


5

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).


4

Answered I received in the Google Support Forum: =counta(iferror(filter(B:B,A:A="name1",B:B="yes")))


4

Column A can be the recurring cost. Column B can be the number of months in the recurrence. Column C can divide Column A by Column B to get the monthly cost. Then you can sum column C. For your last.fm example: A2 = "9.00" B2 = "3" C2 = "=A2/B2" The your total monthly cost would be the sum of Column C.


4

There is no need to jump into GAS, standard spreadsheet functions handle this easily. Please paste the following into a cell in your example =iferror(filter($A$2:$A, match($A$2:$A, B2:$B , false)), "no match") The match function generates a cartesian product of the two ranges full of errors except where a match is found. It returns an index into the ...


3

This little script will compare two ranges, as a formula: function COMPARE(array1, array2) { var array = []; for(i=0; i<array1.length; i++) { for(j=0; j<array2.length; j++) { if(array1[i][0] == array2[j][0]) { // the extra square brackets will make it a 2D array, // aligning it vertically ...


3

Since @jam 's answer there might be some improvement in Spreadsheet's features. If you'd like to preserve and use the numbers as figures (eg. automatic fill) you might apply the settings from the Format > Number > More Formats > Custom number format... menu, and set the pattern to 000, where the number of zeros represent the number of digits to ...


3

I've been able to find a solution for my problem. Using =ARRAYFORMULA(Sheet1!A1:A) in the top of a column Google Docs will update the whole column, without the need for functions in each cell. Thus, deleted or inserted rows will not cause trouble anymore. I found this answer (by brettathds) at the Google Support forums.


3

When you find yourself writing a bunch of similar IF statement, you probably need FILTER. For example, =JOIN(CHAR(10), FILTER(A1:F1, NOT(ISBLANK(A1:F1)))) joins the content of cells A1 through F1, separating them with linebreaks — but the empty cells are skipped as they fail the condition NOT(ISBLANK(...)).


2

This will work. I've used the Named Ranges, Name and From. You don't have to use To at all, since your values are all contiguous. B2 is what I used as the value of the first BMI, you can just drag this formula down the column: =IFERROR(INDEX(Name, MATCH(B2,From),1),INDEX(Name,1,1))


2

Use the following formula to accomplish that: =SUMIF(range;">"&0) The range is of course the one you set it to be !! EDIT 29-03-2013; 23:00 CEST IMPORTANT: This is only valid if you work at least 70 hours per week, each week. It is just as important to know if you have worked less !! Therefore you need to change the way you calculate the ...


2

Figured it out... took me a while... INDIRECT() has to wrap the entire reference. In order to achieve what I needed, the formula looked like this: =INDIRECT("Sheet2!A"&A1)


2

Your calculation of "This time" / "Last time" expressed as a percentage (by the formatting) is essentially correct, except it isn't working out the increase. So if it was 10 minutes yesterday, and 10 minutes today, that means you did 100% of the time you did yesterday. This is what you are calculating now. So (Today / Yesterday) - 1 will give you the ...


2

GoogleTournament() doesn’t work anymore, womp. Mar 18 (2014?) I suspect the above is really just a different way of saying what help does: The behavior of GOOGLETOURNAMENT for the play-in round is broken for 2011 onwards, when the format changed. The behavior is still correct for historical tournaments prior to 2011. but as I doubt it can now be ...


2

My guess is your 1st one is the old Google Sheets and your 2nd one is new Google Sheets, where the Script gallery has been moved to Add-ons and renamed Add-on store. This is the answer to the question of which this has been deemed a duplicate: The Scripts Gallery menu item will not appear in new spreadsheets if you have opted in to the new Google ...


2

You need to specify a condition, not just a value, for the FILTER function. So: =FILTER(A2:A6, B2:B6 = MAX(B2:B6)) I have set up a demonstration spreadsheet for this, feel free to have a look or copy it.


2

Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, ...


2

Use double instead of single quotations marks i.e. =IF(C1="NULL",B1,C1) References IF - Google Editors Help


1

I know this is an old topic, but for the number of unique articles, this might work: =ArrayFormula(counta(unique(filter(A2:A&B2:B, len(A2:A&B2:B))))) I also tried to work out some formulas for the other questions: Hope this helps ?


1

A way of doing it is to make another column with =IF(A2=1,C2,"") A2 being the active flag and then do the `COUNTIF` on that column. You could also replace the A2=1 with a date test if you wanted.


1

I suggest replacing =DIVIDE(D2 ; D3) in E3 with =(D3-D2)/D2 and formatting ColumnE as percentage. This will give #DIV/0! errors in some cases because there is no meaningful percentage increase to be calculated where the base is zero. For your chart, select your data in ColumnD (includingD1), Insert, Chart, Charts, Line, click on the top image and Insert. ...


1

Do you mean output like this! Cell C1 shows correct averages. If yes, set the cell format to 24 hours and use the Average for (Numbers) For time Elapsed use simple subtraction = Cell B - Cell A


1

There is a MAX() function. =MAX(0,SUM(A1:A7)-70) It returns the highest value in a range of numbers. In this case, 0, or whatever the value of SUB(A1:A7) - 70 is. If the latter is negative, this returns a 0.


1

A quicker way of formatting a cell as text, is to add a preceding apostrophe to your number. In a cell, type '050 and it will be interpreted as the string 050. Another option is to write it as a formula: ="050"


1

I needed to have a leading zero on the current month of the year for creating a custom date code I use on my invoices. This is what I did: =IF(LEN(MONTH(TODAY()))=1;CONCATENATE(0,MONTH(TODAY()));MONTH(TODAY()))


1

Some of the calls are going into multiple cells. Try wrapping each importXML function inside noexpand().



Only top voted, non community-wiki answers of a minimum length are eligible