yogi_Delineate in The Active Cell Row Numbers Of Rows In The Active Column In Which The Active Cell Is Duplicated

                                          Google Spreadsheet   Post  #900

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 30, 201

In response to a question that user Michael asked in my following blog post:

yogi_Highlight Rows That Have Duplicate Content In A Specified Column 

http://yogi--anand-consulting.blogspot.com/2011/11/yogihighlight-rows-that-have-duplicate.html

because I thought that Michael's question stands on its own, I present a solution to the problem in the following 

yogi_Group In Sheet2 The Sum Of Values In Column D By Month Of Dates In Column C Of Sheet1

                                          Google Spreadsheet   Post  #899

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 29, 2012

user Nathan Gibson said:(http://productforums.google.com/forum/?zx=fgswwk4cu5xw#!category-topic/docs/spreadsheets/Oa3NjbWNVIY)

get the sum of a cell depending on the DATE value of another Cell 

I have spreadsheet1 with column C listing a date in MM/D/YYYY format and Column G listing a dollar Amount. 

I need a formula to parse speadsheet1 and find all dates in comlumn C for January and add Column G and post it in spreadsheet 2. And so on for each month

Spreadsheet 1  <----What I got

C                      G

1/1/12               $5.00

1/5/12               $5.00

2/5/12               $10.00

2/12/12             $7.00

3/15/12             $2.00

5/22/12             $9.00

6/16/12             $6.00

Spreadsheet 2:  <-----What I need

A                B

January       $10.00

February     $17.00

March         $2.00

April            $0.00

May            $9.00

June            $6.00

I am not a spreadsheet master but I was working down this path but haven't really got anywhere:  =ArrayFormula(sum( (month(C2:C)=9)*E2:E ))

Any help would be appreciated.

-------------------------------------------------------------------------------

I have a hunch you want to group in Sheet2 sum of values in column D by month of dates in column C of Sheet1 -- for this ... for this I have presented a solution in the following

yogi_Sum Values In Column G Of Sheet Realizacija/podjetja For The Month Name In Column A Of Sheet Zaslužki

                                          Google Spreadsheet   Post  #898

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 28, 2012

user Pergas Si said:(http://www.blogger.com/blogger.g?blogID=8807805591547673516#editor/target=post;postID=4178660158141334476)
Question about formula (if month...)
Hello,

I have a question about formula in google docs and this is how it goes:

In column A I have dates (1.12.2012, 22.11.2012,...) and in column D I have values which are earnings on that day. I want that in A1 on other sheet, it counts all the earnings in selected month.

Thank you

---

https://docs.google.com/spreadsheet/ccc?key=0Am4TqJJ8aHUWdG1HZ2hrV0VrNzd5d3ExaTYycGF5a2c#gid=0

Ok here is the spreadsheet. 

So in sheet "realizacija/podjetja" there is in the column A (Datum) is date and in column E there is "provizija".

In sheet "zaslužki" there is column G (provizije). I would like that this column has a formula so it gathers information from Klici!E:E based on month of the year. So SUM of E in november.

I hope you understand me...

---------------------------------------------------------------------------------

following is a solution to the problem ... based on user's locale, the month names are as they would be in Slovenia

yogi_Extract Sales Records In Sheet 'Each1' From Sheet 'Each' by Specific Dates But Exclude Canceled Sales Logged In Sheet 'CXL'

                                          Google Spreadsheet   Post  #898

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 28, 2012

user Chris Sansone said:(https://productforums.google.com/forum/#!mydiscussions/docs/bpQ-ZJeSAio)

How to exclude rows against an array? 

I'm working on a bunch of sheets that manipulate info from a master sheet with a bunch of information about orders.

It starts out by using QUERY statements to pull the master sheet apart into different date ranges, which then get picked apart by more QUERY statements to more sheets for analysis.

What I want to do, is have a list of cancelled order numbers in its own sheet and exclude rows with that order number in some of the queries.

What's the syntax for this?

---

Here's the link to the sheet:

https://docs.google.com/spreadsheet/ccc?key=0AmwoqTvyX6YadGRGVWZZN01rMWZOUFF2U29ZTGlYZWc

Actually that's a small bit of sanitized sample data.

So info about each order is entered into the first sheet, named "each"

the sheet "dates" has periods of time, which are pulled into QUERY statements in cell A1 on the numbered sheets:

"each1" "each2" "each3" "each4" respectively.

Other stuff happens from there, I left a few sheets but they aren't important. What I'd like to happen is for the four numbered "each" sheets to pull rows from the main "each" sheet within the date frame (works already) but also where the order# in column B does not match any order number in column A of the sheet named "cxl"

"cxl sheet would be a maintained list of orders that were cancelled for some reason, and I don't want them included in any further calculations in this part of the operation.

----------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Capture Minimum and Maximum Of The Values That Are Successively Entered In Cell A1

                                          Google Spreadsheet   Post  #897

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

user Domi Paravasilinus said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/fFvgcW2fz9Y)
Two probably simple to answer questions

New to using Docs/spreadsheets and could use a little help with a couple items:

1. How do you create a cell that "keeps track" of the highest and lowest values that were ever put into another cell?  Example, cell B16 gets its single number data updated every so often.  Each time it updates, I want another cell to display a range indicating the lowest and highest values ever entered into B16.  If the updates were made in this order: 12, 5, 12, 92, 12, 106, 32, I would see the tracking cell range shift as follows: "12-12"->"5-12"->"5-92"->"5-106". 

2. How do you conditionally change a cell's background color depending on whether the value in the cell is greater or less than the value in another cell?

Thanks very much for any help!

---------------------------------------------------------------------------------------

this is in reference to Domi's first question

There are no native formulas to store successive entries in cell A1 for one to be able to recall what was ever put into a cell... so for what you want to accomplish you will have to explore doing it with Google Apps Script.

if you are open to making successive entries within cell A1 with a delimiting character, such as a space character, a comma, a tilde, etc. then for that I have provided a solution in my following 

yogi_Computer Number And Amount Of Transactions Conducted After Specified Hour

                                          Google Spreadsheet   Post  #896

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 26, 2012

user mqoue said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/BDWGuGnxbVE)
Managing Timestamp Data
Hello,

I have a small sample of timestamps transaction that I'd like to get some information out of.  I'd like to figure out what percent of these transactions took place after 5 pm? and I'd like to see what percentage of payout vs total was paid out after 5 pm

The link to the data is right here:
https://docs.google.com/a/mqoue.com/spreadsheet/ccc?key=0AlbhsLTIt0yMdEhXaGlVV1UzUk9MOGFiSFc3WW5EMkE#gid=0

Thanks in advance,

Robert
---------------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem

yogi_Remove Extraneous Characters Such As Space ~ * # From Columns Of Numbers

                                          Google Spreadsheet   Post  #895

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 26, 2012

user Jesper Hasteen said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/mgY5cx74-GA)
I'm importing information (just with copy/paste) from another program and they have written spaces in the numbers.

