yogi_Compute Values In Columns Per Specified Criteria Then Sum Up Specified Columns By Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #522    Apr 29, 2012     www.energyefficientbuild.com

user swickedtwc said:
spreadsheet form pulling data form sheet1 to sheet 2 and calculating it 
HI,

I have a spreadsheet form that helps students define their learning style i.e Visual, Auditory, or Tactile. The data is collected in sheet1. I want to add calculations to each question and then sum the results identifying the students learning style. I have tried import rage in to a second spreadsheet which worked but did not update unless I had the sheets open. then I tried simply using sheet2 to pull the data from sheet1 and calculate it but the calculations change because of the way new answers to the form are inserted. So now I'm messing with =ARRAYFORMULA, but have not been able to get it all to work. I want sheet2 column1 row 2 and down to contain column AB row 2 and down.  I want a column for each question to contain a calculation that looks like =if(Sheet1!D2="Often",5, if(Sheet1!D2="Sometimes",3, if(Sheet1!D2="Seldom",1))) but then continues to grow as data is inserted. I tried this =ARRAYFORMULA(if(Sheet1!C2="Often",5, if(Sheet1!C2="Sometimes",3, if(Sheet1!C2="Seldom",1)))) and it gave me the right answer but did not grow to the row below it.

Thanks for any help you can be,

Chad

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

I don't have access to the user's spreadsheet(s) ... however, in the following I have provided a solution for the components that Chad would need for his real world project:

yogi_Publish A Dynamic Range Of A Single Sheet Of A Spreadsheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #521    Apr 29, 2012     www.energyefficientbuild.com


user adventruresincubing said:
google spreadsheet w/ limited embed range
I have embedded a Google Spreadsheet into a Wordpress blog, using widget=false, single=true, range=XXX, etc. to get rid of the title/footer, etc.  It embeds as expected.
One quirk I can't figure out surrounds the range variable.  I want only columns A through H, with all of the rows in columns.  I had expected to be able to put "range=A:H" into the iframe code and have it work.  No dice.  The only syntax that works is something like "range=A1:H20."  That functions fine, but isn't practical: If I add more rows, I need to update the embed code.  Is there a wildcard that I can put instead of row numbers to get the embed range to capture the WHOLE of the selected rows?
Thanks.
------------------------------------------------------------------------------------------------------
there is no direct way of publishing a dynamic range ... in the following solution I present a workaround for  publishing an expanding range

yogi_Count The Number Of Instances Of Non-Blank Items In A Column

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #520    Apr 27, 2012     www.energyefficientbuild.com

yogi_Extract Dash Separated Values Into Comma Separated Values In Quotes And Also In Individual Cells

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #519    Apr 27, 2012     www.energyefficientbuild.com

yogi_Share Different Views Consisting Of Different Columns In A Spreadsheet With Different Contacts

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #518    Apr 26, 2012     www.energyefficientbuild.com.

user shiroise said:
Share only selected spreadsheet columns INSTANTLY + SECURELY
I need to create a main spreadsheet with 4 different views.
All views have the first 5 columns.
Each view then has additional columns from the main spreadsheet.
I make each of these 4 vies  accessible to different contacts (total is only 7 persons)
So ContactA can see view 1 = First 5 cols + col 6, 7 8.
ContactB can see view 2 = First 5 cols + col 9,10,11.
etc
I need my colleagues to access the info they need in real time. I do not want to have to download and send a copy - this is what I do already.
I have seen a suggestion to use Hide but then I would have to remember to use Hide every time I make a change in order to keep data securely hidden. Sooner or later I would forget or my assistant would!
Quite a lot of enquiries sem to have been made about this function. Is there something I've missed in my searches?
---------------------------------------------------------
there is no direct way to share multiple views of the same spreadsheet ... following is my Workaround solution to the problem

and here is an image of the spreadsheet

from the spreadsheet, I created 4 (four) spreadsheets ... one for each view -- View_1, View_2, View_3, View_4 as depicted in the following

yogi_Sum Up Amounts By Month And Year From A Table Of Dates And Amounts

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #517    Apr 26, 2012     www.energyefficientbuild.com.


