Tag Info

Hot answers tagged

16

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.


7

=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

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

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

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


4

I bashed up a comparison sheet for the formula's 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 formula's: ADD ARRAYFORMULA CONCAT ...


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


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

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.


2

Ok - this isn't simple but it can be done. There may be a more elegant way but here's a way that will work, using only the terms you are providing. Take a range of cells as follows Col A B C 2 1 300 2 3 2 450 4 4 3 700 5 4 900 6 5 1000 7 6 1200 Take these formulas: Cell A10: ...


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

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


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

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


1

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


1

A formula like this: =vlookup(A1,Sheet2!A:B,2,0) copied down in your first sheet may serve.


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

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


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

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


1

Assuming you have "BANK" in I3:I20 (as opposed to "AMEX") for bank payments, then I believe you can achieve your goal by reversing your logic (equal to "BANK", rather than not equal to "AMEX")... Change the formula in F24 to: =arrayformula(sumif(H3:H20 & I3:I20,E24:E33 & "BANK",D3:D20)) This will then populate cells F24:F33 with the appropriate ...


1

According to http://productforums.google.com/d/msg/docs/X8DuFNN8GZI/5SpFysHvB4EJ there is no formula that can do this. If you happen to know something about the characteristics of the numbers in question, e.g. if you know that the sums are always greater than 45 and no individual number is, you could use that, e.g. SUMIF(1:8, ">45") However, in the same ...


1

error: The xPath query given could not be evaluated. Please check the syntax of the query: //a/@href The Error message says XPath could not be evaluated. Here the problem is: you need to provide / target with proper XPath to retrieve the value. Refer my sample XPath for this:- =importXml("http://www.toysrus.com", ...


1

Basically, you need to use a few formulas to accomplish this, like: UNIQUE FILTER SUM Walk through the spreadsheet I prepared and see the formula's for yourself: file I prepared with example



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