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


5

Solved with: =INDIRECT( ADDRESS( 4, COLUMN() ) ) ADDRESS gives me a string like "$A$1" and INDIRECT makes it a cell reference.


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

There are two things that you need to take into consideration. Consideration The IMPORTRANGE function requires two parameters (strings): spreadsheet key data range The data range needs to correspond to the number of columns you're about to use. In your case, you selected only column B and column C. Therefore, going beyond col2 will end up in failure. ...


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

Try Shift+Ctrl+: and Ctrl+; Those are the keyboard shortcuts for "insert time" and "insert date" respectively. (based on pnuts' comment)


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

Try =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where Col18='"&E3&"' and Col19='Installation Booked' ")


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


2

Once you are running code in the Sheet, you can loop through the column to see if the values you care about are set or not. You can then only send the email if your criteria are met. Keep in mind that if your logic is if (empty > 20) you will get an email on entry 20, 21, 22, etc. You may want to send one every 5 entries over 20 or something like that.


1

Say create a table with H in G1, S in G2, 1984 in H1 and 672 in H2. In D1: =C1/vlookup(B1,G:H,2,0) and copy down should suit.


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



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