Saturday, July 22, 2017

yogi_Count Cells That Begin With Dates marked as 'dd.mm.yyyy' Of Specified Month And Year (country code Philippines)

Google Spreadsheet   Post  #2210

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-22-2017
question by Emerz:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ccHqOrz-RXQ;context-place=forum/docs

yogi_Count Cells That Begin With Dates Of Specified Month And Year

Google Spreadsheet   Post  #2209
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-22-2017
question by Emerz:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ccHqOrz-RXQ;context-place=forum/docs

Monday, July 17, 2017

yogi_Multi-Criteria Count Based On Entries In Top Row And Left Column And Data In Another Sheet

Google Spreadsheet   Post  #2208
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-17-2017
question by Roy Klossner:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/G3CDV6R0ANc;context-place=mydiscussions

Using Data Validation for dynamic chart with different size range

Hi,

I'm trying to do a count of call days for a google calendar for a given set of dates.  Here is the link: https://docs.google.com/spreadsheets/d/17It8HbfmRSHvnYQgSVDYrAcyB43JCslrl5sg61Y9in4/edit?usp=sharing

I have most of it figured out.  I'm using a script to pull the data from the calendar to google sheets then using the COUNT() function to count how many calls a person has within a given date range.  What I would like to do now is count weekends twice.  
For example, Isaac is on call 2/11/17 (Sat) and 2/12/17 (Sun) so I want that to count for 2 in C4.  I REALLY don't want to manually input all the weekend dates into a COUNTIFS () function so was hoping to use a WEEKDAYS() function to do it automatically.  Can someone help me with a formula that will auto-count weekends with a COUNTIFS() string, given a certain person being on call any given Sat or Sunday?

Thanks!
Roy

P.S. Please don't run the script, the calendar link has been removed
---
Yogi,

Gen = number of times a person is on general call, indicated on the calendar by Gen: Name
Gen Sat/Sun = number of times a person is on general call on any given Saturday or Sunday, indicated on the calendar by Gen: Name
Gen (Second) = number of times a person is second on general call, indicated by the parenthesis.  For example Gen: Roy (Isaac), means Roy is on call for general and Isaac is second on call.
Gen (Second) Sat/Sun = number of times a person is second on general call, indicated by the parenthesis on any given Saturday or Sunday.  
OB =  number of times a person is on OB call, indicated on the calendar by OB: Name
OB Sat/Sun = number of times a person is on OB call on any given Saturday or Sunday, indicated on the calendar by OB: Name 
OB (Second) = number of times a person is second on OB call, indicated by the parenthesis.  For example OB: Roy (Isaac), means Roy is on call for OB and Isaac is second on call. 
OB (Second) Sat/Sun = number of times a person is second on OB call, indicated by the parenthesis on any given Saturday or Sunday.  

Thanks,
Roy


Thursday, July 13, 2017

yogi_Compute Row By Number Of Days Persons Worked On Weekends (Saturday and Sunday)

Google Spreadsheet   Post  #2207
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-13-2017
question by Roy Klossner:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/G3CDV6R0ANc;context-place=mydiscussions

Using Data Validation for dynamic chart with different size range

Monday, July 10, 2017

yogi_SetUp A Single Chart To Show Dynamically Via A dropDown Data For WeekNumber WeekDay Or AllDays

Google Spreadsheet   Post  #2206
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-10-2017
question by Sam Larimer:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/GNagREZr_Lc;context-place=mydiscussions

Using Data Validation for dynamic chart with different size range


I may be blindly searching for a how-to that does not exist, but is it possible to use data validation, formula in charts, dynamic ranges, etc. to create a dynamic chart that measures the same quantities but over different lengths of time?

Document in Question: Chart w/ Dynamic Range

Basically, I'm attempting to create a single chart that measures a float ratio and displays a month's data points by:
  • date in month
  • day name
  • weekly average
However, all this data in one place would be very cluttered and/or useless for comparison. The next best option in my mind is a data-validation to choose which data range is pulled for the chart.


Chart type isn't too important. I have faith in you experts, but let me know if this is a wild goose chase

Sunday, July 9, 2017

yogi_Pull Row By Row Most Recent Previous Order Per Specification

Google Spreadsheet   Post  #2205
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-09-2017
question by Matan Arie:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Ao79a29uT3Q;context-place=forum/docs
Trouble with **shifted** VLOOKUP in ARRAYFORMULA
Hi everyone!
I'm trying to do a VLOOKUP inside a ARRAYFORMULA, where the search_key is taken from the range A2:A, the lookup takes place in a range that starts a row lower (A3:B) and the result is taken from the B column of that range. 

