yogi_Pick A Set Of Random IDs As Percentage Of IDs Assigned To A Set Of Names

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

user statq said:
I want a set of random ids picked against a subset of names, and this random sample should be percentage of the subset.
I want to get a list of random IDs corresponding to Alex, Adam, Steve. This list of random Ids should be 20% of the total number of Id under Alex.
Example : Alex has a total of 6 IDs that he has worked on, now I want a random sample of 20% of that 6 which is around 1, if he is worked on 100 IDs then I want the query to pick up a random sample of 20(20% of 100) IDs that Alex has worked on and 20% of IDs that Adam has worked on and so on.
Right now the following query works to get a list of ransom samples under say Adam alone.
=ARRAYFORMULA(SORT( A2:B7 ; RANDBETWEEN( SIGN(ROW(B2:B7)) ; 1E293 ) ; 1 ))
https://docs.google.com/spreadsheet/ccc?pli=1&key=0AlqKm4fWq06OdGpPVnVmNk1iNEpmRktaMHdCcGtlTGc#gid=0
Wanted some suggestions and help. Thanks
-------------------------------------------------------------
following is a solution to the problem

yogi_Extract Name And Address Rows With Unique Addresses Only

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


user Cullen MI said:
Filter unique rows, but include a row that may not be unique
My main sheet is simply a list of names and addresses. I want to end up with a sheet of unique addresses to use as mailing labels.
However, a lot of the entries are members of the same family at the same address. I might have:
  A     B           C             D      E    F
Joe   Smith  123 Main Street  New York  NY  10010
Jane  Smith  123 Main Street  New York  NY 10010
I would like to keep one of those addresses, and just select one of the names to use.
In my second sheet I can do Unique(A:F). But this will return both of those lines, since they are not entirely duplicates.
I could also do Unique:(C:F), which will return just one copy of the address, but then I lose the name.
How can I get one address, with one name?
---------------------------------------------------------
following is a solution to the problem 

yogi_Develop Formulas For Fraction Rounding

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

user theDante said:
formula errors in google docs but not excel
My fraction rounding formula works in excel, but not in google docs.
=IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),16)=0),TEXT(R2,"#"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),8)=0),TEXT(R2,"# 0/2"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),4)=0),TEXT(R2,"# 0/4"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),2)=0),TEXT(R2,"# 0/8"),TEXT(R2,"# 0/16")))))
It’s supposed to round numbers to the nearest 1/16th, but it returns “error: Unknown format text: # 0/16”
Does anyone have any ideas why google docs doesn't understand?
-----------------------------------------
following is a solution to the best of my understanding what user theDante is intending to do ... I have however generalized the formula byond just the 1/16 rounding

yogi_Count The Number Of Specified Entities Laid Out In Indefinite Number Of Columns In A Sheet

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

user JohnT1979 said:
Counting occurences of a words on a spreadsheet
On one tab I have a series of rows and colums that have a bunch of names of different companies. A lot of them are duplicated throughout the sheet. Here is an example: https://docs.google.com/spreadsheet/ccc?key=0At9MZ6gkPp_EdFEydk52aGNqaU1vRnhXY0FVaXBWenc#gid=0
What I'd like to do on a separate tab fina way to automatically list each Company and how many times it appears. Is there a way to automatically populate that list with the number of occurences so it would look like:
Company A 4
Company B 2
Company C 1
etc
Is there a formula or two I can use to do this?
Any insight would be greatly appreciated.
Thanks!
-------------------------------------------------------
following is a solution to the problem -- the number of companies are listed in columns of Sheet1 ... and the count of the companies is computed in Sheet2

yogi_Count Number of Cells That Meet A Creiterion And Cells In Corresponding Rows Of Columns Are Blank

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #447          www.energyefficientbuild.com
user nooff said:
How to count number of cells in a column, that are below a specific date, AND that neighbouring cells are not blank
Hi
I have the following columns
B C D E
I need to count the number of cells in column C, where C is less than or equal to a specific date, AND cells B D and E should be empty at the same time. Also, the cell C should not be empty, because if it is, than it will automatically be less than or equal to the date. This is what i have come up with:
=SUM(if(if($C$12:$C$400<=$AY12;1;0)+if(ISBLANK($B$12:$B$400); 1; 0)+if(ISBLANK($D$12:$D$400);1;0)+if(ISBLANK($E$12:$E$400);1;0)++if(NOT(ISBLANK($C$12:$C$400));1;0)=5;1;0)) 
So, i check: 
If C is less than or equal to the date = +1 
If B is blank = +1 
If D is blank +1 If E is blank +1 AND, 
if C is not blank: +1 
The total should be: 5, and it should sum up it all up. I have a date column, and i drag this function downards the 30'th of June. The date changes for each row. There is only 1 row that meets this criterium, where the date is 15/02/2012 When i get to this date, it shows 1, but when i get to the 16 and after, it shows 0 again, where it should be showing 1. 
What am i doing wrong?
 -------------------------------------------------
