yogi_Create A Pie Chart That Displays The Frequency With Which A Name Occurs In A Table

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user jacrvr said:
How do I create a chart that displays the frequency that data occurs in one and two columns?

I am a primary school teacher and I am still relatively new to google docs.  I would like to create a chart that displays the frequency that a student's name appears in a single column (i.e. column B).  When I type the range in the chart editor ('raw data'!B1:B107), I get the following message: "The required data format for the pie chart doesn't match the current data".

The strange thing is that this method has worked for me in a previous spreadsheet (the name of the main sheet is raw data in both cases) and I was able to generate a pie chart that gave a break down of the frequency a particular student's name appears in column B on a separate sheet.  When I scroll over the working pie chart, each piece belongs to one student and the number of times their name appears in column B is displayed along with a percentage associated with that name.  I am not sure why I can't reproduce the same results.  

I am very interested in learning how to chart the frequency a particular word or phrase appears in a single column and across multiple.  Any help or advice would be much appreciated.

------------------------------------------------------

following is a solution to the problem -- I have inserted a computed column shown in brown background to delineate the maximum frequency with which a name occurs in the table. The Pie Chart is then created for two columns B and G.

yogi_Pull Most Recent Records Based On Unique Values In A Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user lukeacaratan said:
use query function to pull most recent records based on unique column values.

I have a table (with data populated by a form) with three columns. Col A is the Timestamp, Col B is Location, Col C is a Unit Label.

I am trying to use the query function to pull certain records from a table. My goal is to create a report that only shows the most recent entry for each unique item listed in the Column C.

My first attempt (unsuccessfully) used a combination of Vlookups to pull the specific records. After doing some research, it seems like the query function might be a better fit for what i'm trying to accomploish.

After a couple days of trying on my own, gathering knowledge from the internet, in this forum and ones like it, I can't seem to input the correct SQL. I have made the spreadsheet public and shared it below in hopes that someone can help point me in the right direction. 

https://docs.google.com/spreadsheet/ccc?key=0AjejngD4Oi-vdG5SeUU4bUlJbWZ4X1BIRG9vZERJblE

Thanks you in advance for any help!

Luke

Chrome Browser on Mac Lion with Virtual XP machine, Desktop with XP or Windows 7

-----------------------------------------

following is a solution to the problem

yogi_Count Number Of Months For An Attribute From Different Sheets Using Only A Single Formula

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user puyan said

