Sunday, August 12, 2018

yogi_Calculating Concurrency with Start Times/End Times and Duration

Google Spreadsheet   Post  #2485

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

question by: Anders Vonderheyde!topic/docs/fHalIMqftkU;context-place=mydiscussions
Calculating Concurrency with Start Times/End Times and Duration
Hi all,

I'm looking to calculate chat concurrency. In my case, this is defined as: on average, how many chats is a rep handling at the same time? I have chat start times, end times, and chat durations listed in seconds. What is the best way of calculating this overall? I've included a screenshot of a little glimpse of the data below. Happy to share the doc with the full dataset with someone as well. Thanks!!!

Thursday, August 9, 2018

yogi_Key-in Student First Name In Cell E2 And Pull All Student Information From 'Data' Sheet

Google Spreadsheet   Post  #2484

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

question by: Junnie Chen!topic/docs/law8Pw5wjLw;context-place=forum/docs
VLookup Using Partial Values
I have a list of student names and their information in 'Data' sheet. Separately in 'Inquiry' sheet, I want to pull all information for that particular student once user key in the student name partially in cell E3. Currently I am using the formula below to extract the information but it does not work if I key in the name partially.


I've included the sample sheet here. User would need to key in only partially of the name eg. "Alex" instead of full name "Alex Tan Man Man " and i need the rest of the info to be populated automatically. Kindly advise me how to fix this, many thanks!

Wednesday, August 8, 2018

yogi_Compute Sum Of The Maximum of Group of Specified Number Of Rows Of Numbers In A Column

Google Spreadsheet   Post  #2483

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

question by: David J Spigelman!topic/docs/UWgK-rRzjFc;context-place=forum/docs
Sum of Max of every N-rows?
I want to be able to take a column full of numbers, and find the max values of every 3 rows. I then want to be able to total just those max values. So, for the list of values:

I want to be able to select 28, 290, and 10, which would then give me a total of 328. I've been able to find how to add every 3rd value, but not how to max them by 3 rows. I've looked at MAXIFS, as well as several SUMIF with ARRAYFORMULA, MOD and ROW functions thrown in. I'm guessing it actually is a solution involving those, but I can't seem to put it together right. 

Can anyone help with this, and explain how your solution works?

Sunday, August 5, 2018

yogi_Conditionally Format B1:D1 When Date In Column A Is TODAY And Columns B:D Houses Off In Any Row

Google Spreadsheet   Post  #2482

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

question by: Cary K!topic/docs/guf_6HBhZT4;context-place=forum/docs
Conditional formatting - Changing header cell color if two cells in row match criteria
Hi All!

I'm hoping someone can help me with a problem I'm having because it's definitely stumped me.  I consider myself to be pretty proficient with conditional formatting but am struggling trying to figure out how to change a header color based on the values of two cells in a row.  I have Names as headers (Row 1) and Dates in column 1.  The dates would actually be a list of all the dates in a calendar year.  In this example, I've just included a few from August.

What I'd like to do is when the date in column A equals today's date AND the value in the cell in column B for today's date is equal to "OFF", I'd like to have the Name1 header change to a specified color.  Same for Name2, Name3, etc if the value in their respective columns is "Off" and the date is today.  So as an example if today's date was 08/05/18 and the value in column B for that date was "Off" then the Name 1 header would change color.  Kind of like a intersection of the date and name if it's OFF.

I tried using =AND($A2=TODAY(),$B2="Off") in the "Custom Formula" portion of conditional formatting with the range being B1 but that didn't work.  Same with the following ...

=AND($A1:A=TODAY(),B1:B="Off")  as well as other formulas but none seem to work.

Any thoughts?  Or is this even possible using "Custom Formula" in conditional formatting?

Cary K

Thursday, July 26, 2018

yogi_Find Associated District From Given Set of Road/Locality and District Data

Google Spreadsheet   Post  #2481

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

question by: Steven Goovaerts!topic/docs/jI1bs1T3skI;context-place=forum/docs

Please Help - Return cell data based on range search


I am very new to this and was hoping someone may be able to help?

I have been trying and failing to come up with a way to search a cell value across a large data set and return the column title of the located cell as the result.

I have tried everything from VLOOKUP/HLOOKUP to INDEX and MATCH, to ARRAYS, but I am completely lost.

I have attached a sheet with an example data set.  It includes a table of district groups and individual road references within those districts.

Essentially, I would like to be able to search by a road reference in a cell (e.g. "ElBrto2") and have the results return the district it is in (e.g. Springfield).  Something like ... SEARCH RANGE FOR ... "ElBrto2" AND RETURN RELEVANT COLUMN HEADER.

I am sure there is a simple way around this, so apologies for asking something so basic.

Any help would be greatly appreciated :)  Thanks!


yogi_Query B27:C Where C=Value As Specified And Sort By B and C In Desc Order

Google Spreadsheet   Post  #2480

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

question by: guyute!topic/docs/dA21oBp67f4;context-place=mydiscussions
How can I count totals of items multiple columns when the items overlap in each column?
I'm wondering if you could help. I'm trying to record procedures being done by multiple people.  Ultimately, I want to get the total amount of each unique procedure each person has done.  They will do different number of procedures each time (i.e. sometimes just one, sometimes 2, sometimes 5, etc.)  

Here's an example of what I have set up

Procedure 1Procedure 2Procedure 3Procedure 4Procedure 5


Each procedure could be either a, b, c, d or e.

Tom, Jerry, Mickey, and Minnie are the people performing the combinations of procedures.

I want to total automatically each specific procedure (a, b, c, d or e) regardless of which procedure column it falls in.  In other words, I don't care if there are 3 "a"s in procedure 1 column and 2 "a"s in column 3.  I just want a way to get to the total of 5 "a"s for Tom, Jerry, Mickey or Minnie.  

Is there a better way to record the data rather than by creating 5 procedure columns?  If not, then is there a way to get a count of unique procedures in all 5 columns?

This is somewhat difficult to explain, so I apologize if I'm not doing it well.  Thanks for any help!

Wednesday, July 25, 2018

yogi_Conditionally Format By Date Groups With A Blank Line Between Date Groups

Google Spreadsheet   Post  #2479

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

question by: guyute!topic/docs/9bJSSRpGbvY;context-place=forum/docs
How do I automatically sort by 3 sequential columns and add a colored dividing line?
I would like to automatically sort by 3 fields sequentially so that I don't have to highlight everything, then click data>sort range each time.

1. How can I have it automatically sort by multiple columns? First by date (column B), then by location (column I), then by time (column c)?
2. Also, I would like to have a colored line (bottom of rows 31, 32 and 39) dividing the different dates. If a colored line isn't possible, then an empty row.

Here is the link to the spreadsheet:

The sheet is called "example 2 for totals"
The tab is called "autosort".

The top part of the table shows the data unsorted.
The lower part of the table shows it sorted with the horizontal line dividers in it.