yogi_Adjust Sumproduct Formula Used In Google Spreadsheet So That It Will Continue To Work When Spreadsheet Is Downloaded Into Excel

                                          Google Spreadsheet   Post  #1379
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 28, 2013

user Pat Gilbert (http://productforums.google.com/forum/?zx=kq47eb7b00d7#!mydiscussions/docs/weoTzEAnypE)
How to save a google spreadsheet as excel with Formulas
Is there a way to retain formulas in a google spreadsheet that is saved as xlsx? I want to save my completed speadsheet to my desktop and remove it from Google Drive....
I'm using Win 8 with Mozilla Waterfox browser.
---
Yogi,
Her is the link to the sample spreadsheet,

https://docs.google.com/spreadsheet/ccc?key=0ArUt_HBqvxZkdFRmcm95cWRtRVRRdGpFTFpKaWIzUVE&usp=sharing

Cheers
Pat
-------------------------------------------------------------------------------------------------------------------------------------------

yogi_For Budget Spreadsheet Set Up Amount Expended Sheet For Each Month of The Year

                                          Google Spreadsheet   Post  #1378
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 27, 2013

user  Al_searching (http://productforums.google.com/forum/?zx=lenf7csdi1jt#!mydiscussions/docs/Z6tl_yFaAIs)
Data sorting by month + category
Hello Everyone,

This is for a monthly budget spreadsheet associated with a google form.

I'm using the following formula to pull data from the form tab to another tab in the same spreadsheet : =SUMIF(Category,A65,Price)

where Category = a specific category from the associated google form, A65 is the cell in which that same category is displayed on the second tab, and price is the price sum added. 

For instance if there are 4 items of $3 each in the category "Groceries" that have been entered in the form, the number displayed in the cell with =SUMIF(Category,A65,Price) is $12

Now what I'd like to do is create several tabs, one for each month of the year, and have the data from the Google form sorted in each tab according to the month.

For instance all the items entered in september go in the "september" tab, all the items entered in october go in the "october" tab and so on.

Do you know what formula I need to enter along with =SUMIF(Category,A65,Price) so that it also filters the data according to the month ?

Thanks, any help is appreciated

---

Thanks for your answer Yogi! I'm not that familiar the QUERY function. Here's the link the the spreadhseet : https://docs.google.com/spreadsheet/ccc?key=0AsCa64sWf_X5dG1SU0gwVlpvOXVFUUZhY3BkUHg4QlE&usp=sharing

Thanks in advance and have a good one!

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

yogi_Workaround For Applying Conditional Formatting When Cell Values Needs To Be Compared With Values Of Successive Following Cells

                                          Google Spreadsheet   Post  #1377
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 23, 2013

user  Scott DK (http://productforums.google.com/forum/?zx=uyr15nwjbsi3#!searchin/docs/Scott$2C$20DK%7Csort:relevance/docs/sDlsUhAywNw/bcOcebkszM8J)
Can you do IF THEN formulas in Google spreadsheets?
I'm trying to compare cell A1 with cell A2.  If A1 is greater it turns red.  If A2 is greater it turns green.  Is this function possible in Google Docs?
--------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull The Corresponding Color From Column B Of database If A Part Of Entry In Column A of index Matches Entry of database Column A

                                          Google Spreadsheet   Post  #1376
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 21, 2013

user dyegovasc (http://productforums.google.com/forum/?zx=auqs2gn0ypq8#!category-topic/docs/spreadsheets/hF1wD9ULYjQ)
autocomplete column based on string search from another sheet (maybe VLOOKUP) Google Spreadsheets

I`ve been trying to solve this for a while but without success. Maybe someone can help me.

PROBLEM: when a cell in column A contains a string from column A from sheet 'database', then autocomplete cell in column B with corresponding value from column B in sheet 'database'

I need to autocomplete column B in sheet 'index'

Ive published a spreadsheet explaining better what Im looking for.

Thanks!

https://docs.google.com/spreadsheet/ccc?key=0AjT7-UeYDyR7dEpBMERvMENiOFRSdlZodTM0bzZRM0E&usp=sharing

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

yogi_Set Up To Show Comma Delimited CheckBox Items In Form Responses Sheet In Separate Columns

                                          Google Spreadsheet   Post  #1375
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 20, 2013

user kchan88 question by Joe Janiga via comment in associated blog post (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dfsF4qjIp2c)

by Joe Janiga

Nicely done. Would there be any way to instead of having for example ADVENTURE TIME #6- P200 and DAREDEVIL #15- P150 nicely delimited within the same column as you've done, have them each occur in their OWN column. So in effect NEW COMICS column would have as many columns needed for as many new comics that arise.

yogi_Count Instances Of A Specified String (Word or Phrase) In A Range Of Cells

                                          Google Spreadsheet   Post  #1374
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 19, 2013

user Sandy Joe (http://productforums.google.com/forum/?zx=x8ts4l41ygu7#!category-topic/docs/spreadsheets/DXbul9-gIYY)
How can I count the occurrences across a range of cells of a word within a paragraph
I would appreciate any help on how to do this since I've been racking my brains and searching like crazy on an easy way to accomplish this.  Basically, I have multiple columns of data where each cell contains a paragraph of text.  I would like to get a count of the number of occurrences for a specific string contained within the paragraph.  For example, if the string I'm looking for is "class" then for a defined range of cells, I would like to get a count of how many times this string appears.  For any given cell, this string could appear multiple times.
--------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull Values From Responses Sheet For A Given Row And Column And Then Drag Down To Pull Values From Every Second Successive Column In The Corresponding Row

                                          Google Spreadsheet   Post  #1373
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 19, 2013

user Greg B. Johnston (http://productforums.google.com/forum/?zx=x8ts4l41ygu7#!category-topic/docs/spreadsheets/iG9SJ8AMu7Y)
Pasting formula and choosing how many rows to increment?
Here's something like my data: https://docs.google.com/spreadsheet/ccc?key=0AoiOHFjFsZRfdGlONzFhYTlObU9YZFJUTkJCb2pIenc&usp=sharing

The goal is to pull data from the 'Responses' sheet columns C/D, E/F, and G/H into the 'Results' sheet as I have it. The purpose is to have each organization's numbers in a separate row in the 'Responses' sheet, but the same row in the 'Results' sheet. The problem is that when I drag down or copy and paste the formula, it increments the row references by 3s, rather than by 1s.

I'll look at the solution you posted and see if I can figure it out.
-----------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Count Instances Of Numbers In Column A That Are Greater Than And Less Than Respective Specified Numbers

                                          Google Spreadsheet   Post  #1372
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013

user mike losey (http://productforums.google.com/forum/?zx=8h90ovzom3m#!category-topic/docs/spreadsheets/FMSmBt1Byzc)
COUNTING VALUES THAT FALL IN A SPECIFIC RANGE
I have a set of numerical numbers ranging from 0-150.  I would like to count how many fall in between a specific numerical range (i.e. how many entries fall between 100-130).  I have tried to do it using a countif formula but I have not been able to get it to work.  The formula I attempted was =COUNTIF(A1:A6;">100") but I am not sure how to make it stop at 130.

I would appreciate any help you can provide.

Thanks.
------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull Into Another Sheet Every Nth Row Starting From A Specified Row Number Of Form Responses Sheet

                                          Google Spreadsheet   Post  #1371
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013

user Greg B. Johnston (http://productforums.google.com/forum/?zx=8h90ovzom3m#!category-topic/docs/spreadsheets/iG9SJ8AMu7Y)
Pasting formula and choosing how many rows to increment?

I have a set of formulas in merged cells that each cover three rows. I want to be able to copy and paste the formula to a spot three rows lower, while only incrementing the cell number in the formula by 1; I'm referencing another sheet, so it's important that I only go down one row of data at a time.

In other words, I'll paste something like ='Form Responses'!A2 into row 5. I want ='Form Responses'!A3, not ='Form Responses'!A5.

Is there a way to do this?

I'm using Google Docs on Firefox/Windows 8.
----------------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Apply Formula Using LINEST Function To A Dynamically Expanding Set Of Entries In Column E

                                          Google Spreadsheet   Post  #1370
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013

user meph2u (http://productforums.google.com/forum/?zx=64ecnzx8oga3#!category-topic/docs/spreadsheets/DTPS5zVkZuE)
I want to change a range using a value in a cell to reflect when rows are added. How can I use a formula to determine the last row in a column where there is data?

Lets say there are rows of data on columns A-E and I have a formula in F1 (=linest(E1:E25)) that requires knowing the last row.   Can I get a formula to put a value in H1 that is the final row in the E column (in this case 25) and then put that into the function in F1?   Note:  linest does not accept (E1:E) as a range.

I searched for an answer and didn't find one.   But if the solution to this is published and you can point me to the thread, that would be great.

Thanks

---

Here is a spreadsheet

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

End goal:  When I add data in row 26, I want the formula in F1 to change from line linest(E1:E25) to linest(E1:E26)

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

yogi_Return Row1 Value of The Associated Column Where A Specified name Occurs

                                          Google Spreadsheet   Post  #1369
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013

user malvaoo (http://productforums.google.com/forum/?zx=aa774cqrbnbg#!category-topic/docs/spreadsheets/BRu_U2OoER0)
HELP: Formula to return only ROW 1 values that contain a specific value in their columns?
Example: A1 has "Event 1", B1 has "Event 2", C1 has "Event 3." 

Let's say "John" is listed somewhere (could be anywhere) under Event 1 and 3, but not 2. I want a single formula to return all the events John is listed under. If the formula is in G1, I want it to return "Event 1" in G1 and "Event 3" in H1 (Event 2 is skipped).

Any help? :D
------------------------------------------------------------------------------------------------

yogi_Compute Number Of Approved Status Items By WeekDay From A Table Of Person WeekDay And Approval Status

                                          Google Spreadsheet   Post  #1368
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013

user Mo S (http://productforums.google.com/forum/?zx=12tctimpyndk#!category-topic/docs/spreadsheets/ud1L2SvDIpc)

Query function to count rows that has both: 1) contain a string in one cell and 2) contains specific text in another cell

Here's what my sheet looks like (based on form responses).

Person	Availability	STATUS
Bob	Mondays, Tuesdays	approved
Joe	Mondays, Wednesdays
Fish	Mondays, Tuesdays, Wednesdays	approved

Here's what i want the results to look like:

RESULTS (on another sheet))
Mondays	Tuesdays	Wednesdays
2	2	1

Basically, the query function needs to count how many ppl are available on those days that are also approved.

The query function i currently use does not consider the 3rd column becuz i dont know how to make it:s

Any help appreciated. Thanks.

I am using firefox/win 7
-----------------------------------------------------------------------------------------------------------------------------------------------------------

yogi_WorkAround For Conditional Formatting For A Range Of Cells Based On Number Of Comma Separated Items In A Cell

                                          Google Spreadsheet   Post  #1367
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013

user Aram Donabedian (http://productforums.google.com/forum/?zx=12tctimpyndk#!mydiscussions/docs/x0kQMy_tOeI)
Using Conditional Formatting with Commas
I was wondering if there was a way to assign conditional formatting based on the number of comma separators in a cell?  IE: A,=green B,C,=Blue D,E,F,=Yellow

The letter values won't always be fixed and will be different from cell to cell so I want the number of commas in the cell to be the condition.

Can I do that?

Thanks
---------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute SUM COUNT And AVERAGE Of Amount For Specified Item (or all Items)

                                          Google Spreadsheet   Post  #1366
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013
user Steve Nordstrom (http://productforums.google.com/forum/?zx=ktl89mk9ta16#!mydiscussions/docs/oLtTAejtd0s)

Example from a budget spreadsheet - I would like to filter a single recurring item, see the total spent on this item, and calculate the average spent on this item for a week/month/quarter/year, etc, and calculate the percent this item represents to a total spent for a week/month/quarter/year.

Apparently, Sheets cannot do calculations on filtered subsets of a column of data.

---

Well, it seems that I cannot drag a Sheets file from Google Drive to this attach window.

Sample base document
Item	Amount
Food	$55.00
Gas	$25.00
Utilities	$100.00
Food	$47.00
Gas	$15.00
Utilities	$95.00
Food	$63.00
Gas	$31.00
Utilities	$87.00
Desired Filter features
Food	$55.00
Food	$47.00
Food	$63.00
Filter items total	$165.00
Filter items listed	3
Filter items average	$55.00

There!  That worked.

All I want (which I can get in Excel and Open Office) is some statistical data from a filtered column.

In the example above I want some info on each of the budget items listed.

So I filter for each one consecutively.

I am showing "Food" as the filtered item in the example above.
I want to see the total for all the entries in the filtered data, the number of entries found, and the average amount of the items based on the number of entries listed. 

Simple.

Except if I set these formulas up at the bottom of my budget spreadsheet, when I apply a filter for a particular item, the formula cells do not see the filtered items, they still see all the entries in the entire column. (even though I cannot see them at this point)

I hope this helps.

Maybe I am missing something that is already there.

Educate me.

Thanks.

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

yogi_Compute Total Score For Specified Candidate For Latest (or specified week) For An Open-Ended Number Of Weeks

                                          Google Spreadsheet   Post  #1365
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 09, 2013
user Joy Bird (http://productforums.google.com/forum/?zx=i955ropbiggh#!mydiscussions/docs/86hQ_qA4WGc)
query inside of a sumif/sum(filter
I want to count scores of students for the entire year, but also on a weekly basis.

I need a function that will sum the scores associated with the students' initials, AND that correspond to the max number in row 1. So basically, sum if column A = BJ AND row 1 = the max so far.  

So far, I used the query function to sum the scores in the entire table if column A = BJ.  I used a separate function to count the scores in row 3 if row 1 = the max so far.  But I need to combine the two so that I don't have to count row by row.  

https://docs.google.com/spreadsheet/ccc?key=0AoBn7ck7bJXzdGRuYjR6bkRuSnUtcTdxcTluVmVWWlE&usp=sharing

---

Oops, I changed the sharing, so it should work now.  The equations are on the sheet, but I'll put them here too.

sum(query(Data!A:H, "Select * where A = 'BJ'"))  - This gives me ALL of BJ's scores.

sum(filter(Data!3:3;Data!1:1=(Max(Arrayformula(Data!1:1)))))  This gives me BJ's scores in row 3 that are from the second (most recent) week

I need an equation that gives me BJ's scores from the most recent week.

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