So if I have =SUM('Dec 2011'!D42;'Jan 2012'!D42 its the sum of December and January's profit. My problem is that if I have a client's sheet and it only contains a December sheet only, it comes up with an error because January sheet is not there. Is there a way of making the formula work (or a more efficient way) so that if the sheets that are not there it can ignore it and move on?

2. is trying to count the number of months that are active. This is also shown by a single cell which counts the days, so if the day's number is over 0 then I want it to be counted. Also, if there if the months are not shown is there a way of ignoring it?

So to clarify, as an example;

Bob's Spreadsheet (4 tabs), Jan 2011, Feb 2011, March 2011, and Summary Tab

Mike's Spreadsheet (5 tabs), Feb 2011, March 2011, April 2011, May 2011, and Summary Tab

In all Monthly Tabs - Monthly Profit, Days Active

In all Summary Tabs (I want) - Overall Monthly Profit, Months Active 

------------------------------------------------

following is a solution to the problem for count of Total Months Played

yogi_Expense Register With The Name Of Statement Period As A Variable

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user LMLML said:
get text from one cell into the formula of another cell
hello
I have a formula in my check ledger spreadsheet which looks for the last balance value on the previous sheet, and inputs it as a "previous balance" on the new sheet
the formula takes the name of the previous sheet as an input in 4 places, and I would like to be able just to enter the name in its own cell, and have the formula grab it 4 times, rather than me copy/pasting it in 4 times
the fomula looks like this:
=FILTER( 'nov-dec'!H4:H1000 ; ROW('nov-dec'!H4:H1000) =MAX( FILTER( ROW('nov-dec'!H4:H1000) ; NOT(ISBLANK('nov-dec'!H4:H1000)))))
so I would like to have 'nov-dec' in its own cell, and a variable in the above formula instead... is that possible??
thanks!!
LML
------------------------------
following is a solution to the problem

yogi_Sum Up Fractions And Present The Result In Fractional Notation

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Stars1234 said:
add inch fractions
I need to add standard length inch fractions based on the denominator.
for example: 1/2 + 1/8 = 5/8; 3/16 + 5/16 = 1/2
and make a formula to paste into columns
cell    data
A1     1/32
A2     =A1 + 1/32  ---answer would be 1/16
A3     =A2 + 1/32  ---answer would be 3/32
the answer needs to be in fractions, not decimals.
is there a way to do this?
--------------------------------------------------
in the following solution to the problem, I have generalized summing up of fractions and not limit it to adding inch fractions only.

yogi_Extract Data From Master Sheet By Specified Group

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Senna300 said:
How do I link data between sheets, but make it so I can rank on one sheet and not have it affect data shown in another?
For example, on sheet 1 I have a master list of people with their roles in the company and basic information. On sheets 2-5, I want to have lists of the specific people in each role only. Rather than enter all the information in multiple times, I use = to link the cells in sheets 2-5, to the master list in sheet 1. But when I add rows or sort the data in sheet 1, the data in sheets 2-5 changes.
Help?
---------------------------------------------
following is a solution to the problem

yogi_Sum Up Cells In Different Sheets of A Spreadsheet Using Only A Single Formula

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user puyan said

So if I have =SUM('Dec 2011'!D42;'Jan 2012'!D42 its the sum of December and January's profit. My problem is that if I have a client's sheet and it only contains a December sheet only, it comes up with an error because January sheet is not there. Is there a way of making the formula work (or a more efficient way) so that if the sheets that are not there it can ignore it and move on?

2. is trying to count the number of months that are active. This is also shown by a single cell which counts the days, so if the day's number is over 0 then I want it to be counted. Also, if there if the months are not shown is there a way of ignoring it?

So to clarify, as an example;

Bob's Spreadsheet (4 tabs), Jan 2011, Feb 2011, March 2011, and Summary Tab

Mike's Spreadsheet (5 tabs), Feb 2011, March 2011, April 2011, May 2011, and Summary Tab

In all Monthly Tabs - Monthly Profit, Days Active

In all Summary Tabs (I want) - Overall Monthly Profit, Months Active 

------------------------------------------------

following is a solution to the problem

yogi_Operate On Cells In Different Sheets Within A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
Merry Christmas!
user puyan said:
Countif/Sum cells on different pages
Hi guys, (and merry xmas lol)
I've made a template sheet in which money data is shown monthly with each sheet representing a month.I want to do a summary page in which it takes certain data from each sheet and then sum/counts them.
The 2 main ones I'm trying to do is;

1. Sum total profit from all months.

2. Count/Don't count months where months are active or not.

One of the biggest problems is that for different people they all don't necessarily have all the months added due to the length of their contracts with us.

So that using; (1)

=SUM('Dec 2011'!D42;'Jan 2012'!D42;'Feb 2012'!D42;'March 2012'!D42;'April 2012'!D42;'May 2012'!D42;'June 2012'!D42;'July 2012'!D42;'Aug 2012'!D42;'Sept 2012'!D42;'Oct 2012'!D42;'Nov 2012'!D42;'Dec 2012'!D42;)

is problematic because if the month sheet is not there, it doesn't ignore it but gives out an error message.

and using; (2)

=COUNTIF('Jan 2012'!D44,">0")+COUNTIF('Dec 2011'!D44,">0")+COUNTIF('Feb 2012'!D44,">0")+COUNTIF('March 2012'!D44,">0")+COUNTIF('April 2012'!D44,">0")++COUNTIF('May 2012'!D44,">0")+COUNTIF('June 2012'!D44,">0")+COUNTIF('July 2012'!D44,">0")+COUNTIF('Aug 2012'!D44,">0")+COUNTIF('Sept 2012'!D44,">0")+COUNTIF('Oct 2012'!D44,">0")+COUNTIF('Nov 2012'!D44,">0")+COUNTIF('Dec 2012'!D44,">0")

has the same problem in which if the month sheet is not for that particular client's spreadsheet, it will give out an error message.

Much help appreciated, thx!

---------------------------------------------------

following is a solution to the problem

yogi_Rearrange Data In A Multi-Column Table And Present As Sorted Into Two Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user rkarbowski said:
Formula Rubik's Cube: combining and re-arranging data
Hey all!
In my first range of data, I have a list of bloggers and the IDs of the campaigns they participate in:
BLOGGER | CP1 | CP2 | CP3
alice   | 1   | 3   | 5
beth    | 2   |     |
chris   | 1   | 2   | 4
dara    | 1   | 3   |
Essentially, I need a formula to arrange the above data like so:
CAMPAIGN | BLOGGER
1        | alice
1        | chris
1        | dara
2        | beth
2        | chris
3        | alice
4        | dara
5        | alice
I've seen some of the other posts on combining arrays, etc, and I couldn't quite apply it to what I'm trying to do here... so sorry if this question has been answered before :)
Thanks much,
---------------------------------
following is a solution to the problem:

yogi_Import From A Table With Data in Variable Position

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Orion77 said:
How to import from a table with data in variable position?
I want to import data from a table in a web page that updates daily. Some days there is no data and I just want to get the last valid data. The table is similar to this:
1/12/2011     25    -    -
30/11/2011    28    -    -
29/11/2011    28    -    -
28/11/2011    28    -    -
25/11/2011    28    -    -
24/11/2011    28    -    -
23/11/2011    39  4000  1560
22/11/2011    27    -    -
I am interested to get "23/11/2011" and "39" since those are the last valid data.
I use =ImportHtml(Concat("http://www.boerse-frankfurt.de/DE/index.aspx?pageID=125&ISIN=";"XS0171467854");"table";1)
to get the table, but I can not use INDEX(;;) since I do not know the position of the valid data. I have tried VLOOKUP and FILTER but I do not know how to use them in conjuntion with ImportHtml.
Any help would be appreciated.
Thank you!
----------------------------------
following is a solution to the problem

yogi_Replace Substrings In Rows of A Column With Other Substrings From A Lookup Table

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user flimzitree said
replace substring with substring
B2: =SUBSTITUTE( TRIM(A2) ; CHAR(34) ; "")
The SUBSTITUTE function lets me replace a substring with another string ... but what if I want to have 20 different substitutions to make to every one of hundreds of string cells in a speadsheet column?
e.g.
A1 = "sdvkcvhjghgfkjhgvjkhbljh"
I want to substitute every occurrence of 'adsfg' with 'kjhgk', every occurrence of 'bdfgh' with 'fgsdg' ... etecetera for 20 substitutions.
I could create a complex nested set of SUBSTITUTE functions, but is there a better way of doing this? Maybe one that looks up the substitution terms from another sheet in case I want to change them without changing all the nested equations?
------
But supposing instead of replacing single characters with null characters I wanted to look up a list of substrings to substitute with a corresponding list of strings. e.g. in the string '/dog//cat//snake//parrot/' I want to substitute every occurrence of '/dog/' with '/mammal/', every occurrence of '/snake/' with '/reptile/' etc. I would like these substitution rules to come from a table, since there may be dozens of 'rules' to apply, and the same rules to be applied to hundreds of cells in a column.
:) Dom
--------------------------------
following is a solution to the problem:

yogi_Replace A Substring With Another Substring (Not Quite)

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user flimzitree: said
replace substring with substring
B2: =SUBSTITUTE( TRIM(A2) ; CHAR(34) ; "")The SUBSTITUTE function lets me replace a substring with another string ... but what if I want to have 20 different substitutions to make to every one of hundreds of string cells in a speadsheet column?
e.g.
A1 = "sdvkcvhjghgfkjhgvjkhbljh"
I want  to substitute every occurrence of 'adsfg' with 'kjhgk', every occurrence of 'bdfgh' with 'fgsdg' ... etecetera for 20 substitutions.
I could create a complex nested set of SUBSTITUTE functions, but is there a better way of doing this? Maybe one that looks up the substitution terms from another sheet in case I want to change them without changing all the nested equations?
Hi Yogi, thanks for quick response.

I have created
https://docs.google.com/spreadsheet/ccc?key=0Asd7WkVXqKmddGFmU1RUOUdxT0kwQTRQZWpDWkJxeGc
which is an example of the task that I am seeking to solve. In this example I want to take all the 'Sample names' in the 'Sample data' sheet and replace with a null character any occurrence of any of the single characters within the following double quotes: "abcdefghijklmnopqrstuvwxyz, '-".

This example spreadsheet achieves this by using formulae such as.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2 ; "a" ; "") ; "b" ; "") ; "c" ; "") ; "d" ; "") ; "e" ; "") ; "f" ; "") ; "g" ; "") ; "h" ; "") ; "i" ; "") ; "j" ; "") ; "k" ; "") ; "l" ; "") ; "m" ; "") ; "n" ; "") ; "o" ; "") ; "p" ; "") ; "q" ; "") ; "r" ; "") ; "s" ; "") ; "t" ; "") ; "u" ; "") ; "v" ; "") ; "w" ; "") ; "x" ; "") ; "y" ; "") ; "z" ; "") ; "," ; "") ; " " ; "") ; "'" ; "") ; "-" ; "")
Not pretty. So I am seeking a better way of achieving this. fyi the formula is generated by the 2nd sheet of the spreadsheet. This particular example yields the initials of the 'sample names' if these are all consistently capitalised. I would really love a more general solution to this problem, e.g. to be able to have two columns in a sheet: 'text to find' and 'text to substitute with', and then a way to get all rows in that sheet to be used to do effect the substitutions. In the sample names initials example, the 'text to substitute with' would all be blank cells, but in other situations I have wanted this same capability but with substitution of non-null strings.
Any help would be appreciated, thanks.
-----------------------------------------------

