yogi_Find 1st 2nd 3rd ... Largest Non-Unique And Unique Numbers In Range A2 to F2

                                          Google Spreadsheet   Post  #948

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 31, 2012
user Ron01 said:(http://productforums.google.com/forum/?zx=7swwh6ge4uwz#!category-topic/docs/spreadsheets/HMfOCW2JO7M)
How can i find the 2nd largest number in an array?
Hi,

I know that i can use, lets say:

=MAX(A2:F2)

to find the the biggest number in the array.

Is there a function, or any other good way to find the 2nd biggest number in that array?

Thanks.

Ron.

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

following is a solution to a bit more generalized problem

in addition to a further question by user Ron01 in regard to including corresponding cell references, I have added Sheet2 and Sheet3 wherein the enhanced solution includes cell references as well.

yogi_Facilitate A Student To Enter StudentID Via Form And Read The Score in A Published Spreadsheet

                                          Google Spreadsheet   Post  #947

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 30, 2012
user AndrewHill (thread) and Uwe Waldmann said:(https://productforums.google.com/forum/#!mydiscussions/docs/D5ekeKRi9PI)
Hi Yogi!
I've just come accross this thread as I was having the same problem like Andrew. Your solution sounds good, but I'm not sure if I got it right. Could you maybe describe the two steps a bit more precisely?
That would be great!
Cheers,
Uwe
---
Thanks for providing me more details. I have actually already tried to follow your recommendations. To make it a bit easier, I have created a form which is similar to mine (but in English):https://docs.google.com/spreadsheet/ccc?key=0Ag80lLrCqkKrdEpBbHpRSk1iclhiNjNkdkM4N29UeHc#gid=0

1) Is it possible to embed a "dynamic spreadsheet" (one in which the participant can still insert the ID) into a website? I tried to embed my spreadhseet but was only able to embed a "static" (one in which nothing can be changed).

2) In this form you will also see a problem, I have posted under another thread (https://productforums.google.com/forum/#!msg/docs/KQkISccbH2k/9RdX9g_E_OoJ) This might also have affected Andrew. After I have seen what you posted under other threads, you might also be able to give an advice on that.

3) For some reason the vlookup-function in sheet2 also didn't work, it did in my original sheet...

Final question for my interest: Do you think it's easier to give feedback via e-mail? Are there possibilities to write nice e-mails (design) via scripts or is that pretty complicated? 

Thank you already for your help! You are doing a great job!
Uwe
---
So far I have been able to answer some of my questions myself by checking out some forums and Yogi's blog.

The solution to point no. 2 is an arrayformula like this: =arrayformula(if(C2:C="agree";3;if(C2:C="partly agree";2;if(C2:C="disagree";1;0)))
I have been able to claculate means with a formula like this: =arrayformula(if(row(A:B)="test";0;(H2:H+J2:J)/2))    (I still wonder if there isn't an easier way)
The vlookup-function finally also works: =vlookup($C$4,Sheet1!$B$2:$O$100,11,FALSE)

There are two things which still don't work: 
1) the calculation of standard deviations with an arrayformula (no idea how a solution coluld look like)
2) I still wonder if it's possible to embed a "dynamic" sheet in a website in which the user can enter his ID but change nothing else...

Would be great to get some help on that.
-------------------------------------------------------------------------------------------------------
Uwe has got a whole slew of questions ... he has got his whole project wrapped in here ... normally one can only address a specific technical question via Google Docs help forum, but let me give it a shot

this is the sheet where the student student data is logged in ... then I have computed columns to the right shown with light brown colored background ... this is the main sheet from which the student score would be drawn and published ... this sheet is the work-horse but this will not need to be published.

here is the Form via which a student will s8ubmit his/her ID: once a student has submitted her/his ID, the student can then see her/his score via the following

yogi_In Summay Sheet SumUp Values For Entities in Row 1 Of Sheets In Column A

                                          Google Spreadsheet   Post  #946

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 30, 2012
user Nick Wohlfarth said:(http://productforums.google.com/forum/?zx=y1lexgi8u6aw#!mydiscussions/docs/FEU4kEg-318)
Enter a Sheet Name into a Function from a Text Cell 

Using this Function on Summary Sheet  =SUMIF('agh north'!A:A;"NESE";'agh north'!B:B)

The Function Searches Column A for NESE then Summarize Data from B on Summary Sheet.

The Sheetnames are n Column A of Summary Sheet

Need a Function to Enter SheetName pull name from Column A into Formula.

This Has Been Successful Where A3 is Sheetname > =INDIRECT("'" & A3 & "'!" & C3)

Here is Link to Sample Sheet Help File

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

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

following is a solution to the problem

yogi_Split Strings in Column A To Numbers Street_Addresses And Names Row By Row As Specified

                                          Google Spreadsheet   Post  #945

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 29, 2012
user googledocsfan said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/cKhOoPp_8DM)
Is there a formula to separate contents of one cell? Please help! :) An example is listed.
Hi fellow google docs users!

