yogi_Compute Row Number Of Nth Negative Number Up From last Occupied Cell In Column A

Google Spreadsheet   Post  #2103

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-01-2017

question by: hilman.chung:

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

How to find cell in column with the 12th negative value from the bottom?
Hi,

I like to find the index of a cell within a column that is the 12th negative value from the bottom (last populated cell).

Can someone help me?

yogi_Convert Fraction To Percent

Google Spreadsheet   Post  #2102

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-01-2017

question by: Ms Drasby:

https://mail.google.com/mail/u/0/#inbox/1595c05266f8c196

yogi_Convert Fraction To Percent

yogi_Conditionally Format In Column B Top 3 Unique or Non-Unique Entries as noted

Google Spreadsheet   Post  #2101

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-01-2017

question by: Matthew Farquhar:

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

How do I custom format the 1st, 2nd, and 3rd highest number in a column?

I am running a small sweepstakes competition, using google sheets so everyone can access it. I want it to automatically update who is in 1st, 2nd and 3rd by changing the color of their cell. Placings are based on who has the highest amount of points. What is a way I can do this or the closest I can get to doing this?

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