yogi_Count Number OF Unique Drinks And Number Of Drinks By Person

Google Spreadsheet   Post  #2155

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-29-2017

question by epelj1:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/ydLHdtDT-j4;context-place=forum/docs
Trying to 'rank' the most common used words in a range

I guess, while I'm at it, is there any way to Label the Col1 of that formula?

I'd appreciate any help! Thanks!

Test Sheet:

https://docs.google.com/spreadsheets/d/1Pa2UntDLeHrfV1KXiMD24TFt0cB_RM7fZwTSTEf4Srk/edit#gid=0

yogi_Compute Row By Row Sum Of Scores In Several Columns Per Poster's Specification

Google Spreadsheet   Post  #2154

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-22-2017

question by Brittany Manning:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/xKcapVNrkv0;context-place=mydiscussions
Formula to add cell values in a range BUT only add value up to 5
I have a sheet that I keep soccer scores on for district rankings.  One statistic that we must keep is "Goals Scored Maximum 5" and "Goals Against Maximum 5"  Is there a way to add the goals per game but only add the values up to 5 in each cell?  For example: 5, 9, 6, 1, and 3 would only add to 19 because anything over 5 is not added into the Sum.  I do not want it to omit the values if the cell reads greater than 5.

Please help! Thanks!

yogi_Generate Monthly reports From Data In Another Sheet

Google Spreadsheet   Post  #2153

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-13-2017

question by Paul Barley Chito:

https://productforums.google.com/forum/#!topic/docs/oFearzrrof0;context-place=forum/docs
Data Reporting with Additional Dates
Hi Guys,

I have data I wanted to generate reports.

1.) My problem is how can I do this automatically based on ranking.

2.) Second, report generation must be done monthly and so on.. E.g. If I encoded a new month, a new report for that month must be automatically created on a new column.

I attached my actual sheet.

Thank you in advance,

Paul

Database

Data

yogi_Compute Rate Of Pay Based On Position and Years

Google Spreadsheet   Post  #2152

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-13-2017

question by drdfoley:

https://productforums.google.com/forum/#!topic/docs/WPX4pFCL12E;context-place=forum/docs
Lookup value based on two criteria

I want to figure out the rate of pay for an employee based on the position and the number of years worked.  The first sheet has the employee's position and the number of years worked. The second sheet has a list of positions and the rate of pay based on the number of years worked.

I have tried a variety of formulas with no luck.  I am able to do the lookup based on the position but don't know how to get the rate of pay based on the number of years worked. Any help would be appreciated.

Thank you,

Dianna

Here is the sheet:

https://docs.google.com/spreadsheets/d/1o6l3zVG--trtg7j7LyWxczqc8r8lZVf6Op8JJ6Mtipo/edit?usp=sharing

yogi_Help With LookUp/Match and Split

Google Spreadsheet   Post  #2151

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-10-2017

question by giorobert88:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/UMjEdZqx5bU;context-place=mydiscussions
Help with lookup/match and split

Hi all,

I have been creating a spreadsheet which feeds directly from a Trello API.  One of the columns returns all of the tags for a given customer in a comma seperated list and I am looking for a way to look up the most recent and then only return the details to the right of the "unit:" tag before the next seperator (,). 

So far I have found a way to lookup the latest version:

=arrayformula(if(len(A2:A)=0,iferror(1/0),iferror(vlookup(A2:A,Log!B2:C,{1}*sign(row(A2:A)),FALSE))))

And a way to split:

=TRANSPOSE(SPLIT(JOIN("," , Log!C2:C), ",", FALSE))), 1, TRUE)

However, I am struggling to combine the 2, filter for only the data containing unit: and then return the data before the next comma. A copy of the spreadsheet can be found here:

https://docs.google.com/spreadsheets/d/14MSrBqZ9ye372_NFXUXgnBW9LJCbMatRVjHGUht12Uw/edit?usp=sharing

The log shows the raw output from the API. The Summary sheets shows the table I am trying to achieve. What I would like to return in cell B2:B4 is S5,S6,S5 therefore is looking up the latest activity per customer and returning the data following "Unit:" before the next , seperator.