if I have understood it correctly, in my following solution to the problem I used RegexReplace function in a single array formula:

yogi_Count Number Of CheckBoxes Selected in Google Form

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Febin11 said:
Count number of check boxes selected in form
Ive created a form where the names of persons sharing a cost are selected i need to know how to count the number of people selected. in the spreadsheet the cell shows names with commas. 
------------------------------------------

in the following solution to the problem, I have names from checkboxes (in the Form, separated by commas,populated in column C of the Form Responses sheet. Then I inserted a computed column D where in I used an array formula to make the needed computation.

yogi_Count Cells In A Range That Are Hyperlinked

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user hurricane_ck said:
Count cells that use a specific function
I want to count how many cells in a column have a hyperlink in them, done using the HYPERLINK() function. I tried doing COUNTIF on both "HYPERLINK" and "http", but it doesn't count them. So, is there a way to count text that is part of a function?
----------------------------------

in the following solution to the problem I have generalized from a column to a range wherein I count cells that begin with www. or http or mailto:

yogi_Put Computed results In Appropriate Brown And Green Colored Cells Using Cross Reference Type Checks

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user grebarton said:
Using cross reference type checks to return data in appropriate cells
I am trying to use a spreadsheet to return various parts of information.

For example we work in steel and know that:

Part 1 needs to be made up of 100mm of Steel A, 200mm of Steel B and 2x50mm of steel C,

Part 2 needs to be made up of 200mm of Steel A, 200mm of Steel B and 30mm of Steel D.

I have used a basic VLOOKUP function to return results however have only got the result back where if we input in cell A2 Part 1 it will return the different steel types, with lengths and quantities in a suitable column.

I cannot seem to plan out the best way of doing this.

For example if it would be best have have a list of the different Steel types across the columns (allowing room for lengths and quantities) and the Parts on the rows However I am still unsure how to return the results.

Type Part    Qty            Steel A   Steel B   Steel C

Part 1          10              200 10    200 10    50 20                              

Part 2          10              200 10    200  10   30 10                                          

The actual data to link to will be stored elsewhere within the worksheet.

However we would also want a further basic function built in showing if we wanted 20 x Part 1 in total it would actually take this into account when telling us how many of Steel A to cut to 200mm.

link to user's spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0AtDIWOWnpWmudDlUOTJ6a3ZwR1gtQkJkRmxfdHgtSlE

--------------------------------------------------------------------------

following is a solution to the problem:

yogi_Put Computed results In Appropriate Brown Colored Cells Using Cross Reference Type Checks

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user grebarton said:
Using cross reference type checks to return data in appropriate cells
I am trying to use a spreadsheet to return various parts of information.

For example we work in steel and know that:

Part 1 needs to be made up of 100mm of Steel A, 200mm of Steel B and 2x50mm of steel C,

Part 2 needs to be made up of 200mm of Steel A, 200mm of Steel B and 30mm of Steel D.

I have used a basic VLOOKUP function to return results however have only got the result back where if we input in cell A2 Part 1 it will return the different steel types, with lengths and quantities in a suitable column.

I cannot seem to plan out the best way of doing this.

For example if it would be best have have a list of the different Steel types across the columns (allowing room for lengths and quantities) and the Parts on the rows However I am still unsure how to return the results.

Type Part    Qty            Steel A   Steel B   Steel C

Part 1          10              200 10    200 10    50 20                              

Part 2          10              200 10    200  10   30 10                                          

The actual data to link to will be stored elsewhere within the worksheet.

However we would also want a further basic function built in showing if we wanted 20 x Part 1 in total it would actually take this into account when telling us how many of Steel A to cut to 200mm.

link to user's spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0AtDIWOWnpWmudDlUOTJ6a3ZwR1gtQkJkRmxfdHgtSlE

--------------------------------------------------------------------------

following is a solution to the problem:

yogi_Convert Rows Of Many-to-One Data Into Rows Of One-to-One Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user mtm4319 said:
How do I un-bunch several rows of many-to-one data into rows of one-to-one data?
Here is a spreadsheet illustrating my problem: https://docs.google.com/spreadsheet/ccc?key=0AhyIjyraG-QSdDVJTF9uSEUwZTJ5N3lUQVpDTF9TalE&hl=en_US#gid=0I want the data from columns A and B to turn into the data in columns G and H using formulas.I'm already two steps of the way there, by joining the rows of data in column A, then splitting and transposing it using the ", " delimiters. But the issue is column B. Does anyone know how I could get there?
Thanks!
-------------------------------------
following is a solution to a more generalized problem ... wherein I have used duplicate names with different attributes

yogi_Summarize Stats From Data Across A Number Of Sheets In A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user JamieC said:
Create a summary worksheet from data across a dynamic number of worksheets
I'm tracking stats for my sports team. Using 1 sheet for each game, the players are listed in rows and game stat categories are the columns....along with some totaling being done in the columns.
I'd like to create a Season Summary worksheet. For instance, the summary may show that "Bob Smith" has scored 22 goals over 10 games.
The number of games for the season is NOT static...sometimes we pickup games. For this reason...it is a huge hassle to update formulas that SUM or Count based on the cell value in a'Sheet name' referenced across multiple worksheets.
So I wonder.... is there a way in the Summary Worksheet to pull data from all worksheets (say...those named Game1, Game2, Game3m etc) dynamically? Perhaps a formula on the Summary Sheet that performs a specific "search" across all those 'GAMEx' sheets and returns the SUMd value (where the data being SUMd is 1 cell to te right of the search criterial?
Any help would be much appreciated.
--------------------------------------------------
following is a solution to the problem:

yogi_Query Form Responses Sheet To Extract Records Considering Time Of Submittal

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user MF_Elm said:
How would I then modify this:
=ArrayFormula(query(
ImportRange("spreadsheet1_key","Sheet2!A:I"),"select Col2,Col3,Col4,Col5,Col6 where Col9=1"))
to first filter only those entries that are less than 24 hours, and then again (on a separate sheet) those entries that are older than 24 hours while still checking for the 1 in col9?
Thanks again.
---------------------------
following is a solution to the problem

yogi_Handle Multiple Dependency Lists And Computations

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Loki said:
I've been reading a couple of your posts on the above subject, and looking at the 'screenshot' embedded example(s):
http://yogi--anand-consulting.blogspot.com/2011/11/yogicreate-dependent-dropdown-list-of.html
http://yogi--anand-consulting.blogspot.com/2011/10/yogicreate-dependent-list-of-items.html
It's very interesting (I've never even used named ranges), and looks like it would solve my problem - but I'm not sure how to implement it from what I've read there.
I'm probably just being stupid. Would you mind looking at my spreadsheet? I've made it public:
https://docs.google.com/spreadsheet/ccc?key=0AkDJZNlbZvaCdEgtbXBjWVFYcXB1WFhvZi1WNThlTmc&hl=en_GB#gid=0
I want to have CATEGORIES on the COCKTAILS sheet which then affect subcategories, i.e. the available INGREDIENTS.
And I'd also like both data validated dropdown boxes here to be populated from the full details on the INGREDIENTS sheet.
Is this achievable using the method you posted? If so, help implementing it would be amazing! If not, any ideas?
------------------------------------------------
following is a solution to the problem:

yogi_Compute Class Attendance Stats For Given Layout

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
I have used here part of a class attendance roster used by a teacher 
it is a rather unusual layout 
and the teacher wants hours attended and associated percentage over a period of specified weeks
-----------------------------------
following is a solution to the problem