Sunday, June 17, 2018

yogi_Unique Values From Multiple Columns Based On Values In Another Column

Google Spreadsheet   Post  #2463

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

question by: classroom in school!topic/docs/2AmNIytMxiw;context-place=forum/docs
Unique list of names from multiple columns and based on value in another column?

I have three columns:
A2:A6 contains letters like A or B
B2:B6 contains names
C2:C6 contains names

A name  can appear in both column B and C, like "joe" or "ivan".

How can I create a list with unique names, where the names only appear on the same row as letter B in A2:A6?

Saturday, June 16, 2018

yogi_Compile Monthly Statement By Item From Monthly Expenditure Sheets

Google Spreadsheet   Post  #2463

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

question by: Edison Wu!topic/docs/Cs2NyUuZVI0;context-place=mydiscussions
Data validation question
Hi all,

I want to make a budget spreadsheet. I made a data validation drop-down list of the items so I can choose, for instance: I choose "Grocery" from the drop-down list and enter the value next to it. 

But is it possible to store the value and output the value on another sheet. Like I put $25 next to "Grocery" on day 1, and put $10 on day 2. Is it possible to show the sum of the value on "Grocery" and output it?

Thanks a lot.



yogi_Prepare A Consolidated List Of Certifications Earned From Data In 'Form Responses 1'

Google Spreadsheet   Post  #2462

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

question by: dlrbestpublic!topic/docs/79stcxty14o;context-place=mydiscussions

How to create an array that omits blanks and writes to one cell?

Need some help please. I am using a google form to input certification into a Google Sheets spreadsheet. The form answers get written into a worksheet named 'Form Responses 1'. That tab and my Master tab have a Member ID column for matching purposes. 

Right now, I have no problem copying the first matching line (using Index/Match) from the Form Responses 1 tab into the Master tab to populate that member's certifications. 

However, as I have a few folks that are not familiar with Excel/Google Sheets, I would like the Google Form to be used for certification updates as well -not just a one time entry per member. For example, on initial entry, Certifications 1 and 2 were recorded for Member 1. Then one month later, Member 1 received Certification 3. Using the form to input that adds another line to the Form Responses 1 tab for Member 1. I would like to create an array of all entries for Member 1 and for each cell that is not blank, write that information into one single cell on the Master tab. So far, I cannot figure out how to get it to disregard blanks and to write to the same cell. This is the base formula I have been working with: 

Column B has the Member ID in the one sheet and A3 has it in the other. Column J is just one of the 28 certifications that are being tracked. Let's call that Cert1. I am trying to write the responses found in J (knowing that all will be blank but 1) into cell I3 on the master sheet. I have tried using ISTEXT, <>"", isblank, but the answer is eluding me. Any help is most appreciated.

