Monday, January 22, 2018

yogi_Work Around For Using An ArrayFormula Where Filter Function Is Being Used

Google Spreadsheet   Post  #2352

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-22-2018
Transform a formula into an arrayformula

Hi all, can anyone give me a hint with this?
I have this fórmula


I would like to make it and arrayformula, just like this:


I know that FILTER doesn't work with ARRAYFORMULA, and am also aware that VLOOKUP does. My problem is that i haven't figure out a way of transforming that FILTER into and WORKING VLOOKUP.

Any help/hint would be much appreciated.

yogi_Using COUNTIF For Mult-Criteria Count With Data In More Than One Sheet

Google Spreadsheet   Post  #2351

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-22-2018
Countifs with month

I have a Master data sheet called Donées. In column C, I have the dates when the questionnaire was filled out. 

In the tab Janvier, I would like to pull specific data of all the different FOSAs based on the month of January but I keep getting errors. Please see formula in cell B3 of tab Janvier. Can anyone help me out?

Here is the Link to my sheet.

Thank you in advance


Sunday, January 21, 2018

yogi_Conditionally Format Rounded Numbers To OP's Specs

Google Spreadsheet   Post  #2350

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

Conditional Formatting on averaged numbers
SO I have a formula that averages numbers (it works great)

I have the decimal set so there is noting displayed after the number so it round up just like I want it do to so if my answer is 1.9333 it will display a 2

the problem is when I try to set conditional formatting

I want all 2 to have a yellow background 3 green and 1 red

Google looks at the entire 1.933 number instead of the 2 so it changes the color to red instead of yellow

I have tried text is exactly conditional formatting and it does not work appropriately.

I have tried the in between one also but it still looks at the 1.933 # instead of the 2 which is displayed in the cell

any ideas

yogi_Compute Average Subject to Specified Criteria For Each Student From Data In Another Sheet

Google Spreadsheet   Post  #2349

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-21-2018
Query by date, average, order by student, with one result per student

I have this spreadsheet set up to import form responses that show student performance over a date range:

I am trying to set up the sheet so that the "Query" tab pulls information from the "Progress" tab, providing one result per student/goal, and their average performance over a date range, based on a cell value (G1), using this formula:

=QUERY(Progress!B:E,"select B,C,D,E avg(E) > date '"&TEXT(G1,"yyyy-mm-dd")&"' order by B desc", 0)

Any help is greatly appreciated!!



Saturday, January 20, 2018

yogi_Troubleshoot And Fix Problem With Date String In Cell A2

Google Spreadsheet   Post  #2348

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-20-2018
Date text not being recognized as a date (i.e. Jan 01, 2018)
I'm trying to parse some text that gets automatically imported into my spreadsheet as a date, but that text isn't being recognized correctly. However, when I go to a blank cell and type the exact same value, it's being recognized as a date. Any ideas.

Here are two examples that I can't the spreadsheet to recognize as dates.
M‌a‌r‌ ‌1‌6‌,‌ ‌2‌0‌1‌8‌
=datevalue("M‌a‌r‌ ‌1‌6‌,‌ ‌2‌0‌1‌8‌")

Here's a link to a spreadsheet that shows the issue. Two identical formulas (as far as I can tell) get different results.

Thursday, January 18, 2018

yogi_Compute Row By Row Running Count OF Genres Read By Name

Google Spreadsheet   Post  #2347

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

Counting Unique Book Genres Per Student Form Submission

I am trying to keep a running total of unique book genres that students read.

Students will fill out a Google Form and for each submission that has a unique genre, I'd like the cell in the sheet to increase.

So, the first unique genre's cell would read 1, the second unique genre's cell would read 2.

When a different student submits, it would count that student's unique genre starting at 1 and increase with each new submitted genre.

Can anyone out there assist?

Here's a link to the Google Sheet.


yogi_Conditionally Format Column A If Neighboring Column B Houses Yes or ✔

Google Spreadsheet   Post  #2346

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-18-2018
Is it possible to create Conditional Formatting that uses a neighbouring cell's value?

Is there any way to create conditional formatting that depends on a cell OTHER than the one being formatted?

i.e. I want to change the formatting of a cell if the next door cell contains the word "Yes"  (or the  ✔ character).



Tuesday, January 16, 2018

yogi_Calculation Cell Referencing Across Multiple Tabs In A Spreadsheet

Google Spreadsheet   Post  #2345

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-16-2018
Calculation referencing multiple cells and across tabs
I have a spreadsheet with multiple tabs for inputs: Money we have received (Budget), Money we have spent (Actual Spent), and Balance (where I want to calculate).  All data is tied to a client name in column A.

What I want to do is be able to have a roll-up sheet (Balance) with a formula that will reference the input in Column A (client name) and in Row 1 (month/year) and calculate the difference between those values in the other tabs.  This would be used in cell H2 and copied down the line.

For example the sample client I have entered has a budget for each month in the Budget tab.
Actual spends are entered in the next tab.
in Balance, I want to calculate the difference between Budget and Actual as it relates to the client in column A, and if possible, reference Row 1 so that it will just look up those values so I don't have to mess with it down the road as long as the naming structure is consistent into 2018, 2019, and so on as we add dates down the line.

Here is the sample data I've entered into a sheet to share: 

Any help is appreciated!  I've been mapping out how to make this work and keep running into a wall.


Sunday, January 7, 2018

yogi_Split A String In Cell C2 To Pull Items In Master List (Cells D2:D)