following is a solution based on my best understanding of what user nooff is trying to do ... I have use a smaller range of B12:D24, and I have used the date for comparison in cell A12

yogi_Merge Data From Multiple Columns in Multiple Sheets Into A Summary Sheet

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

user ween said:
how to concatenate range of sheet
Here is my spreadsheet
https://docs.google.com/spreadsheet/ccc?key=0Atv06cGw9P0fdGVkcEk1Tk1kaU13WVBGdFpLNkNGZUE
I d like to concatenate spreadsheet (complete or just a range) qs in my exemple for exemple in a certain page i start at row N°7; on another in A1
I don't want to change the order of the sheet but just to copy and paste the results.
I've tried a Yogia formula which is very good just for one column; the following (=ArrayFormula(transpose(split(concatenate(indirect("SH1!A10:A10"&counta(SH1!A10:A10))&char(9),indirect("SH2!A10:A10"&counta(SH1!A10:A10))&char(9),indirect("SH3!A10:A10"&counta(SH3!A10:A10))&char(9),indirect("'RERANGE SH4'!A10:A10"&counta('RERANGE SH4'!A10:A10))&char(9)),char(9))))
But I need not to copy only one column but a range in a certain sheet and another ranger in another sheet but keep in as it was and not mix it!
Please Help
A virtual french desert for the one who find the solution; at least the receipe hihihi
regards
--------------------------------------------------
following is a solution to the problem ... using custom written function VMERGE

yogi_Enter Format And Do Calcs With Time That Includes Fractions Of Seconds

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

user ScullerGuy said:
TIme Format Issues
In building spreadsheets the compare times for sports, I need a time format that includes tenths of a second. As in 2:31.5 for 2 minutes, 31.5 seconds. Google Docs does not like this, and converts it to text. It is difficult to perform math functions (like division...) on text fields. How about adding this time format?
update:
Specifically, I want to list times and splits for each rower, and be able to do such things as add the times, average them, divide them, etc. I can do this in an Excel sheet, but as soon as I upload the sheet it rounds the time to the nearest whole second. If I attempt to enter a time such as 2:31.4 into a google docs spreadsheet, it is converted to text. No math allowed with text...
Here is an example of what I am looking to do:
Name 2000 meter Time 500 meter split
John 6:20.4 1:35.1
The 500 meter split is simply the 2000 meter time divided by 4. The issue is that "6:20.4" is viewed as text. If it was in an excel spreadsheet uploaded, the 6:20.4 would be rounded down to 6:20.0.
---------------------------------------------
following is a solution to the problem

here is a picture of the sheet with Custom added in the MenuBar ...

yogi_Compute Weighted Average For An Attribute Given Values Over Several Set Of Numbers

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

user mikem0123-bt said:
Hi,
I have a business with a product in which the margin changes each time I order it. It's important for us to get an accurate average for the margins and the process we are using now, creating a new line in the spreadsheet for each new margin, is time consuming and I know there can be a better way.
Here is an example of a product and it's margins over a month:
Product X:
December 1-6: .52 (or 52% margin)
December 7-18: .54
December 19-31: .59
The quick-and-dirty route would be to take the three and just average them, but I end up with an inaccurate number because each margin is weighted differently based on how many days it's being used.
Any help or suggestions will be greatly appreciated!
Thanks!
--------------------------------------------
following is a solution to the problem:

yogi_Set Up A Form For Top N Attributes With Each In Its Own Column

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

