yogi_Consolidate Names Of Registrants From Several Columns Into A Single Column

Google Spreadsheet   Post  #2423

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-13-2018

question by: Arthur Shurn
https://productforums.google.com/forum/#!msg/docs/3gL5zqBfwxE/6EadHDwpCgAJ;context-place=forum/docs

How to Consoliadte Multiple Columns into a Single Column

I have a spreed sheet that is a collection of registration information. Each person who registers is allowed to register more than just himself/ herself. Thus I have several columns with names within each registration. I need to create a master registration which is a single column of all the names registered. Is there a way to do this with Pivot tables. I’m familiar with pivot tables but have not been able to extract this master registration list without cut and pasting the information. I have included a sample of my sheet.

https://docs.google.com/spreadsheets/d/1FWdsEkHc__Y_OtmkVTs3X6GZtX7_RAUDJH3aYYfu3K4/edit?usp=sharing

yogi_How To Handle Field With Mixed Data Type While ORDERing This Field Using QUERY Function

Google Spreadsheet   Post  #2422

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-12-2018

question by: Krist_chat
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/B4hWffMJRgI;context-place=mydiscussions

Creating a "Query How To" ... stuck on order by
Hi All,

I'm creating a short and basic 'how to' guide for beginning query users. Ran into an unexpected issue with 'order by' as it's only working when I have a 'where' included. Please see this Sample Workbook, specifically the 'quick reference' tab and the associated number tab #4.

Feel free to add any simple examples if you've found them helpful. I'm trying to think of the basic ones that are used frequently when beginning to work with query. Maybe add an average or something?

Thank you!

Kristi

yogi_Create A List Of TRUE(1) And FALSE(0) With A Specified Approximate Distribution Of TRUE And FALSE Entries

Google Spreadsheet   Post  #2421

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-11-2018

question by: iamthebull
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/NLCYqe4WaRg;context-place=forum/docs

how to generate random list of boolean values with certain distribution of true values
In sheets I would like to generate a random list of boolean values, say 100 values, where a certain percentage of those values will be true (ie. approx 60% are True). How can I do this?

yogi_Sum Numbers In Column B if entry in column C matches one in Lookup table when dare in column A is within specified limits

Google Spreadsheet   Post  #2420

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-11-2018

question by: benr4513
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/DRNE9pC-n1U;context-place=forum/docs

SUMIFS on whole column with multiple criteria including conditionals

Here is an example spreadsheet. I wish to sum the values in column B when the date is between 2018-04-08 and 2018-04-20 AND the letter in column C is in column the lookup table

2018-04-10	1	a		Lookup table
2018-04-09	10	r		a
2018-04-26	100	g		b
2018-04-02	1000	e		c
2018-04-12	10000	v		d
				e
			0

https://docs.google.com/spreadsheets/d/10ueiZnLRYsd5-65xtlMIPVp0Tya73g8dfjF8rT02Ufg/edit?usp=sharing

the following works in excel

{=sum(sumifs($B:$B,$C:$C, E2:E6,$A:$A,"<=" & DATE(2018,4,20),$A:$A,">="& DATE(2018,4,8)))}

but this doesn't work in Sheets due to sumifs not working in arrayformula

=ARRAYFORMULA(sum(sumifs($B:$B,$C:$C, E2:E6,$A:$A,"<=" & DATE(2018,4,20),$A:$A,">="& DATE(2018,4,8))))

does anyone have any ideas as to how i might get the desired result?

yogi_Look For Teams In 'Season 1 Challenger' And Pull Name Of Opponents

Google Spreadsheet   Post  #2419

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-10-2018

question by: Dravaco
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/o8pC6uNKltU;context-place=mydiscussions

INDIRECT to a range reference in a different sheet doesn't work.

So what I'm trying to do is create a (dynamic) cell range referencing a range in another sheet. This is what I've been trying:

=MATCH($E$2, INDIRECT("'Season 1 Challenger'!B4:'Season 1 Challenger'!B19"), 0)

=MATCH($E$2, INDIRECT(("Season 1 Challenger!" & ADDRESS(7, ((A4 - 1) * 8) + 2)) & ":" & ("Season 1 Challenger!" & ADDRESS(8, ((A4 - 1) * 8) + 2))), 0)

=MATCH($E$2, INDIRECT(ADDRESS(4, ((A4 - 1) * 8) + 2, 1, True, "Season 1 Challenger") & ":" & ADDRESS(19, ((A4 - 1) * 8) + 2, 1, True, "Season 1 Challenger")), 0)

Every single one of these returns the error:

Function INDIRECT parameter 1 value is "Season 1 Challenger'!$B$4:'Season 1 Challenger'!$B$19'. It is not a valid cell/range reference.

When I try the same things, but just making a cell reference instead of a range reference, it works perfectly fine, but I need a range reference.

If it helps, I can share the sheet.

yogi_Compute Average Of Entries In Column F For Names In H2:H For Month Specified In Cell G2

Google Spreadsheet   Post  #2418

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-10-2018

question by: [AIR] Joshua Murphy
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/OHCo_6DL1Xs;context-place=mydiscussions

What is the formula for when you want the avg of 1 column only when other column is "x"?

Hello Google Forum Community!

Another situation I'm hoping I can get some help with. I'm looking to get a Formula in I2 that will give me the avg of all data entries in F2:F, but only if the agent's name matches. Ex: F2:F avg if C2:C = "John Smith".

Does anyone know how I would do that? 

I tried with a =query, but could not get it top work out.

I intend on have a google form auto enter the data into these columns and will have agent names change under column C to include anywhere from 11-20 different agent names over the year.

I am sharing the doc, see the "Absenteeism" tab at the bottom to find my ref.

https://docs.google.com/spreadsheets/d/1cJ5TIxYPlg6N7UkwgpufvgYC3YaA06mg4ASIjGTLFDE/edit?usp=sharing

Thanks,

Josh

yogi_Sum Entries In Column G Of Rows That House A Specified Entry In Cells A101:F107

Google Spreadsheet   Post  #2417

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-09-2018

question by: Dan Zunko
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/bJluhcsE_r4;context-place=forum/docs

Add column if row contains a certain value

Hi All.

I'm fairly good with Excel and Sheets, but I have a problem that has me stumped.  Consider the following:

	A	B	C	D	E	F	G
101	1	3	5	7	9		56
102	1	2	3	5	6		27
103	2	3	4				49
104	2	4					23
105	3	6	2	1	8		84
106	9	6	3	7			57
107	2	7	4	9	5		63

I would like to calculate the SUM of the last column (G)  IF a certain number appears in the same row.

For example, Lets say x=1.  I would like to find the sum of column G in any row which contains a value of 1,

In this example, row 101, row 102, and row 105 contain a value of "1", so I want the sum of G101, G102, and G105.

I thought about Match, Index, Vlookup, and Find, but I can't seem to get them working together to give my desired result.

Any ideas?

Thanks so very much!!!

Dan