Google sheets doesn't recognize these as numbers because of the spaces, I tried removing them with find and replace all " " but that only removes spaces in words, not numbers.

Because I have so many of these numbers I can't do it manually so is there another way?

This is what the imported info looks like (obviously without dividing them into cells here):

Market Transaction 213 500 345.26 2 725 244 440.47

Transaction Tax -315 000.62 2 511 744 095.21

------------------------------------------------------------------
following is a solution to a bit more generalized problem in that the proposed solution will remove most extraneous characters such as space ~ # * etc.

yogi_TimeSheet Related Calculations In Computed Columns In Form Responses Sheet

                                          Google Spreadsheet   Post  #894

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 26, 2012

user Eddieb0s said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/BmCqesnFcGI)
Auto calculate a complex daily timesheet
I am developing a somewhat complex daily timesheet for my employees.  I have linked a copy of it here for anyone of you developers out there to help me with.

To Edit/View: https://docs.google.com/spreadsheet/ccc?key=0AlM5ix7hZdekdDBDNy1COG0tWXpfRGlfV1V3NTN3Znc

Everything works great!  Columns AT-BF automatically calculate the employees payout, HOWEVER, when a new form entry is added I have to copy those formulas to the next line because the form adds a new row!  It's quite tedius!  Is there a way that I can get the spreadsheet to automatically pull the formulas down when a new form is submitted?

-----------------------------------------------------------------------------------------------------

there are a number of redundant columns in this spreadsheet -- however I have made no attempt to reduce the number of columns; I have marked the computed columns with light brown background color and I have shown the formula that needs to be used for each of the computed columns -- ideallt the formula for the computed column should be written in row 1 so that the formulas are aploicable from Get Go.

yogi_Compute Stats For FIFE S.T.A.R.S Observer Evaluation Form

                                          Google Spreadsheet   Post  #893

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