user Hoopsnl said:
Sum per month in a specific year 
Hi,
Hopefully someone can help me;
I have several dates in a spreadsheet and I want to have the monthly total per month per year, my table;
Date                Total
(MM/DD-YYYY) 
01/02/2011            100
01/08/2011              5
02/02/2011              6
02/08/2011              6
09/02/2011             10
01/02/2012             12
01/22/2012             12
04-06-2012              5
04-08-2012              5
12-10-2012              6 
12-25-2012              6
Result should be:
Month      Total
Jan 2011     105
Feb 2011      12
Sep 2011      10
Jan 2012      24
Apr 2012      10
Okt 2012      12
Who can help ?
----------------------------------------------------------------------------------------
following is a solution to the problem ... in Sheet1 I have shown Month numbers instead of Month names

yogi_For Time Given In hh:mm:ss Show It Supressing Hour Where Specified Time is Less Than 1 Hour

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #516    Apr 25, 2012     www.energyefficientbuild.com.

user yv6eda said: 
Hello guys/gals. 
A rookie here, please be patient with me. Please help me with this. I have a list of video clips each one with a cell displaying length in time. Format used is HH:MM:SS. Everything works, but I want to hide or remove de HH part if the video is under an hour. 
Example: 
Current: 00:05:23 
Hope: 5:23 
Sorry is the question is dumb... Thanks in advance... 
Jorge
-----------------------------------------------------
following is a discussion on and solution to the problem
 

yogi_Using SUMIF Function And Wild Card * With Date Entries In Google Docs Spreadsheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #515    Apr 25, 2012     www.energyefficientbuild.com.


user Firevelociraptor said: 
Google Excel Sum If Help I would like to do a sumif function that is like Excel's wildcard. 
This is the example: =sumif(B:B,"*January*",D:D) So that, the search in column B will come up with the corresponding prices in column D even when the date is formatted to include the day and year. 
Thanks. 
April 
--------------------------------------------------------------------------
following is a discussion on and a solution to the problem
 

yogi_Work Around For Conditionally Formatting Dates In A Column Based on A Formula-1

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #514    Apr 24, 2012     www.energyefficientbuild.com.

user GavinP said: 
I require the cell to turn red if the date is any time between today and 20 days in the future. For example, today is 24/04/2012, so I need the cell to be red if the date is between 25/04/2012 and 14/05/2012 (20 days in the future). The spreadsheet is designed to warn me if a date has either passed or is coming up 20 days in the future. 
Thanks! 
--------------------------------------------------------------
following is a solution to the problem -- Please note that I changed TODAY to specified date to have generalized solution This solution is an extension to the solution I had posted in response to the user's similar question -- see my blog post: http://yogi--anand-consulting.blogspot.com/2012/04/yogiwork-around-for-conditionally.html

 

yogi_Sum Up Data In Specified Column Between Two Dates And Present By Year And Month

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #513    Apr 23, 2012     www.energyefficientbuild.com.

user BobRoss said:

Formatting date number to keep trailing zero for October to not confuse with January 

Please consider:

=query(importrange(ʺ0Ao4BSCJLCiKUdEpvUXliSXQxZC1Wb2h6TlI2MFZ5amcʺ, ʺData!A:Cʺ), 

ʺselect Col2, sum(Col3) where Col1 is not null and Col1 >= date 'ʺ & text(A13, ʺyyyy-mm-ddʺ) & ʺ' and Col1 <= date 'ʺ & text(A14, ʺyyyy-mm-ddʺ) & ʺ' group by Col2 pivot year(Col1) + (month(Col1) +1) * .01ʺ)

Note the pivot clause:

pivot year(Col1) + (month(Col1) +1) * .01ʺ)

Which can produce the groups:

2011.1 (October 2011)

2011.01 (January 2011)

Problem:

2011.1 may seem like January 2011 to some users.

What I'm trying to do:

Display 2011.1 as 2011.10 to avoid confusion.