user SurveyQuest said:
Creating subtext boxes within a text box in a Survey Form
I know one can use "Forms" to create a survey and link the response to a spreadsheet. I need to create a survey where the user fills in 3 or 5 unique ideas (responses) for the one questions which I can then categorize separately in the spreadsheet. For example, Question: List your top five (5) movies. Currently, I can only have the user number their entries as 1., 2., 3., etc, but since the box is a "text" or "paragraph text" everything goes into one column in the associated spreadsheet. Is there a way to get around this limitation.
--------------------------------------------
following is an idea for solution to the problem

here is the so called Form Responses sheet -- each idea has been logged in its own column

and here is how the Form was set up to obtain responses as showin the Form Responses sheet:

yogi_Merge Data In A Column From Different Sheets By Appending Consecutively

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

user SimonNY said:
How to consecutively add INDEX
How can I pull column data from multiple columns? The current formula I have merges the data together, but I want it to be consecutively added instead.
=INDEX(INDEX(Sheet1!A:A)&INDEX(Sheet2!A:A))
Thanks!
-------------------------------------------------
following is a solution to the problem where I have appended the data from column A of Sheet1, and then to that appended data from column A of Sheet2.

yogi_Publish An Image Inserted Into A Cell Of A Sheet

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

yogi_Concatenate Grouped Attributes For A Specified Column

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

user davesmithdub said:
How do I do a kind of pivot and concatenate
Hi
I've prepared a spreadsheet to show what the situation is: https://docs.google.com/spreadsheet/ccc?key=0AiRLtUqKvuCbdFYtU2I4M3ZEelBXM0RxQTJGS0VKOEE
.. and here's an explanation: The above link has a Source Array with two columns: Names and Dates.
What I want is to have an output where there are two columns: Dates, which contains unique dates and Names, which contains concatenated names (because there can be more than one name per date)
I looked into doing this with something like =Query(A:B, "select B pivot A") but I'm hitting a wall with a missing aggregation function.
all the best
Dave
------------------------------------------
following is a solution to the problem

yogi_Lookup Max Value Of An Attribute In Another Sheet

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

user ELM98 said:
How To Lookup A MAX Value In Another Sheet
I am working on a spreadsheet that keeps tracks of loans. On the first sheet is a list of the loans with information about who the loan is too, the loan amount, the payment amount, etc... The second tab keeps track of payments to loans and it has three columns, the loan that the payment is for, the date of the payment, and the payment amount.
On the first sheet I would like to add a column that shows the last payment date for each loan, but I am struggling with how to do this. The DMAX function looks like what I want, but I am struggling with what to pass in for the third parameter (the criteria array). On all the examples I see they have a group of cells setup for the criteria. The group of cells for the criteria how two rows, the first row has the name of the cells that they want to filter on, and the second row has the value that they want to filter on. However I do not want to setup a separate set of criteria cells for each loan. I want to just be able to have it get the loan name from the row it is on, and use that to lookup the last payment date for that loan. What is the best way to do this?
Thank You
--------------------------------------------
following is a solution to the problem

yogi_Setup A Leaderboard That Automatically Updates As More Stats Are Added

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


user JToy43 said:
Formula for Sports Statistics?
i'm trying to create a leader board for hockey stats that will automatically update when stats are changed, but i have no idea what formula to use. i need it to list the top 3 in each category (names and corresponding value) and to only include cells with values entered. help? picture is where everything is located
----------------------------------------------
following is a solution to the problem

yogi_Setup To AutoFill Two Rows Of Data In Another Sheet From One Source Row

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

user dcdva said:
Need help with auto-filling two rows of data from one source row
Hi everyone,
I'm sure I'm missing something obvious here.
I'm looking for an auto-fill function that will let me copy down two-rows of data at a time without incrementing by two. The source data is contained on one row (Entry Tab) but needs to be displayed on two rows (Pretty Tab). Manually entered the second set of data on Pretty Tab so you can see what I'm trying to accomplish.
https://docs.google.com/spreadsheet/pub?key=0AlJa1o__trcidGJBbk96aW5NeVhub2VOSkMwY3JackE&output=html
Thanks in advance.
---------------------------------------------------
following is a solution to the problem

yogi_Extract Approvers Names In Book Second From A List In Book First

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

user Farhaan100 said:
Match the data
Hi All,
I had a rather silly query but I'm sure wizards like the ones in this forum will be able to help me out.
I have certain codes in a Excel Book, lets name it "Book First", in cell A1 and their respective Approvers in B1.
I have another Excel Book, lets name it "Book Second" which contains lot of codes in which there will be codes apart from what I have in Book First.
I want the name of the approvers with a single formula that can match my codes in Book Second with that of Book First and give me the approvers name.
Regards,
---------------------------------------------
following is a solution to the problem