=ARRAYFORMULA(IF(COUNTIF(A2:A,A2:A<2,"",VLOOKUP(A2:A,A3:B,2*SIGN(ROW(A2:A)),FALSE)))

Here's a test spreadsheet with the same data:
The formula is in D2.

The object of the formula is to find the previous date (B) that contains the same number (A) as the number (A) in the current row.

I've done the trick with adding *SIGN(ROW(A2:A) to the column index in VLOOKUP (even though it worked the same without it).

I thought the problem had to do with the fact that the lookup range starts a row lower than the search_key range (i.e. they had a different number of rows), but even when I compensated for that by setting the search_key range to A2:A50 and the lookup range to A3:B51, I got the same results.

Any advice?

Thanks!
Matan

Saturday, July 8, 2017

yogi_In Reference To Column A Provide Clean Names In Column C Considering Roster Names In Column I

Google Spreadsheet   Post  #2204
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-08-2017
question by Sam Larimer:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/nTDJouyOSfY;context-place=forum/docs

CONDITIONALS: Replace out-of-order/cluttered names with clean names from given roster


An exported report gives names that contain extra characters, names out of order, or both. I need the names to be replaced in correct First Name Last Name order.

Document in question: Names in order

If column A contains strings of names and column I contains a cleaned up roster, reorganize and remove non-letter characters. Return "clean" name in column C.

The spreadsheet contains a goal return (column E) and match check (column F)

So far, I've only been able to reorganize names if they are in the form BBBB AAAA with only one space. The following issues still exist:
  • Names that contain extra characters (1 or 2 plus signs) at the end of the last name field. "B++ A" should be "A B++"
  • Names that contain multiple words in surname are reorganized from "B C A" to "C A B", but they should be "A B C"
  • Optional: Names that are reported as "LastName, FirstName" reorganize as "FirstName LastName"...basically, I don't need something that only ignores plus signs. It should ignore any extra characters.

Now for the challenge. Avoid Google Script. I am extremely amateur at this stuff still and cannot code to save my life. This formula will need to be used and replicated in the future by other users in various applications.

Here's what I have currently. Feel free to scrap and start from scratch if it's as ugly as I think it is.

=IF(ISBLANK(A2),
A2,
IF(ISERROR(MATCH(A2,I:I,0)),
IF(ISERROR(MATCH(MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2)),I:I,0)),
A2,
MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2))),
A2))

Friday, July 7, 2017

yogi_Multi_Criteria Row By Row LookUp And Computation

Google Spreadsheet   Post  #2203
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-07-2017
question by O Ramirez:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/BJgy-4FOwCQ;context-place=forum/docs
Please help me figure out an IF formula for two ISSUES I am having!!!

Hi need help figuring out how to automate the Total & Fees columns every time we put the trip mileage?


***EXAMPLES FOR TOTALS

Example 1 : C2 should be $15 because it is ambulatory and it is equal or between 16 & 30 miles. 

Example 2 : C6 should be $20 because it is wheelchair and it is equal or between 16 & 30 miles.


***EXAMPLES FOR FEES(only applicable to wheelchair)

Example 1 : D3 should be $25 because it is wheelchair trip but only One Way

Example 2 : D6 should be $50 because it is wheelchair trip and Round Trip



Thursday, July 6, 2017

yogi_Show In Sheet2 Results From Combining Data In Sheet1

Google Spreadsheet   Post  #2202
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-06-2017
question by Lalla Lilley:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/1KbbcqanNro;context-place=forum/docs
If same Name, combine data in Column B???
Here is my sample file:

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


Here is what I'm trying to do :

Sometimes on a cruise there are several tips. Customer pays BEFORE cruise (Pre-Included Tip) and then decides they want to give them more at the end of the cruise.  

The Sample file shows how the csv file looks when this is done (Sheet 1 yellow).
I need code for a Sheet 2 that finds the "like" names and combines the total into one row of data.

I left the cell blank on Sheet 2 so that others can test code. I am hoping I shared it right this time and it allows editing.
If not, please let me know.

Thanks so much!
 L ~

yogi_Compute Total Hours Worked From Daily Log Of From To Hours

Google Spreadsheet   Post  #2201
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-06-2017
question by CooperElle:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ptItF96AX8k;context-place=forum/docs
How to calculate hours in google sheets

There is a specific formula (or function) I'm looking for in order to add all of my hours worked each week. I don't need one to calculate each day. For example it would look like:

((FORMULA))

1 Mon:   9-5 = 8hrs
2 Tues:  9-5 = 8hrs
3 Wed:  9-5= 8hrs
4 Thur:  9-5= 8hrs
5 Fri:     9-5= 8hrs
6 Sat:    OFF
7 Sun:   OFF

Total hours worked: ??

Wednesday, July 5, 2017

yogi_Sort Multiple Entries By Run Time In Descending Order

Google Spreadsheet   Post  #2200
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-04-2017
question by Michelle Greenwalt:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/YVQx6erT3RE;context-place=mydiscussions
Using Index & Match on columns that contain repeat entries
Hello - 

I'm creating a workbook that will help us run our swim meets more efficiently. On the first sheet the home and visiting coaches list their swimmers and their best times. The second page then sorts those times from fastest to slowest with 4 colums - the first is the swimmer's name, second their time, third is the list in ascending order from fastest to slowest (using SMALL) and then the last column matches the time in column 3 to the name in column 1 and shows the name (using INDEX & MATCH.) 

These are then pulled into a 4th sheet that creates a list for each lane for the timers to use. Currently I'm manually building the sheet, but that's another question on how to make that easier. 

The issue that I have is that some kids have duplicate times (28.8 and 28.8) and INDEX & MATCH stop at the first instance of the time and match that name to the cell. So then I have two results in the 4th column for the first swimmer who has 28.8 and zero for the second. Ideally it would show the first and then the second below it in alphabetical order if the times were the same. 

Any ideas how I can have the formula disregard the first after it has been used? Thank you!!!

Tuesday, July 4, 2017

yogi_Pull From Data In Another Sheet Row By Row Sum Of Entities And Total At The Bottom

Google Spreadsheet   Post  #2199
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-04-2017
question by SATH59:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/HSuxTDzvEZU;context-place=forum/docs
Help on a Query
Hi

In this sample doc:


How can i achieve correctly the query im tryng to do ?

I have a tab named query where i explain better my Expected result for better understanding

Also need a total row below my query

Thanks !