yogi_Compute Overall Team Scores From A Table Of Player And Individual Team Scores

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #550    May 22, 2012     www.energyefficientbuild.com.

user Chris Hardcastle said:
Variable team scores
Hi,

I've got a spreadsheet set up to gather scores for a golf weekend. Currently I'm adding team scores up with simple formulas (e.g. =SUM(B1:B3)) but what I'd like to do is have scores add up dependent on team names/numbers.

I've got an example of something working here:

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

If you change the team number (between 1 and 3 for this) the totals update

This is doing what I want it to do but I'm wondering if there's a cleaner way to do it?

Thanks if you can help.

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

a clean way of doing this is by using the QUERY function

yogi_Query A Range Of Values And Present the Results In A Comma Separated String

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #549    May 21, 2012     www.energyefficientbuild.com.


user Phattiem said:
query concatenation question
The question is pretty simple. I am running a query to return all columns that contain a matched value. However, instead of returning each hit in a separate column, is there a way to have the output concatenated and separated with a comma?

Example of my query function:

=(query(B2:C5,"select C where B = '" & D2 &"'"))

But this returns 2 columns.

However,

=(concatenate(query(B2:C5,"select C where B = '" & D2 &"'"))) 

Concatenates into the first column (can't figure out how to get a comma separator) but STILL creates and fills a second column.

Any help would be appreciated,

Mark

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

(by the way in regard to your statement  that your formula
creates and fills a second column
your formula using the QUERY function will output multiple rows, but only one column)

following is my proposed solution using the JOIN function

yogi_Choose Answer Based On Multiple Set of Choices

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #548    May 20, 2012     www.energyefficientbuild.com.

user Bronxand said:
Another IF Qiestion.
So i'm trying to get a box to show a value in cell (B10) depending on what number is punched entered into a cell (F5)

F5= Autopay, B10= .15

30, .05

60, .10

90, .18

150, .25

240, .30

and 0 for any other value

I've been working with this formula

=IF(F5="Autopay", .15,IF(F5="30", .05,IF(F5="60", .10,IF(F5="90", .18,IF(F5="150", .25,IF(F5="240", .30, 0)))))) 

So when i punch in Autopay into F5, the correct value of .15 shows up in B10, but if i plug in 30, 60, 90, 150, or 240, I get zero. 

I noticed that what ever is the last number of the formula, then that's the value that comes up when 30, 60, 90, 150, 240 is punched in. 

not to sure where to go from here, any suggestions?

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

in the following I present couple of different solution to the problems

yogi_Compute Grades By Dropping Specified Number Of Lowest Grades And Present Results Per Spec

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #548    May 20, 2012     www.energyefficientbuild.com.

user Cackoa said:
Problem with spreedsheet formula
Hi

I know that there is some people who can do miracles with spreedsheets formulas. I have problem with this "algorithm":

https://docs.google.com/spreadsheet/ccc?key=0Alc63T7mXdf7dGc2encxa29GRGo4YURHUTBnbFkwTlE#gid=0 

this is sample sheet. I have sheet with exam marks. I want to have some marks which not be counted to the final grade. This marks should be the worst from all (of this person).

For example: mark 5 (where max is 10) is better than mark 7 (where max is 20).

Can you help me write formula to SUM and % od possible points? Feel free to edit this form. I will don't delete this -> it will be example for other people.

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

following is a solution to the problem

yogi_Compute Products Ordered By Name For Data In Table

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #547    May 18, 2012     www.energyefficientbuild.com.

user dcfitjar said:
Counting number of occurrences of various text entries 
Hi!
I am working on a research project where I have collected a lot of names in a spreadsheet, along with information like nationality, gender, religious affiliation etc. All of these are entered into different coloumns. Is there a way for me to get statistics out of this - like a counter that shows number of people from the USA or how many males or females there are in the group?
Thanks,
Daniel
-----
 I cannot provide my data as they contain confidential information, but I can try to explain what I need using your picture, here: https://lh3.googleusercontent.com/hnSu8CbECCbsTQIspYQo10EcJs5gjF8_0oyZbl_JQZJ9UwbQ79al6qqQBkTgzmECkFsdt4RIVTuQsSnTN1nKVPLY8RHJnHnqr6ts_QlZQrcsZQdBO5
If I used this, I would like something that collects data from the "product" coloumn, and presents it like this:
Pencil: 16
Stand: 8
Pen: 4
Desk: 1
Then, If I add a new row, with say Stand, it changes the number to 9 (because now there are nine occurrences). 
And here comes the twist: If I add an entirely new category, say "Ruler", I want that to show up at the bottom automatically, so the new list, with my to additions, would show:
Pencil: 16
Stand: 9
Pen: 4
Desk: 1
Ruler: 1
Is this possible?
Thanks,
Daniel
-------------------------------------------------------------------------------------------

in the following I have simulated data from part of your table

yogi_Convert Specified Currency To US Dollar Using www.oanda.com

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #546    May 18, 2012     www.energyefficientbuild.com.

user Pete Cutter said:
Single date historical currency exchange rate from OANDA
I'm looking for a spreadsheet function to bring a single historical exchange rate for a particular currency into a single spreadsheet cell.

I've tried some of the more user-friendly strings such as (apologies for my cell references--I've checked and these are all valid):

