## Tuesday, December 27, 2016

### yogi_Formulate Query For Numeric Stats When Using Fields With Mixed NUMERIC and TEXT (or blank) Cells

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-27-2016
PROBLEM OF AVG_SUM_ONLY_NUMERIC Unable to parse query string for Function QUERY parameter

Same DATA - SAME QUERY
=query(DATA!A3:N126;"select M,SUM(G),SUM(H),SUM(I),SUM(J),SUM(K),SUM(L) group by M ";1)

The different is ONLY in the CELL I4  ( I Marked by green color)
in DATA I4=0  and QUERY is OK

But  in DATA2 I4 = blank  Result is Error
Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC

I heard about the  PROBLEM OF AVG_SUM_ONLY_NUMERIC
( many blank cells in the column   and QUERY consider as TEXT !!!!)

Thanks for any SIMPLE helps to SOLVE this problem

Dan

### yogi_Array Formula For Splitting Row By Row Entries In Column A Of Sheet 'T1 Cores'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-27-2016
REGEXTRACT() function to split data in an array stopped working
Hi all,

I've been using the function string =TRANSPOSE(ARRAYFORMULA(IFERROR(REGEXEXTRACT(" "&'T1 Cores'!S2:S26,"^"&REPT(" +[^ ]+",COLUMN(OFFSET(A1,,,1,25))-1)&" +([^ ]+)")))) for some time now to split data separated by spaces in cells in the range 'T1 Cores'!S2:S26 and it has stopped working properly now. All that is displayed is the first item in each of the cells in that range and nothing after the first space. Using the SPLIT() function with space as the delimiter works fine, but I have a large range of changing data so it's not practical to use SPLIT() since it can't be used in an array.

Any ideas? Here's a link to a sample sheet.

Please note that I have been able to use SPLIT function for an array of values

## Sunday, December 25, 2016

### yogi_WorkAround For An ArrayFormula Application Where Normally SUMIFs Function Would be Used

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-25-2016
How to convert a SUMIF to ARRAYFORMULA

I have this formula in Cell A2

=SUMIFS(E\$2:E,A\$2:A,A2,C\$2:C,C2,E\$2:E,">0")-SUMIFS(F\$2:F,A\$2:A,A2,C\$2:C,C2,F\$2:F,">0")

In cell A3
=SUMIFS(E\$2:E,A\$2:A,A3,C\$2:C,C2,E\$2:E,">0")-SUMIFS(F\$2:F,A\$2:A,A3,C\$2:C,C2,F\$2:F,">0")

And goes down just changes the "A2" to "A3" and so on for all my rows

How can I convert it to an array, so I don't have to copy down the formula every time I add a new column ?

Thanks !

## Thursday, December 22, 2016

### yogi_Condionallty Format Rows Where Emails Are The Same As Those in Tab Named 'Received'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-22-2016

# Data comparation & conditional formating

I have 3 tabs:
- Sent
- Report

There are new rows automatically created from another software In tabs Sent and Received.

I need to:

1. compare tabs Sent and Received and highlight green every row in tab Sent where is the duplicite email (in column C) found in tab Received.

2. Count higlighted rows in tab Sent and their number insert to tab Report

David

## Sunday, December 18, 2016

### yogi_For Data In Sheet Named Research Pull Into Another Sheet Certain Attributes For Select Entities

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-18-2016
HOW TO EXTRACT THE DATA TO ANOTHER SHEET WITH EACH NAMES HAS GOT THE MATCH VALUES ??????

## Friday, December 16, 2016

### yogi_Rearrange Data From Columns Into Alternating Rows

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-16-2016
Alternating Cells
I wanted to know if anyone has an idea how to accomplish something I am trying.  I can figure out how to pull data from one cell to another sheet, but What I want to do is have the data alternate cells in my destination  Does anyone know how to do this?

Basically I want to pull everything from Column A1 through A100, and place it onto a different sheet in Column A1, A3, A5, A7, A9 etc.

Edited

## Thursday, December 15, 2016

### yogi_Rank Entities In Column C Based On Their Performance By Attributes In Fields F E And G

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-15-2016
Determining Rank across 3 variables / Google Sheets and Stats
I have been stumped with this one all day -- hoping someone might help me. I just don't know where to begin

I need to create a weighted ranking system which will assign a ranked value as a percentage.

The first data set is an integer value of anywhere from 0 - Infinity ( just depends on the data at hand)
Second and third data set are percentage values of 1-100%

I have created an example sheet of my data -- you can see what I have in the link below

The three data sets of concern are the Treats Eaten, Obedience Score, and Friendliness Score.

Days in training do not matter.

I want to know which dog has the highest ranking based on those scores. The most important value to consider is obedience, the second most important value is most treats eaten, and the third is Friendliness.

I want to know which dogs have the best combinations of  highest obedience with the highest treats and the highest friendliness

Thank you so much!

## Sunday, December 11, 2016

### yogi_For Dates In Column A Assign Weekday And 'Commenncing Week Of ' Day Number Of Monday of The Week

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-11-2016
question by:Mike Kn0tt:

# How to update the date, weekly?

I know how to update the date daily ( =today() ) but is there a way to do this but weekly? I know I can set up scripts to do different things on a weekly basis but can't think how I would do this to make it change weekly and not change the date daily in between.

To explain further, if I was to use =today() on Monday it would show Monday in the cell with the date. The next day it would change to Tuesday and so on. But what I would like is a similar function but to only change weekly. On Monday it would show week commencing 7th, Tuesday it would still show week commencing the 7th, same would go for Wednesday, etc. Until the following Monday where it would change to week commencing 14th.

## Friday, December 9, 2016

### yogi_Pull From Sheet1 Data Only For Home And Away Teams That Are flagged With 'x'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-09-2016
question by:Aric K:
Looking for Help Filtering Data by Linking the Filtering Action to a Cell's Criteria
I'm looking to filter specific data out of a table when I mark an "x" in a cell.  For instance, if I have a list of names as shown below, I would like to enter an "x" in the cell, which would filter out the person's name from my table.  Any suggestions?  Thank you in advance for your help.

 x Mike Eric David Joseph Bill Jennifer Lisa Jeff

# Calculating Last Month's Income

## Monday, December 5, 2016

### yogi_From Table of dates And Amounts Compute Lastt Month's Income Average income And Current Month's Income

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-05-2016

# Calculating Last Month's Income

I'm trying to dynamically calculate the income for last month. There will continue to be a dynamic data set with new values each month, typically with multiple entries per month so the rows of data will continue to expand. I'm looking for the formula to always sum the total income for the month prior to this current month.

Additionally, I'm also looking to calculate the historical average monthly income.

I've prepared a mock sheet to illustrate what i'm looking for here: https://docs.google.com/spreadsheets/d/1lx8OT64Dzw0JsoWzOXIDVvX21PePTh0hvhxCXdyUMSc/edit#gid=0