Hopefully that makes sense. Any help would be much appreciated.

Thanks in advance.

yogi_Count Number By WeekDay For WeekDays Listed In Column F

Google Spreadsheet   Post  #2150

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-09-2017

question by Cyndy Hopper:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/4MqXIv_xRyY;context-place=forum/docs

HELP needed with formula

I have a column that contains data like

               Monday, Tuesday, Wednesday, Thursday, Friday

               Monday, Wednesday, Friday

               Tuesday, Thursday, Friday

               Wednesday, Thursday, Friday

               Tuesday, Wednesday, Thursday

I would like to count the number of Mondays             (2)

                              the number of Tuesdays            (3)

                              the number of Wednesdays       (4)

                              the number of Thursdays           (4)

                              the number of Fridays               (4)

HELP......... as you can see I'm not a big formula user - but need for this spreadsheet

Thanks !

yogi_Setup Array Formula For Computed Column Based On 'OR' Criteria

Google Spreadsheet   Post  #2148

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-09-2017

question by HelloHey:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/KjrrSY7qQ4I;context-place=mydiscussions
Automatically expand formula to new rows. How?

Hi,

How do I apply a formula to ALL cells in a column? Alternatively, how do I automatically expand a formula to new cells?

I want to expand this:

=IF(OR(F27="USA",F27="Canada"), H27, 0 )

Thanks!

yogi_Setup Table For Group No And Tickets In Each Group For Specified Number Of Groups And Tickets In Each Group

Google Spreadsheet   Post  #2149

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-09-2017

question by Daniel Umansky:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/8zEJcQ8t0fo;context-place=forum/docs
How do I auto fill a series of ticket numbers (in groupings of 25) in a Google Sheet?

I am creating a Google sheet to keep track of Raffle tickets for a fundraiser. I am assigning 25 tickets to 100 individuals.

tickets 1-25 are going to Person A, Tickets 26-50 Going to Person B, Tickets 51-75 assigned to Person C etc, 

I am trying to auto fill the column of ticket numbers, 

1-25

26-50

51-75

Etc

etc

etc

However when I select the 3 cells and then auto fill on down what I get is

2-26

27-51

52-76

The Auto fill needs to go in increments of the next series of 25 tickets.

What am I doing wrong?

yogi_Rearrange Table In A2:B5 In A DataBase Style Table

Google Spreadsheet   Post  #2147

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-08-2017

question by Edward Hugh Summers:

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

Transpose Question

---

Hi Yogi and Ballow. Here's an example of the type of transformation I want to do in a sheet:

https://docs.google.com/spreadsheets/d/1ZbsE-B_VQBKHdbhJTLSDn8o8jPOXRQHFC9RtF9y4cFI/edit?usp=sharing

Conceptually I don't think it's that complicated. It is worth noting that I'm not really interested in a solution that involves lots of repetition for each row, because there could be an arbitrary number of rows. I can do this fine outside of Google Sheets (in Python) but I was wondering if it's possible without leaving sheets for the classroom setting. If not, no need to stress :-)

If you are interested I want to turn the rows of data into an edge list that I can then visualize as a graph in Google Fusion Tables. 

yogi_Split Expenses By Participants

Google Spreadsheet   Post  #2147

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-08-2017

question by Tweedle:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/28zV7XRlYE4;context-place=forum/docs
If then function

I'm trying to create a sheet for shared expenses and am want to break down the expense by those involved.  I would like to have the following results.

Column F

If column E contains "WDG" then column F = column D/3

If column E contains "WD" then column F = column D/2

If column E contains "WG" then column F = column D/2

Column G

If column E contains "WDG" then column G = column D/3

If column E contains "WD" then column G = column D/2

If column E contains "WG" then column G = blank

Column H

If column E contains "WDG" then column H = column D/3

If column E contains "WD" then column H = blank

If column E contains "WG" then column H = column D/2

I would also like for the total row to float down as I add expenses.

Don't know if I am using the correct function.

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

yogi_Transpose ITEMS In A Table To A Single Column List

Google Spreadsheet   Post  #2146

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-06-2017

question by Edward Hugh Summers:

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

Transpose Question

 