user JesseBas said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/I8VaQVYGDos)
How to count data on a Google Spreadsheet based on an identifier
Hi there, I'm trying to figure out how to count data on a Google Spreadsheet based on an identifier (a participant's email address). The spreadsheet was generated by Google Forms. The form first asks the participant email, and the asks them if they completed a number of tasks (some tasks will be completed more than once) . Some of the questions are checkboxes and others are comment boxes, I only need to count the checkboxes. What I need is a formula that will search through every column of the spreadsheet, and add to the count only if a participant's email matches the row with their name, and if they completed that particular task. On a new sheet (same Google Spreadsheet file however), every participant will have a row to themselves, and the columns will consist of the tasks that may or may not have been completed. For example, ja...@google.com completed task x and y twice but forgot about task z. Therefore in the row titled "Jane" there would be a 2 in column x and y but a 0 in column Z. I believe this is possible with a COUNTIF function with multiple criteria but I heard that this isn't available on Google Spreadsheets. Does anyone know how to do this? Any help would be much appreciated, thanks,

- Jesse
---
Hi yogia,

Here is the link to the Google Spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0AiL5hXlzV2PzdDRZWU9WOXZvTksxN0RXbFd1dDlsRUE

For reasons of confidentiality, it is not the original file; it's a copy where I've deleted all the participants and created 3 "test" names.

The purpose of the form/spreadsheet is to evaluate the history taking skills of medical students in practice sessions. The "Observer" checks off and comments on the tasks completed by the medical student in the presence of a mock patient. Once the Observer submits the form, a script automatically sends it to the student's email. However, after multiple practice sessions, we want to be able to send out summed data so that the student knows what tasks he/she is consistently missing.

This summed data will appear on the sheet titled "Summed Data". Every student will have a row to themselves and the columns will consist of every checkbox for every task. So far I've only added the "Introduction" checkboxes, which are: "Introduces Self", "Shakes Hand", "Patient ID", "Chief Concern". There is also an attendance column that counts the total number of entries that contains that particular students email address (as this same spreadsheet will be used for multiple practice sessions). Note also that after the first session the order of the email addresses will change, as it depends on the order that the Observers submit the form. The formulas that should count the tasks are in the "Summed Data" sheet, columns D-G. I have experimented with some formulas, so far with no success.

Please let me know if you have any questions and are able to help. Thanks again,

- Jesse
-----------------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Query Data Conditionally To Compute FirstChance Or SecondChance Grades As Specified

                                          Google Spreadsheet   Post  #892

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

