Tag Info

Hot answers tagged

13

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


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

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


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

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

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


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

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


1

Use worksheet function OFFSET. Suppose your values are in column B (starting from row 2) and that your start and end values are in range C2:C3. Then you have to input: =sum(offset(B1;C2;0;C3-C2+1;1)).


1

There's a few ways of doing that, here's an easy one: =SUM(INDIRECT(ʺ$D$2:$D$ʺ & A1))



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