yogi_Custom Formatting Numeric Entries -- Including Dates Using Google Apps Script Via MenuBar

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

yogi_Sort A List Except For Certain Specified Things In Another Sheet

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

user DjDurland said:
How to sort a list except for certain things
Alright, I've come to another stump. I have 2 "boxes", both 8 cells from top to bottom. In the first "box", I have this:
CUBS
KITTENS
MINHUTEMEN
WINTER
CHEWS
PATHFINDERS
LAHC: MINORS
SHAOLIN
In the second "box", I have this:
WINTER
PATHFINDERS
What I'm trying to figure out, is how to get the rest of the items from Box 1 to Box 2, based on top to Bottom, and place them under the items in Box 2. In the end, Box 2 would look like this:
WINTER
PATHFINDERS
CUBS
KITTENS
MINHUTEMEN
CHEWS
LAHC: MINORS
SHAOLIN
How would I go about doing this? Does it also help to say that the Boxes are on separate sheets within the same worksheet?
-------------------------------------
following is a solution to the problem

yogi_Align Columns Of Data Using One Comment Field

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


user Wes.vasher said:
Align columns of data usine one comment field?
I have a master list of all 1000 customers with a login column, email column and other columns such as address, phone etc.
I have another subset of this list with around 500 customers with a login column and one other column of data.
I have them both in the same spreadsheet and want to combine the list using the common login column, that is, align the correct rows of the 500 list to the 1000 list.
I've tried everything I can find without success and am turning here for help. Any help would be greatly appreciated!
Align columns of data "using" one "common" field that is. I've added a link to a sample spreadsheet below.
https://docs.google.com/spreadsheet/ccc?key=0Aigg8OpP5H89dGc4Q3BNanFIMUppaGpWVWxPaGlUNnc
------------------------------------------
following is a solution to the problem:

yogi_Check user mwatanabe's Interactive Chart

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

here is the code as mwatanbe presented in Google Docs spreadsheets Help forum ... post
https://groups.google.com/a/googleproductforums.com/forum/#!category-topic/docs/spreadsheets/Kvr-IhJkIF8  and this does not display the Chart
here is the script for the interactive chart without the opening angle bracket before script, and without the closing angle bracket after chart.js"
script type="text
/javascript" src="//ajax.googleapis.com/ajax/static/modules/gviz/1.0/chart.js" {"containerId":"chart_1","dataSourceUrl":"//docs.google.com/spreadsheet/tq?key=0At3gqtlT0iJwdDZLVm1MTHBicGpMRjJYM1EwTy1Ib2c&transpose=0&headers=1&range=A1%3AB11&gid=0&pub=1","options":{"series":{"0":{"color":"#ff0000"}},"title":"Rainfall totals (Feb. 15, 2012)","booleanRole":"certainty","height":371,"animation":{"duration":500},"width":600,"vAxis":{"format":""},"useFirstColumnAsDomain":true,"hAxis":{"title":"Rainfall in inches","viewWindowMode":"pretty","format":"","viewWindow":{}},"isStacked":false},"state":{},"view":["{\"columns\":[0,1]}","{\"columns\":[0,1]}","{\"columns\":[0,1]}"],"chartType":"BarChart","chartName":"Chart 1"}
/script
and finally without the opening and closing angle brackets for /script


following is the script as I modified it ... and this one does display the Chart
here is the script for the interactive chart without the opening angle bracket before script, and without the closing angle bracket after javascript"
script src="//ajax.googleapis.com/ajax/static/modules/gviz/1.0/chart.js"
type="text/javascript"
{"dataSourceUrl":"//docs.google.com/spreadsheet/tq?key=0At3gqtlT0iJwdDZLVm1MTHBicGpMRjJYM1EwTy1Ib2c&transpose=0&headers=1&range=A1%3AB11&gid=0&pub=1","options":{"series":{"0":{"color":"#ff0000"}},"title":"Rainfall totals (Feb. 15, 2012)","booleanRole":"certainty","height":371,"animation":{"duration":500},"width":600,"vAxis":{"format":""},"useFirstColumnAsDomain":true,"hAxis":{"title":"Rainfall in inches","viewWindowMode":"pretty","format":"","viewWindow":{}},"isStacked":false},"state":{},"view":["{\"columns\":[0,1]}","{\"columns\":[0,1]}","{\"columns\":[0,1]}"],"chartType":"BarChart","chartName":"Chart 1"}
/script
and finally without the opening and closing angle brackets for /script

