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

=IFERROR(VLookup(TODAY(),Filter('Inventário'!$A2:$C,'Inventário'!$B2:$B=$A2),3,1),0)

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

=IFERROR(VLookup(TODAY(),Filter('Inventário'!$A2:$C,'Inventário'!$B2:$B=$A2:$A),3,1),0)

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
Hello,

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

Ulises 


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
Hello,

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

Best,

M

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.


Thanks,
Mike 

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?
Hello 

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).

Thanx

J