user Juan Bozzo said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/n13iR-0y5fU)
QUERY breaks under conditional data source
I`ve noticed that QUERY sometimes loads wrong cells when the data source (the range) of the query is not fixed. I've made an example sheet to show the issue; https://docs.google.com/a/uc.cl/spreadsheet/ccc?key=0Ar2-w83T2UgmdEpOMFc1eDV3cU5aOU5wcWZvaW1taGc#gid=3

To give a little context to the sheets: Imagine you are a teacher that want to publish some exam grades. You have corrected the exam and filled up a table with details of the points earned in every item. Then you publish that table, some students show you that have not been graded correcty. You fill other table with the new grades of only the people whose grades changed. Now you need to merge that data.

So you have four sheets. One that gives every student an id. Other two that have the grades and points of the firsts and second opportunities you graded, they are the almost sheet but with different data. And a fourth sheet that merges the data of the previous two sheets. The way the second sheet works is using a boolean per row that tells a query function if it has to load the row from the first data sheet or the second. When the bool is false, everything works ok, but if the boolean is true. The query function loads the data of the all previus rows. All the detail of how to reproduce this in the sheet itself.

I know this problem can be solved using other methods but is just an example to show the issue with QUERY.
---------------------------------------------------------------------------------------------------

I found that the QUERY function works if it is used correctly ... I have provided a solution to your problem in the following 

yogi_Find The Number Associated With A Name In A DropDown List

                                          Google Spreadsheet   Post  #891

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

user Beno Pileggi said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/6nF8amG7MiM)
Question about data
Ok well, I am creating a question about spreadsheets, and I have a question about data.

In A1 I have a drop down menu with 10 separate values. For each of those values, there is another corresponding value. I would like the corresponding values to appear automatically once some one selects a value from the list in A1.

Thanks in Advance,

Ben

---

Here is the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0ArRsrugbajYadEdQalVyNDlSSXhXcEpoX0lSWEFNRlE&pli=1#gid=0

I wrote a further explanation in the spreadsheet.

With Thanks,

Ben 

---------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Find Associated Name And Number In A Cell And Put The Number Into The Column Dedicated To The Name

                                          Google Spreadsheet   Post  #890

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

user Guy Pilkinton said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/_mUbRmTuHvk)
Find a name and number in a cell and put the number into the column dedicated to the name.
Hi I want to Find Names and numbers in a cell which includes other text. Then I want to put the number into a cell column headed with the name. 

I want a funtion that will mean L4 reads "7", M5 reads "4", and N6 reads "7".  Is there a function that will automatically do this for me?

-------------------------------------------------------------------------------------------------

following is a solution to the problem ... there is a slight discrepancy in your expected response, so I have altered the names in row 1 to match your expected response

yogi_Pull Data From Form Responses Sheet Into Sheet2 And Setup Two Computed Columns For Row By Row Sums Of C:D And D:E

                                          Google Spreadsheet   Post  #889

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 24, 2012

user Ron01 said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/iZ7I2Wi7ItQ)

how can i overcome the problem that a form delete my sheet formula on other columns then the form suppose to edit?

Hi,

I have this issue:

i created a form using spreadsheet.

in the form i have many rows and columns that i want to execute a function (formula) on,

after every row was submitted by the form.

in this scenario i have 2 issues i would like to solve:

1. after entering the 1t submitted form, i insert the formula, that i want to be duplicated to every new submitted form.

   the only option i saw i can do it is by copy the formula to X rows in advance manually.

   is there a way to make the formula copied automatically every time the form is submitted?

2. when i copy the formula manually down to empty rows, every time a form was submitted, it deletes the row before placing the data inside,

   so what it does is delete my copy formula, even that they are in cells that are out of the form range on that row.

Does any 1 know how can i overcome this issue, and make the form or the spreadsheet enter the correct formula without edit or delete it?

(i already try to make the calculation in sheet2 on sheet1 data... still have the same issue there).

Thanks.

Ron.

---------------------------------------------------------------------------------------------

in response to first question from Ron, following is a solution to the problem

yogi_WorkAround To Apply Conditional Formatting For Condition 'Cell Is Not Empty'

                                          Google Spreadsheet   Post  #888
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 24, 2012
user Clown Guy said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/l7Gq8mloYR4)
Feature Request For Google Sheets - Conditional Formatting - 'Cell Is Not Empty'
Hi,

An almost embarrassingly simple but important feature request.

There is currently a conditional formatting option for 'Cell is empty' but not an option for 'Cell is not empty' or 'Cell contains data'.

There is an option for 'Cell contains' and its opposite 'Cell does not contain'. There is an option for 'Cell is empty' but not its opposite.

This is a very simple but powerful feature that allows any cell with any data in it to be formatted in some way.

Is this the right place to post this feature request and if not, can someone help by posting a link to where I can propose this feature request?

Thanks

Clown Guy

-----------------------------------------------------------------------------------------------------

following is a simple WorkAround solution to the problem

yogi_Group Data By Customer DayOfOrder And Quantity And Include Interior Columns As Specified

                                          Google Spreadsheet   Post  #887
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 24, 2012
user thangle said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/sv6CWFKl7TA)
Sum or Subtotal using multiple conditions with Arrayformula
Hi guys,

I have a detailed list of product that my customers has ordered. Each of product is has the basic of informations: customer, day of order, productID, quantity.

ColumnA         ColumnB   ColumnC ColumnD         ColumnE

Customer Day of order   No      Product code Quantity

A Nov-1 Product01                                   7

A Nov-1 Product02                                   5

A Nov-1 Product03                        30

A Nov-1 Product04                        10

B Nov-3 Product02                         5

B Nov-3 Product03                                  25

...

My goal is to have summary table that list all the order by customer and the day-of-order, with total quantities of each order. 

ColumnG         ColumnH   ColumnI ColumnJ ColumnK

Customer   Day of order   No      Note   Quantity

A          Nov-1                            42

B          Nov-3                 30

...

One simple way is using Google Sheets Pivot table feature, but that's not what I want this time.

My draft function using query() with filter() does work:

L3 = if(G3="","",iferror(sum(query(filter(A$3:E,A$3:A=G3,B$3:B=H3),"select Col5")),""))

But how to achieved that results using Arrayformula() ? I'm still struggling understand MMULT() and can't let it work out. Maybe MMULT() is the solution for me?

This is my simplified spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AuVRjZ-x_VAldFhfRExHNVZRNEpVSmRLUGRhNlRybnc

Happy weekend!

Thang.

------------------------------------------------------------------------------------------

 following is a solution to the problem