I have a table like this:

  A 1 2 3

  B 3 2 1

Is it possible to use Sheets to transpose it into:

  A 1

  A 2

  A 3 

  B 3

  B 2

  B 1

Any help you can provide would be greatly appreciated!

yogi_Compute Row By Row Count Of Type Of Shards From Data In C6 To D

Google Spreadsheet   Post  #2145

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-06-2017

question by Micheal McAllister:

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

Using COUNTIF and wildcards for cells with commas and similar words

I'm trying to create a form to tally the drop rate of shards for this game I'm playing.  I wanted to see if some shards had a higher/lower drop rate than others.  To count the individual shards per difficulty, I've been using =countif(range, "*shard name*") and that has worked well.  

Unfortunately, two of the shards have the same first word "Piercing" and "Piercing Power" so when I use the above code to count "Piercing" it gives me the total for both of them. Is there anyway to differentiate the two different shards?  Can I somehow include the comma in the criterion?

Here's a sample spreadsheet  

yogi_List In Column C Items That Are In Column A and NOT In B And Those That Are In B But NOT In A

Google Spreadsheet   Post  #2144

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-05-2017

question by Dominic Ng:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/EtH811d42PE;context-place=forum/docs
Comparing Two Columns and Returning in Third

Hi,

I have a question about how to write a formula that compares two columns (i.e. Column A and B) of string data and returns the string data in the third column where if the data is matched BETWEEN A and B, ignore it but if they show up in A and not B and show up in B but not A, I'd like those strings returned. 

Is this possible to do it in 1 column or maybe even 2 at max? Thanks!  

yogi_Operate On Entries Made As Decimals Or Fractions (google sheet does not recognize entries made as fractions as numbers)

Google Spreadsheet   Post  #2143

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-05-2017

question by JRPennington:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mF3XksBoDAk;context-place=mydiscussions
Fractions and Decimals question in Google Sheets

I'm trying to create a spreadsheet that will help me generate a list of dimensions to build things to. What I need to do is be able to type a fraction in one cell and have it calculate a dimension into another cell. I have the calculation set up and works great when inputting numbers using decimals but does not work at all using fractions. Most of my inputs are in fractions to the 1/16" and sometimes 1/32" so converting them to decimals before putting them in the sheet is just a giant inconvenience. In excel I can input a fraction and it will use the decimal form in the calculation (while still showing the fraction) and it's so easy. Does Google Sheets not convert fractions to decimals for the purpose of calculation?

EX. I need to take off 3/32" from each input that I give. I need to be able to input dimensions such as 5 9/16" or 8 1/4" and have it calculate. Currently I am having to input as 5.5625 or 8.25 respectively and it takes too much time trying to figure out the decimals myself. Basically I would like to put in 5 9/16" and have it automatically convert it. Is this possible?

yogi_From A Table Of Rounds And Players Pull Round Each Player is In

Google Spreadsheet   Post  #2142

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-01-2017

question by Toby Jacobs:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/IDdFPhXrARg;context-place=forum/docs

Google Sheets - Use VLookup/Index-Match to look in array for value

Hi all - 

I'm working on using Google Sheets to create draft software for a recreational sports league. I'm trying to use index/match on an array to do a number of things in the software. I did this in Excel a few years back but can't figure out how to get it to work in Google Sheets. 

Example: https://docs.google.com/spreadsheets/d/1Ajq-4zcd0ed3JrBo99Y27r8mVVh1pE7yrjfez1qwfYs/edit?usp=sharing

And here's the same thing copied:

Round 1	Round 2	Round 3	Round 4	Round 5
Player 1	Player 9	Player 11	Player 12	Player 14
Player 5	Player 10	Player 4	Player 6	Player 15
Player 7	Player 3	Player 8	Player 2	Player 13
	Round?
Player 1
Player 2
Player 3
Player 4
Player 5
Player 6
Player 7
Player 8
Player 9
Player 10
Player 11
Player 12
Player 13
Player 14
Player 15

I would like to populate Round? (B7:B21) with a formula that looks for the player name (A7:A21) in the array (A2:E4) and returns the Round (A1:E1).

Any ideas welcome. Thanks!