• Grandtrunk (=importData("http://currencies.apps.grandtrunk.net/getrate/"&text(I39,"yyyy-mm-dd")&"/"&I41&"/"&I42); and
• Google Finance (=Index(googlefinance("currency:myrusd","high",E31),2,2); and
• Yahoo Finance (=index(importData("http://finance.yahoo.com/d/quotes.csv?s=KHRUSD=X&f=a"),1,1)

But have run into problems because:

• Grandtrunk and Google Finance don't support one of the currencies I need: Laos Kip (LAK) (!?); and
• Yahoo Finance doesn't seem to have a mechanism to call historical rates

I have thus used the following function to call up data from OANDA:

• =index(importhtml("http://www.oanda.com/currency/table?date=I39&date_fmt=us&exch=USD&sel_list=LAK&value=1&format=CSV&redirected=1","table",2),2,1)

However, using this, I end up getting all 8 values from the resulting table (Currency,Code,USD/1 Unit,Units/1 USD, Lao Kip,LAK,0.0001269,8163.46) all in one cell.

I've tried a =right([cell ref],7) function to isolate just the last number, but it turns out that the length of this number differs for different currencies so I can't use it dependably.

I've also tried:

• =index(importHTML("http://www.oanda.com/currency/historical-rates-classic?date_fmt=normal&date="&CELL-WITH-START-DATE-IN-DD/MM/YY&"&date1="CELL-WITH-END-DATE-IN-DD/MM/YY&"&exch="&CELL-WITH-FROM-RATE-CODE&"&exch2="&CELL-WITH-FROM-RATE-CODE&"&expr="&CELL-WITH-TO-RATE-CODE&"&expr2="&CELL-WITH-TO-RATE-CODE&"&margin_fixed=0&format=ASCII&redirected=1","table",3),1,2)

...as posted here: http://productforums.google.com/d/topic/docs/cO8lVyBpwyE/discussion

...but get a parse error every time.

Can anyone suggest a way to get just one isolated number from OANDA representing an exchange rate for a given day?

Any thoughts/techniques greatly appreciated. Specific function string examples are even more appreciated.

Thanks!!!

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

I have requested Pete Cutter to share his spreadsheet with me so I can see how his data is laid out and specifically what is housed in cell I39 and some other cells ... in the mean time following is my convoluted solution

yogi_Compute Average Rating Provided In Columns D F H and J by Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #545    May 17, 2012     www.energyefficientbuild.com.

user RonEarl said:
Average 4 columns per entry row
I'm setting up a spreadsheet to rate story submission entries for an anthology I'm producing. Each submission is on its own row and each editor has two columns. One to mark story as read and one to rate story 0 - 9. I want to apply a formula to a ratings column that averages current row for columns D,F,H,J. I can do it on individual lines, but I would like it to auto apply to each row in the column, so I or one of the editors can add new stories without creating a new formula for each row.
Thanks.
Ron
------------------------------------------------------------------------------------------------
following is a solution to the problem