Saturday, June 23, 2018

yogi_add values up from a data (from to hours) validation drop down box

Google Spreadsheet   Post  #2464

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

question by: kyle hoelger!topic/docs/KOhWMJqU6cE;context-place=forum/docs
How to add values up from a data validation drop down box?

I have created a rota with data validation drop down boxes with the times I went my team to work, at the end of the week in a separate column I wanted to add up how many hours that staff member has worked. so example is 8:00am - 16:00pm is 8 hours shift but i cant work it out how to get that cell which contains the value 8:00 - 16:00 to equal a number for example 8 hours. 

What I would like it to do is be able to use the data validation so I can drop down the times for each staff member and it all adds up the total hours worked in end column. 

Is there a way to make this happen?

I have attached a link to my document but all personal data has been taken out of it for reference.

Many Thanks

Friday, June 22, 2018

yogi_Pull Multiple MATCH results from one row

Google Spreadsheet   Post  #2464

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

question by: Kelther!topic/docs/Nu5z3DJ3nJg;context-place=forum/docs
Multiple MATCH results from one row?
Is there a way to use Match to get multiple results on one row?

My data is as follows

Name     Apples     Bananas     Oranges     
Alice          1                 3                   4
Bob           3                 3                  2
Charlie       2                 2                  2

I'd like to put in a formula using VLOOKUP and MATCH to determine the following:

What does [VLOOKUP index] have [MATCH Index] of?
What does Alice have 4 of?
What does Bob have 3 of?
What does Charlie have 2 of?


Alice     Oranges
Bob       Apples      Bananas
Charlie   Apples     Bananas    Oranges

Is there any way to do that?

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!