yogi_Compute Average Amount For Each Budget Category

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

user imac13 said:
Average IF
I have a spreadsheet that contains a budget category in column B, and a corresponding dollar value in column C. There are multiple entries for each budget category and I want to create a formula to average the values in column C corresponding to each budget category in column B. I have tried many variations of the formula to no avail. My most recent attempt is as follows.
=AVERAGE( FILTER( C:C ; B:B="Rent" ))
Regards
-----------------------------------------------
since the average is to be computed for each budget category, I suggest using the QUERY finction
following is a solution to the problem

yogi_Change Text To Vertical Orientation With Letters Turned On Their Side

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


user BrianatMenlo said:
Change text to vertical instead of horizontal
Is there any way to change cells to orient text vertically instead of horizontally?
update:
Thanks for the help. It would visually be easier to read if each letter were turned on it's side instead of each letter being presented horizontally.
But hey, beggars can't be choosers.
Thanks again for the formula.
------------------------------------------------
following is a solution to the problem

yogi_Compute Needed Sum And Count For Cells That Meet Multiple Criteria On Different Columns

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

user Ern said:
How to count the cells that meet the multiple criterion on different columns?
thank you very much yogia!
it worked!
if you don't mind i would like to ask another question,
Name Task Completed (Total) Estimated Time To(Hr) (Total) Complete Actual Time To Complete(Hr)
Andy    2  ?  ?
Billy   2  ?  ?
Charlie 0  ?  ?
Donnie  0  ?  ?
Ellie   0  ?  ?
==============================
Task    AssignedTo    TaskStatus    EstimatedTime    ActualTime
Task 1     Andy          Done             8               7
Task 2    Billy          Done             4               4
Task 3    Billy          Done             6               8
Task 4     Andy          Done             1               1
Task 5     Andy         Pending           1
i would like to ask how i'll count the total number of estimated hours of all the task assigned to "andy" and marked "done", and put it in (Total) Estimated Time To(Hr)
for example, andy has a (Total) Estimated Time To(Hr) = 10 and (Total) Complete Actual Time To Complete(Hr) = 9
thank you in advance :D
-------------------------------

following is a solution to the problem

yogi_Mark Items in LIST1 That Are In LIST2

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

user jveix33 said:
Compare Column B to Column A, Highlight Duplicates
Hello,
I am working on two different databases with some overlapping data. Currently I have two columns, Column A has a list of everything, and Column B contains a list of things I've already added from Column A. So basically I was wondering if anyone could help me find a way to compare column B to column A and highlight the duplicates in column A therefore I can see clearly what I still have to add. The Problem I've been having is that each column has a different number of items and this was giving me trouble developing a formula.
Thanks.
John
------------------------------------------
following is a solution to the problem

yogi_Color DeadLine Notification Dates In An Adjacent Column

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

user qbender said:
Deadline Notification (dates in spreadsheets)
In spreadsheets, is there anyway to set a notification (of any kind) on dates entered in a cell? For example, I've entered 02/15/2012 in a cell and I want to be notified when that date passes. I'm managing the submissions of 50-odd writers and I need an easy way to keep track of their deadlines without having to rewrite everything in a calendar.
------------------------------------
following is a solution to the problem

yogi_Compute Age In Years Months and Days As Of A Specified Date From Date Born

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

user dummy56 said:
I need to create a formula in a speadsheet that every time I put in a birth date, for example 9/19/1945, If I put in today's date I wil be able to get the age of the item. For example if I put in 9/19/1945 and then put in today's date 2/15/2012, I should be able to get something like 67 years and get the amount of months.
------------------------------------------
following is a solution to the problem:

yogi_Sum Top N Numbers And Highlight The Top N Numbers

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

user Squarky:
I was impressed by your last answer so I have one final questions regarding this gymnastics spreadsheet. On each sheet I have the following formula:
=SUM(LARGE($F$2:$F$8,1),LARGE($F$2:$F$8,2),LARGE($F$2:$F$8,3))
For each event it takes the 3 highest scores from the team in a column and averages them. Is there any way in google docs to change the background color (highlight) those 3 highest score cells?
-------------------------------------
following is a solution to the problem:

Copy of yogi_Get Count Of Various CATEGORY Items Housed In One Column_1

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

user sadistiko said:
How to count until some specific cell?
This looks simple but I cant figure out how to get it work.
I have column with some strings in it.
column is separated in 7 parts, every part starts with name of day.
Example:
A
MONDAY
Marko vs Darko
Darko vs Stjepan
Marko vs Stjepan
TUESDAY
Darko vs Alen
Darko vs Petar
...
I know I can do it ba COUNT but I would like some automatisation because those strings are added very often.
Maybe to do it by some formula that will stop counting if it gets to next day name, but what it could be I can't figure it out.
Sorry for bad english and thanks in advance
---------------------------------------
following is a solution to the problem

yogi_Apply Filter Function Using Multiple Criteria

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


user ween said:
how to USE multi FILTER?
Hi buddies,
I'd like to have a result for a FILTER formula as in my example in:
https://groups.google.com/a/googleproductforums.com/forum/#!category-topic/docs/spreadsheets/JBs0ZGeADok
I'd like to sort result from a TABLE with severals conditions so that the resultat show me only the lines that match with several criteras
I try to use function Filter but it seems that this function doesn't work. It this possible or not ?
Please try to check if i'm just dreaming or if it's possible ??
regards buddies
Ween
--------------------------------------
following is a solution to the problem

yogi_Compute Bonus Points For Name(s) If Specified Criteria Are Met

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

user foxdog175 said:
if/then question
Please take a look at the attached screenshot in:
https://groups.google.com/a/googleproductforums.com/forum/#!category-topic/docs/spreadsheets/bVJEcI4aFng
What formula would I need if I were to give a bonus of 1 point for every time a person turned in their work on the day it was sent out? Rows 2 and 3 in the screenshot would get the bonus. I'm thinking it would be a formula I'd have in D1 and would behave like this:
If date received = date sent and the name is "John", count one. It'd just be a counter if those 2 statements are true. Can anyone please help me out with the syntax?
--------------------------------------------
following is a solution to the problem wherein I have extended it to computing Bonus Points for more than 1 name, and then all the names in the list

yogi_Create Column Headings Involving Weekly Mix of Dates And Months

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

user farhaan100 said:
Date Ranges
I want the heading with fixed intervals for a period of 6 months..
8-14 Feb 15-22 Feb and so on...
If I am using the AND Function, it is adding the 2 dates and giving the output in nos and not the data as I want... and also since I want the date ranges for 6 months, it is too mechanical to type these manually..
Can any one help me out.. ?
-------------------------------------
following is a solution to the problem

yogi_Pull Abbreviations From SpreadsheetA And Present Them As Terms Based On A Lookup Table

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

user1294 said:
How to replace multiple strings in imported data range??
Hello!
I have 2 spreadsheets: one with raw data table and the other empty.
raw data has abbreviations (Abs, AVRG, TTL etc.) which I need to be full readable words (absolute, average, total etc.) in the second (empty) spreadsheet.
I'm using importrange function
maybe I should use script to create custom function??
please help, I'm a newbie))
-------------------------------------
following is a solution to the problem

yogi_Sum Up And Display Expenses By Type

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

user surfjabroni said:
Formula for expenses, count, countif?
Hi folks I am a total newbie to this so excuse my lack of knowledge:
I have the expenses name listed in column A (ie comcast phone, Radisson etc..) their type in Column B (ie communications, mileage, hotel etc...) and the cost of each item in column C.
I am looking for a formula that looks for all items that are "communications" and sums the total. This needs to be repeated for expenditure type.
Thanks in advance. I hope I made myself clear :)
--------------------------------------
following is a solution to the problem

yogi_Recreating Form For Entity Evaluation Program For Select Answers

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

user ms.sudderth said:
I am trying to recreate these forms that teachers created and now I want to email the completed recreated forms.
---------------------------------------
following is a solution to the problem based on simulated data as in your spreadsheet. In this solution I chose to do the following:

1) not display (withhold) the information regarding Attendance
    so the response to that question will be blank

2) in regard to Contact Info ... I provided a default answer:
    on file

yogi_Pull Data For When Bills Are Due Between Biweekly Pay Periods

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