I am currently working on massive data entry.  Column A is data I already have.  I am trying to achieve the automatic results of Column B and C.

Is there a formula I can place in Column B to achieve the results of B and C?  The Last Name in column A (currently Smith) will always be the same.

Is there a formula that would split the contents of Column A after the word "Smith" passes?

                  A                                               B                                           C

1   Amber Smith 111 First St                  Amber Smith                       111 First St

2   John Smith 15 Cupcake St                John Smith                           15 Cupcake St      

3   Jane Smith 1556 Paradise Rd            Jane Smith                           1556 Paradise Rd

If this is not possible, I have hours of work to complete.  :P

All of your thoughts and suggestions are greatly appreciated!!!!!!!!!!

---

When you can, could you please look at this shared file?

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

I am trying to produce the results as displayed inside this file.  Please feel free to work within this file.

Your assistance is greatly appreciated as always.

Aloha!

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

following is a solution to the problem

yogi_Change Multiple Sets Of Data In Column A To Multiple Rows Beginning With Row 1 Skipping Unneeded Rows

                                          Google Spreadsheet   Post  #944

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 27, 2012
user hshaikh said:(http://productforums.google.com/forum/?zx=7e91x0d3naeu#!category-topic/docs/spreadsheets/xIfao0gKDEc)
Google spreadsheet: how to change multiple sets of data in one column to multiple rows

I have 5 rows of data in one column on google spreadsheets that I would like to be displayed in one row.  I have many sets of data like this which are separated by 3 rows.  I want each data set to be displayed across a row (5 columns).   Is there a formula I can use in order to do that over multiple data sets?  I tried transpose function but was unable to loop it for other data sets.

for example, I have one column as follows:

Name

Description

location

rating

review

- 1st row (blank)

- 2nd row (blank)

- 3rd row (blank)

Name2

Description2

location2

rating2

review2

- 1st row (blank)

- 2nd row (blank)

- 3rd row (blank)

Name3

...etc.

This is how I would like this data to be displayed (multiple rows with 5 columns of data):

name description location rating review

name2 description2 location2 rating2 review2

...etc for multiple sets of data

Would love any input as I have been working on this for hours and can't figure it out.

Thanks!

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

following is a solution to the problem

yogi_Combine The Results Of Six Queries In Sheet Comparer Into A Single Function in Sheet yogi_Comparer

                                          Google Spreadsheet   Post  #943

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 26, 2012
user Gilles-Japon said:(http://productforums.google.com/forum/?zx=potzyfn0cfbg#!category-topic/docs/spreadsheets/5EzIJBtCPik)
Join queries
Hello,

I need to have the results of several queries to appear in a continuous column if possible. I have now 6 independent queries one under the other one. The reason to keep them one under the other, is that the column data are the same for most (ID number, Last name, First name, Spouse name) or can become so (ID Spouse ---> ID, IDchild1 ---> ID, Spouse First Name ---> First Name, Child1 first name ---> First name etc).

One of the problem is that I cannot know how many lines to keep free between queries to allow for the data to be listed without erasing the queries below. 

Here are the various queries :

First query :

=query('Sur MailChimp'!A:AB;"select G, C, E where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains G ")

Second query :

=query('Sur MailChimp'!A:AB;"select N, L, M where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains N ")

Third query :

=query('Sur MailChimp'!A:AB;"select S, C, R where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains S ")

Fourth query :

=query('Sur MailChimp'!A:AB;"select V, C, U where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains V ")

Fifth query :

=query('Sur MailChimp'!A:AB;"select Y, C, X where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains Y ")

Sixth query :

=query('Sur MailChimp'!A:AB;"select AB, C, AA where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains AB ")

The sample sheet can be found at : https://docs.google.com/spreadsheet/ccc?key=0AkGTeIoz4AcxdDdDZjdjdWtfaU96eHEwaGx0SEFSVkE

Any idea on how to proceed ?

Thank you in advance for all your help.

Gilles

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

following is a solution using VMERGE custom function written by ahab  -- available in script gallery

yogi_SetUp Formula For Computing Values For Being Early OnTime or Late

                                          Google Spreadsheet   Post  #942

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 26, 2012
user moses004 said:(http://productforums.google.com/forum/?zx=potzyfn0cfbg#!category-topic/docs/spreadsheets/M6_LewvcuD4)
Can you tell me what's wrong with this formula?
I'm using a Dell laptop, Chrome browser.

Here's the formula:

=IF(C2="Early",100,IF(C2="On time",75,IF(C2="Late",50)))

The only part of it that works is the first option for 100. I have a validation list in C2: Easy, On time, Late. When I choose Early, I get 100 in the correct cell. I get nothing when I select "On Time" or "Late."

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

Thank you.

Joe Moses

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

the problem was that some entries in user's dataValidation list included a leading space character ... so one way would have been to remove those leading unwanted space characters ... however, since this is a common error that is bound to happen, a passive approach is to account for this in the associated formula using the TRIM function

I have also included in the solution a single arrayformula for an array of values

yogi_Pull Google Finance Value For Specified Entrity And Attribute For Latest Market Open Date On Or Prior To Specified Date

                                          Google Spreadsheet   Post  #941

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 26, 2012
user EuDave said:()

Issue while getting historical data through Google finance 

I use google spreadsheet on a google chrome browser, and use the google finance function to get the stock symbol's historical data. Since last week, GoogleFinance("NIFTY","High",12/22/2012) is returning the high of the Symbol from 23rd Dec till today, where as It was returning the high from 22nd(mentioned date) to today's date previously.

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

following is a solution to the problem

yogi_SetUp A Comparison Calculator Where Comparison Is Made For Items From A DropDown List Based On dataSheet

                                          Google Spreadsheet   Post  #940

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 26, 2012
user donald5000 said:(http://productforums.google.com/forum/?zx=potzyfn0cfbg#!category-topic/docs/spreadsheets/ZmTpkq8kaiM)
Creating a calculator?
Does anyone know how or if I can create a calculator (two drop down lists) with a decision being provided after two items are selected (and a "calculate" button is clicked)?

So essentially, I have this spreadsheet here (Shared):

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

What I'm wanting to do:

1. Have that above spreadsheet and the values populate two drop down lists

2. Both drop down lists use the same data, so essentially you could choose the same item on each list and the calculator would determine both items are equal

3. Have some sort of end result displayed after both items are selected in each list

4. A calculate button would be nice, but if the calculation happens automatically after the second drop down item is selected, I'm fine with that

5. Even if the spreadsheet just shows the value and doesn't give any sort of direction, I'm fine with that too (meaning, once both drop downs are selected it just gives the number/rating for each next to the drop down box

As you can see, I'm not set on any one single way to do this, any way will work, I just would like my spreadsheet to drive this calculator, so maybe tab 2 has the calculator and tab 1 has the data like i have it? 

Thanks for the help!!!!!

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

following is a solution to the problem

yogi_Compare Lists In Two Different Sheets And Extract Non_Matching Values From Field A D F Of Sheet ilot484-11-2012

                                          Google Spreadsheet   Post  #939

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 25, 2012
this is an adjunct to the solution in my following blog post

yogi_Compare Lists In Two Different Sheets And Extract in Third Sheet Specified Field Values For Unique Records Only

http://yogi--anand-consulting.blogspot.com/2012/12/yogicompare-lists-in-two-different.html

which was in response to a question by user Gilles-Japon in the following thread in Google Docs Help forum:

http://productforums.google.com/forum/?zx=xqfo7ic9pzt2#!mydiscussions/docs/_4dT8rzAKG8

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

yogi_Compare Lists In Two Different Sheets And Extract in Third Sheet Specified Field Values For Unique Records Only

                                          Google Spreadsheet   Post  #938

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 25, 2012
user Gilles-Japon said:(http://productforums.google.com/forum/?zx=cmk5k2pk0bbf#!category-topic/docs/spreadsheets/_4dT8rzAKG8)
Compare information from two sheets
Hello,

I have two sheets with information arranged differently. 

The first sheet that is sent to me regularly shows one person's information per line, including a unique ID.

The second sheet takes the same information (including unique ID's) and is arranged by family units per row, placing spouses and children in separate columns.

I want to be able to identify quickly the changes made, whenever a new updated sheet is sent to me. It could be that people have been removed from the first sheet, or people have been added.

I therefore need a double check with the results on a third sheet.

First check, looking at column A from the first sheet (one unique ID per row) for the ID and searching in several columns in sheet 2 for their matching field.

The second one, looking at several columns in sheet 2 for the unique ID's and comparing it to the first sheet's column A (where the unique ID are stored).

I have made a query, based on Yogi's blog (super resource) to match the first condition (look into sheet 1 for each unique ID and compare to 6 different columns in sheet 2), but fail to make the second query properly.

Here is the query I could make : 

=query(index('Ilot484-11-2012'!A2:A&"");"select Col1 where not '"&join(";";'Sur MailChimp'!G2:G)&join(";";'Sur MailChimp'!N2:N)&join(";";'Sur MailChimp'!S2:S)&join(";";'Sur MailChimp'!V2:V)&join(";";'Sur MailChimp'!Y2:Y)&join(";";'Sur MailChimp'!AB2:AB)&"' contains Col1 label Col1 'Numics dans Ilot484-11-2012 qui ne sont pas dans Sur MailChimp' ")

The sample sheet can be found at : https://docs.google.com/spreadsheet/ccc?key=0AkGTeIoz4AcxdDdDZjdjdWtfaU96eHEwaGx0SEFSVkE

Any idea on how to proceed ?

Thank you in advance for all your help.

Gilles

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

following is a solution to the problem