This is a series on what Google Spreadsheets can do that Excel can’t To get a list of unique values from a list, use the UNIQUE function on Google Spreadsheets. For example, if you have a list of browsers in column A, type =UNIQUE(A1:A17) at cell B1 to get a unique list of browsers. This is a dynamic list. If you change the list of browsers, the unique list gets updated automatically. ...
Many languages have functions to process lists (array). These functions usually return a list, so you can pass that to another list function. This chaining of functions is really powerful. UNIX provides this sort of chaining capability. If I had a cities (with some repetitions) and I wanted to find out how many started with the letter ‘A’, I’d just type: cat cities | sort | uniq | grep "^A" | wc cat: types the cities. sort: sorts the cities alphabetically. uniq: finds unique cities (works only if sorted - that's why we had to sort the list). grep: filters the cities. Only allows cities beginning with A. wc: word count To do this on Excel, the only way is to ...
Sometimes you want to check the colour of a cell, or whether a cell is bold. This can be easily done with user-defined functions (UDFs). (To create a UDF, press Alt-F11, Alt-I-M, and type the code below.) You can use ISBOLD(cell) to check if a cell is bold, and BGCOLOR(cell) to get the background colour of the cell. This lets you selectively process bold or shaded cells. The two examples below show how you can add only the cells in bold, or only the shaded cells. ...
A few tips, without getting into the details. Array formulas let you perform any calculation on an entire list, and get the result in a single formula. Database functions like DSUM can manipulate tables of data very flexibly. Using these and Pivot tables, you can do anything with any tabular data. Comments Ananth 22 Dec 2005 6:32 am: I needed your feedback on www.doondo.org. Give it a spin let me know. I wanted to avoid positng here but never got any replies for the mails sent at root_node at yahoo.com Ananth 22 Dec 2005 6:33 am: www.doondo.org is a hobby project I’ve been working on. The beta is just out. Ananth 22 Dec 2005 6:35 am: Looking forward for your views and feedback ritzkini 22 Dec 2005 10:01 am: great concept ananth..shld be a hit considering the great response to a similar funda used in IIM-I’s Klueless S Anand 22 Dec 2005 12:31 pm: I liked the concept and interface, Ananth. Will mail you in detail. Quick question… how do I find out what the right keywords are? ritzkini 23 Dec 2005 2:19 am: hehehe…hey anand,saw the visitor stats thing for the first time today ! awesomely cool ,kudos! u did it yourself ?? Dhar 23 Dec 2005 3:26 am: Ananth, does your game support advanced Google operators? Ananth 23 Dec 2005 4:11 am: Thanks for feedback folks. doondo needs additional features, like game summary with right keywords. Right now only basic operators are supported. Even such a simple concept requires complex coding skills! I would love if you can post the suggestion at [email protected]. Great if you can join the group. Anand wouldn’t like spamming his comments section with doondo. S Anand 24 Dec 2005 1:42 am: Yeah ritzkini, spent a week coding the visitor stats. What else do you think would be useful to see? ritzkini 26 Dec 2005 7:22 am: :) i dont think it could have been any more comprehensive than it already is ! :) harish 11 Jan 2006 3:50 am: great work mate…