use Xenopule said:
How can I pull data based on day of the month
I'm working on putting my monthly budget into Docs, and I want to do something to this effect:
•Page 1: An overview of what bills are due this pay period (every two weeks)
•Page 2: A list of each bill, how much is due, and the day of the month they're due on
I want to be able to pull the appropriate data from Page 2 to Page 1 given the two weeks between paychecks.
Optimally I'd like it to automatically adjust the two weeks (payday is the 9th, let's say, and it automatically pulls the dates between the 9th and 22nd without me changing any data), but I'm willing to have a dedicated cell that I punch in a new date and it calculates from there (or even two cells to calculate between those two days).
I'll happily expand on any confusion!
--------------------------------
following is a solution to the problem:

yogi_Extract Details Of The Most Recent Game Played From Data In Different Spreadsheets

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

user jkelley said:
Show only the most recent date in a spreasheet
Ok here is something kind of crazy. I have multiple different sheets that contain scores for sports games in the following format.
Date, @/vs, Opponent, Score
The first 3 columns are pre-filled at the beginning of the season and the score field updated as we go. The different sheets are all embedded in a webpages for public use. Now what I want to do is have the latest score show up in a separate sheet combining all the sheets together. For instance when I update the score today the "summary page" shows the new score I just added and the latest scores from the other sports.
Any thoughts?
Thanks
--------------------------------
following is a solution to the problem ... where I have merged the data from different spreadsheets and then queried the data for the most recent game actually played

yogi_Present Statistical Data In Column Chart And Bar Chart

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

user hihi said:
how can i make a pivot table? I have to make a graph with how many male, female, and both sexes had different types of leukemia in 1997 and 1999.
The data is shown in:
https://docs.google.com/viewer?a=v&pid=forums&srcid=MDIyMTM4Mzk3OTYzNDIwOTIzNTEBMDYyMTc3NTk2NDAxODE1MzEzNzcBMTM4ODk0NDcuMjU1MS4xMzI4NDc4OTQ3MDAxLkphdmFNYWlsLmdlby1kaXNjdXNzaW9uLWZvcnVtc0B2YnljMjIBNAFnb29nbGVwcm9kdWN0Zm9ydW1zLmNvbQ
-----------------------------------------
following is a solutionto the problem ...
the data is chartable as presented -- I have created a Column Chart associated with tabular data in Sheet1, and a Bar Chart associated with tabular data in Sheet2

yogi_Set Up Time Sheet For Specified Month And Year For Multiple Entries Per Day

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user dooc said:
Help with ARRAYFORMULA
Hi,
If someone could help it would be much appreciated. English is not my primary language but i will try to explain as best as i can.
I have found this formula:
=ARRAYFORMULA(IFERROR(MID( TEXT( VALUE( F8 &"-"& F7&"-"&ROW(A13:A33)-12); "yyEEE");3;3)))
basically, i enter month and year (2 for February and 2012), in cells F7 and F8 and it automatically populates from row A13 to row A46 with names of days for that month.
That's my biggest problem, is it possible to change this formula so it is not populating rows continuously ? I need to have name of days for example in cells A13, A17, A21..... .
-----------------
I suggested the OP try the foolowing formula:
=ArrayFormula(if(mod(row(A13:A46),4)=1,IFERROR(MID( TEXT( VALUE( F8 &"-"& F7&"-"&ROW(A13:A46)-12); "yyEEE");3;3)),""))
However the goal posts seem to have been changed on this SuperBowl Day as noted in OP's update
-----------------
update
Hi yogia,
Thank you for your effort it is much appreciated, but i'm sorry to say it is still not what i need. With your formula day names are not continuously populated. I have attached example of what i need, and i have entered names of days where they should be for one week, but i need it to be automatically populated for whole month.
Hope it will be useful to better understand my problem.
https://docs.google.com/viewer?a=v&pid=forums&srcid=MDIyMTM4Mzk3OTYzNDIwOTIzNTEBMDUwMjc3Nzg5MTA1MjY1Mzc2NTEBOTg5NzM1LjMzMzYuMTMyODQ0MTU5OTMzOC5KYXZhTWFpbC5nZW8tZGlzY3Vzc2lvbi1mb3J1bXNAeXFvZTEyATQBZ29vZ2xlcHJvZHVjdGZvcnVtcy5jb20
------------------------------------------
Well, let us see if I hit the goal this time in regard to solution to the problem