Saturday, April 22, 2017

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!

Thursday, April 13, 2017

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



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


Monday, April 10, 2017

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
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:


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.


Sunday, April 9, 2017

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?