Can you help?
-------------------------------------------------------------------
as of now, QUERY function in Google spreadsheet does not support Format option ... as a workaround
following is a solution to the problem

here is the data we are going to work with ...

yogi_Work Around For Conditionally Formatting Dates In A Column Based on A Formula

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #512    Apr 19, 2012     www.energyefficientbuild.com.

user GavinP said:
Google Spreadsheet - Conditional Formatting Question
I have a spreadsheet containing various values.  One column, column I (i) contains a list of dates. 

I need each date to appear RED if

1) The date is Today (I can do this)

2) The date is any date prior to Today (I can do this)

3) The date is 20 days in the future from Today (I can't do this)

1) and 2) are easy to do. I can not for the life of me work out how to do 3) on Google Spreadsheets.  There is no simple option to click 'If the date is 20 days in the future...'.  

Could someone help me please?  

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

following is a solution to the problem ... since one can not use directly a formula for conditional formatting, I have colored cells of adjacent column J in place of cells in column I

yogi_Duplicate Data Rows For Double Entry Accounting

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #511    Apr 19, 2012     www.energyefficientbuild.com.

user adam_sirum said:
yogi_Duplicate Data Rows For Double Entry Accounting

Hi,

My company tracks employee reimbursements on google spreadsheets with each line equal to one reimbursement.  However inorder to import these reimbursements into quickbooks (double entry accounting), each reimbursement needs to be converted into 2 lines.  I don't want to use a non-array formula as there are thousands of entries and I need it to by dynamic.  Please see my example spreadsheets.  I will be forever grateful.

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

Thanks in advance!

Adam

---------------------------------------------------------------------
following is a solution to the problem ... I haveshown the original data rows in sheet yogi_Sheet1 and I have duplicated the rows in sheet yogi_Sheet1-Duplicate Entries

yogi_Pivot Data By Month And Assign Month Names To Month Numbers

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #510    Apr 18, 2012     www.energyefficientbuild.com.

user BobRoss said:
Month number to month name

Hello:

Please consider:

=query(A:C, ʺselect B, sum(C) group by B pivot month(A)ʺ)

How would I display "month(A)" (a month number taken from a date) as a month name?

Also, I'm having trouble putting the where/filter clause "where A is not null" in the right place.

Any help on these issues would be great!

Thanks!

BobRoss

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

following is a solution to the problem

yogi_Extract Rows That Do Not Contain A Specific Term

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #509    Apr 18, 2012     www.energyefficientbuild.com.

user brewcrew said:
Deleting rows based on a column containing a specific term
I have a spreadsheet contained over 3000 rows. 

My rows look like this

brewery          item code           description

dogfish          000001              dog-btl 

dogfish          000001              dog-6pk

dogfish          000001              dog-case

flying dog       000002              flyingdog-6pk

flying dog       000002              flyingdog-btl 

flying dog       000002              flyingdog-case 

ommegang         0000003             ommegang-6pk 

ommegang         0000003             ommegang-btl 

ommegang         0000003             ommegang-case 

I want to delete all rows that contain 6pk in the description column. I am new to the the google functions and having trouble figuring out how to write a formula to do this. can anyone help?

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

following is a solution to the problem

yogi_Pivot The Data In A Table And Reconfigure Per Specification

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #508    Apr 18, 2012     www.energyefficientbuild.com.

user Dark_tower said:
Row problem with Form Submissions

Here's what I've got.

Group of people; For instance

John

Joe

Jane

Julie

Each person has a list of things to do;

Modules

1

2

3

4

(there are a LOT more people and a LOT more modules).

What I'm trying to do is make it so where, each submission will add to or create a column on the same row as the first submission.

EG: John Completed Module 1

Upon Johns next submission, instead of adding a new row that says John : Completed Module 2, I am trying to get it to place the word "Yes" into the column that is labeled "Module 2".

Regardless which item type I use, it always adds a new row, is there a way around this?

Example.

Name       Module 1     Module 2

John           Yes               Yes

instead of

Name     Module 1     Module 2

John           yes

John                              yes

or 

Name     Module

John           1

John           2

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