=ArrayFormula(IFERROR(INDEX('Form Responses 1'!J$2:J$999,If('Form Responses 1'!J$2:J$999<>"",(SMALL(IF('Form Responses 1'!$B$2:$B$999=$A3,ROW('Form Responses 1'!J$2:J$999)-MIN(ROW('Form Responses 1'!J$2:J$999))+1),COLUMNS(I3)))),""))

yogi_Compute Amount By Category By Month

Google Spreadsheet   Post  #2461

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

question by: Marc Zaldivar!topic/docs/sVyYglkxraI;context-place=forum/docs
Trying to set up multiple conditions with SUMIF or DSUM?

I'm have a form that collects payments by different categories.  I'd like to create another sheet that shows sums for each of the categories within a certain date range.

For instance, Sheet1:Column A is Payee.  Sheet1:Column B is Date.  Sheet1:Column C is Amount. Sheet4: Column D is Category (let's say, CatA, CatB, CatC).
On Sheet 2, I'd like to sum any amounts that are between 5/1/2018 and 5/31/2018 in CatA on one row, CatB on another.  

I assume I need nested IF statements or a DSUM, but I keep getting an error. 



Wednesday, June 13, 2018

yogi_Compute Percentage Of Persons Who Got Scores Between Specified Range Of Numbers

Google Spreadsheet   Post  #2460

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

question by: J_sj!topic/docs/mh-DoJ_Jljs;context-place=mydiscussions

Simple percentage-calculating. (arrayformula?)

Hello, dear friends of higher understanding. 

I actually have two problems, the first one is my real problem, and the other one is explaining the first problem. The title isn't great, I know. 
Okay, so heres the deal, I have a spreadsheet looking like this:

The rows 5-19 each contain the scores for different players on 10 questions (1-10, G:F). 1 row is one player. Simple, right?
Now, here's my problem: I would like to make some simple statictics, like how many of all players (In percentages) get between 0-5 points,  how many get 6-10  points etc on these 10 questions.
So I need to:
1. Add up the points from G:F for each row.
2. Check how many rows that get between 0-5 points, how many that get 6-10 etc.
3. Calculate how many percentages of all players that got between 0-5 etc.

At the moment, I'm using far to many cells for this. It is my belief that this can be done with few cells using arrayformulas or something, I just donät know how to. Does anybody know?

I hope I have made the problem easy to understand.

Have a great morning/day/night!

yogi_Sum Up Time Entered in B3:B As Minutes And Seconds Without The Colon Character For Ease Of Entering

Google Spreadsheet   Post  #2459

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

question by: JoLR!topic/docs/FQa7QUUWdSE;context-place=forum/docs
Custom Time Formatting Help

Hello! I work in film editorial. I'm looking for a way to enter a time in minutes and seconds, and have it display as "xx:xx" - I'd like to enter this value as either 4 consecutive digits (i.e. no colon), or as minutes.seconds (e.g. 03.21). The reason being, I'm entering a lot of data, and it's a hassle to have to enter a colon when I just want to keep my right hand on the numpad.

Is there a way I can create a formatting rule, so that entering "0210" displays as "02:10" and correctly registers and minutes and seconds? Because I also have a function that adds the sum of this column so I have a total duration at the bottom.

Please let me know if that's clear. Thank you for your help!


Sunday, June 10, 2018

yogi_Create Top List From Table In A4:B7 With Scores And Names In Descending Order

Google Spreadsheet   Post  #2458

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

question by: Jesper_S!topic/docs/lvwDq5sPzfU;context-place=forum/docs

Creating a toplist


Good morning/day/evening!

I have a table "score"(A3:B7). In this table, I have the scores of four different people. From this I would like to create a toplist, looking like the one in the picture (A10:B14). 

I need a function that places all the people with the same scores beneath eachother, a.k.a. "John" in B11, "Jessie" in B12 and "Emma" in B13. How should I do?

I have managed to put John in B11 and Abby in B14 by using a combination of index() and large(). The problem I get, however, is that John gets put in B12 and B13 aswell, so, yeah, help?



yogi_query a column of single cells and output it to a column of merged cells

Google Spreadsheet   Post  #2457

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

question by: Blorty Blortson!topic/docs/R3Ku2m0BbT0;context-place=mydiscussions

How do I query a column of single cells and output it to a column of merged cells?



I have am trying to import some data into merged cells (3 cell height). I need the merged cells because they are effectively labels for 3 rows. The problem I am having is that the range being queried is not merged so some data is being "hidden" in the merged cells; 1 cell appears and the other 2 are hidden by the merge. 



Current Output:



Desired Output:





My query is a standard =query(importrange("url","tab!range"),"select * where Col1 is not null")

I have been looking for about an hour but can't find anything of help. Is it possible to do what I am attempting? 

Thanks in advance! 

Edit: What I am hoping for is to have only one value per merged cell. So if A1:A3 and A4:A6 are merged cells, the data from the query would go into cells A1 and A4. 

Sunday, June 3, 2018

yogi_Pull Individula Stats For Entities From Data In Tabs Of Another Spreadsheet

Google Spreadsheet   Post  #2456

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

question by: Joan Tomines!topic/docs/Hgn2vUJiq-A;context-place=forum/docs
Grabbing of data from one sheet to another.

Hello, Experts!

I need your help again.

I am working on this sheet:
>>> ATAMAI tab of Copy of Team Stats (Joan) (Please see attached image).

What we are trying to achieve is, we need to pull up the data depending on the month (in column A13) and the name on row 14.

The data with the stats will be coming from this sheet:
>>> All the tabs in Copy of TRIBE ATAMAI (Joan)

Kindly check and advise.

Thank you in advance!

- Joan :)
26 KB