Google Spreadsheet   Post  #2344

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-07-2018
Filtering an array where elements may occur within each other as substrings
I am trying to take an input string and return a set of substrings that are found within it. The issue is that my master substrings are very similar, and some contain the contents of others.
Here is a sample data set:
- ABC News
- CBC News
- MTV News

If I input "ABC and MTV News," I would like the return value to be "ABC" and "MTV News," NOT including MTV (despite the fact that it is technically a substring). I would also be able to input "ABC and ABC News" and have both returned correctly. Ideally I'd like to do it in a way that isn't limited to this set of data (so an if statement like "if MTV is found AND MTV News is found then..." would not be suitable). My intention is to use the filtered data for vlookup purposes, so hardcoding the data differently isn't really an option either. Another user suggested using regexextract and regexmatch and changing the master data, but I'm wondering if it may be possible to utilize them without having to do that.

Most critically, the input string is not restricted to a certain format. All of the following should be valid and return the same thing:

"ABC News and MTV"
"ABC News, MTV"
"I like watching TV. My favourite news channel is ABC News. I also like MTV."

All of the above should return "ABC News" and "MTV" as found strings, and exclude "ABC," as its only appearance is within an existing element. However, the following should also work:

"ABC, ABC News, and MTV"
"ABC News, ABC, MTV"
"I like watching ABC, especially ABC News. I also like MTV."

All of the above should return "ABC," "ABC News," and "MTV," with no exclusion of "ABC" because it was found independent of its appearance within "ABC News."

Sample sheet:

Input: B1
Master data: D and E
Vlookup sample: B13

Saturday, January 6, 2018

yogi_For List Of Cases And Reviewers In D2:D Assign Reviewer To Cases Evenly

Google Spreadsheet   Post  #2343

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

Assigning Challenge

Hi I have a challenge. 

In this sheet I have a list of cases in column A. I also have a list of reviewers names to the right. I want a formula in cell B2 that will read the reviewers list and assign a reviewer evenly to all the data by adding the reviewer's name into the B2:B fields. The result in this case would be Joe, Sandy, Mike, Joe, Sandy, Mike, etc going down from B2.

Also, as a twist, I want the formula to be able to do this regardless of the number of names in the reviewers list. I may add or subtract in the future.

What do you say?? :)


Friday, January 5, 2018

yogi_Match Column Names In Z1:AO1 With E1:Y1 And Pull Data From Matched Columns

Google Spreadsheet   Post  #2342

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-05-2018
Sheet w/input from Form combine like column names into single columns
Could you please offer a solution or ideas to solve this problem? 

 I have a daily sign in form to track attendance  for a community center which has daily classes Mon - Sat. Each day has a different list of classes, but sometimes a different day has the same class as a previous day. So for example:
* Mon has C1, C2, C3
* Tue has C4, C5, C1 & C2
==== In above example C1 and C2 are repeated on Tue but C4 and C5 are unique to TUE but may repeat later in the week.

FORM logic is as follows:
* Select Day (Mon, Tue, Wed, Thu, Fri, Sat)
---- Jump to section of form named for  day selected
---- Data posted to SHEET

Since I have multiple question sections, the data is put into separate column names for each section of the form, sometimes with duplicate event names. Each row only has entries in the columns related to it's section in the Google Form. All other cells are blank for the other sections. 

I'd like to create another set of columns in the sheet, with the names of each class as the first cell in the column, then have a formula following that parses all the cells to the left which contain form class data, and copies the cell contents if it's column name matches the column name of the cell containing the formula. So for example, Cell Z2 looks across cells E2:Y2 (answers begin in column E in this example)  and only copies the contents of a NON-EMPTY cell that matches the formula column name.  There will only be one cell with data in that row matching the formula column name.

This is similar to the problem statement expressed in the link below, except my sections are different but can contain some of the same column (field) names:

Any ideas? Thanks in advance for the help!

(Copies of example of the Form and Sheet are below, if needed.)

yogi_Compute The Date Subsequent To Which Cumulative Sun In Column G Is 6 or More And At Least One Cell In Column H Contains 'HLD'

Google Spreadsheet   Post  #2341

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-05-2018
Return a cell when an ongoing sum exceeds a value

The link to the spreadsheet:

I need help with a formula that will give me the most recent date when column G meets or exceeds 6. It also must check to make sure that within those dates, column H includes "HLD".

For the given data, the formula should return the date 7/12/2017.

I have tried using a query function, and an array formula, but I can't get it to work to add up to 6. Any help with this is much appreciated!

- Jesse

Wednesday, January 3, 2018

yogi_Conditionally Format Cells D:O If Value ThereIn Is Less Than Value In Column A Provided Column A Is Numeric

Google Spreadsheet   Post  #2340

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

Conditional Formatting of rows if less than a specific cell


This question must have been asked and answered somewhere but I've spent nearly an answer trying to figure it out by myself and from questions/answers and still cannot get it, even though it must be so simple. Any help at all is greatly appreciated.

I have a cash flow sheet which I'd like to apply a simple bit of Conditional Formatting to. I would like the text of cells in two rows to turn red if they are less than the value in cell E80. I would have thought that a simple "Format cells if... Less than... =E80" would work but, alas, not so. As you can see from the screenshot, the behaviour is quite random.

I've prepared an example spreadsheet demonstrating the issue here:

How can I get the behaviour I want?

Thanks in advance.