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

Google Spreadsheet   Post  #2100

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

question by: eastsideholidays:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1NFT8NOiVwc;context-place=forum/docs

PROBLEM OF AVG_SUM_ONLY_NUMERIC Unable to parse query string for Function QUERY parameter

Please help me to solve this problem - Result of query

DATA  --- QUERY TEST2  IS HERE  https://docs.google.com/spreadsheets/d/1IJ2S2BsJiIFkjlSsvRNxmB0nil9pZeu9BNMirK7bo_k/edit#gid=0  

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'

Google Spreadsheet   Post  #2099

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

question by: "Ryan":

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/hCdfwwVjDf0;context-place=mydiscussions

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.

Thanks in advance!

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

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

Google Spreadsheet   Post  #2098

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

question by: SATH59:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/0vkWCc-IwFY;context-place=forum/docs

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 !

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

Google Spreadsheet   Post  #2097

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

question by: davieboy_cze:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/ZcgXkZIOq3o;context-place=mydiscussions

Data comparation & conditional formating

 

I have 3 tabs:

- Sent

- Received

- 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

Thanks in advance!

David

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

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

Google Spreadsheet   Post  #2096

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

question by: Debi Ferdian:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/viNtryDH76E;context-place=forum/docs

HOW TO EXTRACT THE DATA TO ANOTHER SHEET WITH EACH NAMES HAS GOT THE MATCH VALUES ??????

https://docs.google.com/spreadsheets/d/151f5U-oqwSOd2RZhaeU3b-2VilvGvP-bZ8YphGL0HXk/edit?usp=sharing

yogi_Rearrange Data From Columns Into Alternating Rows

Google Spreadsheet   Post  #2096

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

question by: mervin 123:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/O5FQNx14BwU;context-place=mydiscussions

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

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

Google Spreadsheet   Post  #2095

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

question by: JkBix:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/46SbX5K4iIs;context-place=mydiscussions

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

https://docs.google.com/spreadsheets/d/1ICMErqpGMHbIJUXC_Xmb-igns7VmeKjpWZRynd8Vk3E/edit?usp=sharing

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 

 Please help! 

Thank you so much!  

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

Google Spreadsheet   Post  #2094

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

question by:Mike Kn0tt:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/8ZhMoUdMdvM;context-place=forum/docs

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.  

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

Google Spreadsheet   Post  #2093

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

question by:Aric K:

https://productforums.google.com/forum/#!topic/docs/fBGgRCXyc8I;context-place=mydiscussions
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

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

Google Spreadsheet   Post  #2092

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

question by: jamesiii:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/g4j7bi_OopY;context-place=forum/docs

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

thanks in advance

(side note for anyone looking at this past the month of jan 17, feel free to add new data for the formula to update)

yogi_Check If Names In LIST2 Are In LIST1

Google Spreadsheet   Post  #2091

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

question by: allan starr:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/uGb8TtTyhHw;context-place=mydiscussions

a function for checking a list of names against a subset list of names?

Hi! I have a list of names in a spreadsheet, and a smaller second list of names which is a subset of the first. (both in two columns ie first name, surname). I need a function/formula that will simply check each full name in the complete list (ie both columns) to see if it appears in the subset list (which is also in two columns), and if it does, to note that somehow ie put some value/symbol/text in the column next to the name in the complete list. Any tips on which formula/function will do this? [If necessary I can merge the first name/surname columns but I'd rather not.]