yogi_Count The Number Of Persons Who Finished After 10:00 PM And Put The Count At End Of Entries In Column B

                                         Google Spreadsheet   Post  #1581
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-31-2014
post by SiP5002 (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/6p4Fc0XLKO0)
If time is above formula

Hi,

I would like to know how to do a formula that will total up the amount of people in a column that finish at 10pm or later and put the total at the bottom. Is anyone able to help me?

Thanks

Simon

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

how exactly Simon wants to present the result is not clear ... so let me see if the following illustration will do

yogi_Compute Row By Row Number Of Work Days in Column L Based On Maximum Work Capability As Delineated In Cells E2:I2

                                         Google Spreadsheet   Post  #1580
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-30-2014
post by Sedonafilmer (https://productforums.google.com/forum/#!mydiscussions/docs/nLE3Q2dJrx8)
Using MAX formula in an array formula

Hello,

I'm making a spreadsheet that calculates out of different work types it will display the largest amount of workdays required.

I'm trying to get the max value listed in a column based on the multiple calculations in several columns. The MAX function does what I need it to do but the arrayformula will not automatically calculate the following rows.

 =ARRAYFORMULA(MAX(E2:E/400, F2:F/100, G2:G/200, H2:H/500, I2:I/500))

This only calculates the first row. I need it to auto fill the rest of the rows without doing a click and drag all the time. Do I need to use a different formula?

Thanks,

Richard

---

Hi Yogi,

Here is a copy of the sheet:

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

I'm creating a working form that will input the quantity and type of a customers order then display how many days the project will take to complete (based on which item will take the longest)

Columns E through K are the data input of the quantities (the note on row 1 describes the quantity per day each type is)

The max function listed above gives the correct result per row, however I need column L to auto fill as rows will be added and deleted throughout the day (I've done this with arrayformulas).

Hope that clarifies! 

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

yogi_Mark Y In Columns C And D If Entries In Sheet1 Exist In Sheets 'AHREfs Links' and 'OSE Links' Respectively

                                         Google Spreadsheet   Post  #1579
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-28-2014
post by vangolfer (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/B1t9CwcYUco)
Referencing a cell within a query (find a duplicate between sheets)

I have a list of links from one source, and I'm trying to see if each one already exists in my lists from other sources: https://docs.google.com/spreadsheet/ccc?key=0Ait1Kc7l3ZVIdGZuM3h2TXg1Z1kzR3p3cHhEWTltSUE&usp=sharing

I *think* the best way to do this is with a QUERY. I'm able to find a hard-coded string, but when I try to reference a cell in my query, it doesn't work. Based upon another discussion (https://productforums.google.com/forum/#!topic/docs/M3y6VJcaRio )

I've tried this:

=QUERY('AHREFs Links'!$A$2:$A$900,"select * where A = " & A2, "")

(and of course countless other combinations) But no luck.

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

yogi_Compute Stats In Form Responses From Commuter Survey Responses

                                         Google Spreadsheet   Post  #1578
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-28-2014
post by dtwilder (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/2gcB5SNEaxY)
calculations on data from form

I'm trying to develop a commuter survey to capture driving distances, mile per gallon, vehicle type and other variables to be used to calculate total emissions for my office building. I keep getting tripped up on how google handles form responses in that calculated fields I add to the response spreadsheet don't calculate for new responses. I did see a thread on this forum about having to use an "arrayformula" but I'm not sure that''ll work for me.

Also, I want the user input form to use a lookup table for one of the questions but it does not seem to be grabbing the correct value from the look up table.  

The link for the spreadsheet is below if anyone has time to lend a helping hand. Thanks.

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

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

yogi_Compute Time Worked In Minutes During WorkDays Given WorkBegin And WorkEnd DateAndTime And WorkStart And WorkEnd Hours

                                         Google Spreadsheet   Post  #1577
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2014
post by Anuj Rastogi (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/IoXZDpc6oIU)
NETWORKDAYS formula showing incorrect results in Google Sheets however it is working fine in Microsoft Excel

We have been using Google Sheets to calculate the "minutes" between two dates as per Normal office working hours (8:00  - 17:00) on working days (Mon - Fri). Google sheets are showing different incorrect result than Microsoft Excel using the same formula.

These are the two dates:-
Cell(AE41)        03/16/14 19:28:00       
Cell(AF41)        03/17/14 14:15:00

Formula used in Cell (AG41)

((NETWORKDAYS(AE41,AF41)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(AF41,AF41),MEDIAN(MOD(AF41,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(AE41,AE41)*MOD(AE41,1),"17:00","08:00"))*24*60

The output Google sheets is showing 855, which is incorrect.
The output in Microsoft Excel is 375, which is correct.

Please suggest some workaround or provide a fix of this issue.

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

I have not checked Anuj Rastogi's formula that he said gives correct result in Excel but not in Google spreadsheet ... however

please look at the solution to a bit more generalized problem I have presented in the following

yogi_Compute Row By Sum of Counts In Multiple Columns And Running Total of Counts In All Columns

                                         Google Spreadsheet   Post  #1576
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2014
post by Tj Mesler (https://productforums.google.com/forum/#!msg/docs/aGkiePJhMqM/34uhWbfK66gJ)
How to get a formula copied to new row?

Ok, I'm a spreadsheet newbie and have done some research but just don't understand how to do what I want.

I have a formula in column H that sums up the values in the previous 5 columns. I want it to expand down the sheet for each row added.

So i tried =ArrayFormula(SUM(C2:H2)) but it doesn't get expanded into any new rows added.

Sample sheet is here:

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

Bonus question : I would also like to keep a running total for each column, but then adding rows gets harder. Is there a way to add rows 1 above the last row?

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

yogi_Apply Conditional Formatting For Cells That House Letter A or AB (Text red) , That House Letter B (BackGround green)

                                         Google Spreadsheet   Post  #1575
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-25-2014
post by Pyth (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/vRaKaw0dvMA)
Multiple conditional formatting rules applied to same cell?
Let's say I have a column where some cells are going to say "A", some "B", and some "AB". I want all the cells containing "A" to have red text, and all the cells containing "B" to have a green background. So I apply two formatting rules to the column: text contains "A" -> red text (no change to background), and text contains "B" -> green background (no change to text).

Result (in old Sheets, in new Sheets, in Firefox, in Chrome, here or there, anywhere, in a house, with a mouse, in a box, with a fox): Cells that say "A" have red text. Cells that say "B" have a green background. Cells that say "AB" have... red text, but no green background.

I do not like it, Sam I Am.

I could solve this simple toy problem by changing the rules, but in the more general case where I want cell backgrounds to be controlled by one set of rules and cell text colours by another set in ways that might have complicated interactions, there doesn't seem to be a way to easily accomplish it using conditional formatting. And yet, it seems like the sort of thing that should be obviously and trivially possible.
-----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull Column By Column Sum Of Amounts Paid By Each Month Of The Year From data In Form Responses 1

                                         Google Spreadsheet   Post  #1574
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-25-2014
post by Mr Matt Cook  (https://productforums.google.com/forum/#!mydiscussions/docs/QzAZb5kfPJw)
Help with forms and sheets needed please.

Hi. I've tweaked a template of a spreadsheet for my own households income/expenditure and a form for our business.

I have no scripting knowledge, (and don't even know if that would be relevant).

I have a cell each month on a sheet which has business income and a form that I will use 'in the field' to input jobs and income from those jobs etc.

I would like to know how to have the sheet automatically updated month by month with the info supplied by the form.

I hope this makes sense.

If you can help I'm sure I can answer any more questions you may have.

I would like this spreadsheet to be LIVE to use by April.  Please help me.

Matt

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

the following is a solution based on my best understanding of what Mr Matt Cook is trying to accomplish

yogi_Count Row By Row Number Of Attendees In Column E By Categories In Column C

                                         Google Spreadsheet   Post  #1573
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-25-2014
post by Colby Mecher  (https://productforums.google.com/forum/#!mydiscussions/docs/FYQse3oQWqI)
Trying to add cells in rows that meet certain criteria

I am getting married, and we are working on our guest list.  What I would like to do is be able to easily know how many people are coming from each category (my friends, her friends, familes, mutual, etc)  

https://docs.google.com/spreadsheets/d/11JvpC3gD7gkk_aPpmu8uN6ceiRxrMkkcct9qoBkyqRI/edit#gid=0

I currently have a COUNTIF formula counting the category each family, or person fits into.  However, to make this work we would have to add a line for EVERY family member, which would take too long, and we will not be sending out an invitation to everyone so it would be easier to have a line for each family, or person who will be receiving an invitation.  

What I want is for the "Colby Guests" cell to: 1) Determine which row(s) applies (i.e. has "Colby") and then 2) add the number in the "reception" column for that row.  

 

Is this possible, or is there an easier solution that I haven't thought of?  

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

yogi_Given Birthday And Test Day Date Compute Age In Years As Of Today And As Of Test Date

yogi_Given Birthday And Test Day Date Compute Age In Years As Of Today And As Of Test Date

                                         Google Spreadsheet   Post  #1572
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-25-2014
post by Eileen Rasmussen  (https://productforums.google.com/forum/#!mydiscussions/docs/lOLvSROOiNU)
Sheets formula for subject age at time of testing
Hey all,

So I've Googled this pretty well and come across a few similar questions, but none of the suggested formulas have been working.

Basically, we have a biiiig google excel sheet with some subject data, and we want a column that shows each subject's age at the time they were tested. Each subject already has a cell that has the date they were tested, and their birthday.

When I try:
=round(((A1-A2)/(365),1) 
Where A1 date tested and A2 is birthdate? 

or

=round((abs(A8-B8))/(365),1)
Where A8 is date tested and B8 is birthdate

I get parse errors. Not sure why.

+ a few other formulas have yet to work.

 I cannot for the life of me get this to work, although it seems so simple! Haaaalp :(

Thanks in advance!

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

yogi_Compute Row By Row Average Score In Column L For Q1_Scores In Columns G and J

                                         Google Spreadsheet   Post  #1571
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-24-2014
post by CathyL16 question by GimelG  (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/1udpQ-HOvjw)
Alternative to CONTINUE function in new sheets

Hi everyone,

Is there a way to automatically copy a formula down a column without showing the "#N/A" or "#DIV/0" and without having to continually copy for new rows? 

https://docs.google.com/spreadsheets/d/1tv2c8VtHKyPnl-GL87EK7hwU4t-evKXUdV6UN31r6L8/edit?usp=sharing

I posted previously and was helped tremendously in setting up the formulas in the "Combined" sheet. Is there a way to have the formulas copy down? It seems to work well for the first cell, but not the others. I tried to use the ARRAYFORMULA function, but that didn't work. I've seen the CONTINUE function work in the old sheets, but read that this was discontinued in new sheets.  I'm using the new version of sheets as that allows me to have two forms links to the same spreadsheet. 

Is there a workaround?

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

yogi_Pull Most Recent Unique Goal Combinations For Specified home Team

                                         Google Spreadsheet   Post  #1570
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-24-2014
post by marcfonteijn (https://productforums.google.com/forum/#!mydiscussions/docs/qcGDd0vNsHU)
Combining Unique and Filter?

this post is in addition to my earlier blog post
yogi_Pull Row By Row  Date HomeTeam AwayTeam HomeGoal AwayGoal For Unique Set Of Scores
http://yogi--anand-consulting.blogspot.com/2014/03/yogipull-row-by-row-date-hometeam_22.html

based on my best understanding of what marcfonteijn is looking for

yogi_Make A Population Chart In Google Docs Spreadsheet -- A Formula Based Solution

                                         Google Spreadsheet   Post  #1570
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-22-2014
post by whsigs (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/rBdJETOE14Q)
Making a population pyramid by reversing the bar chart so the axis is on the right
'm accessing Google Docs on Google Chrome

I am trying to make a population pyramid with sheets. A population pyramid looks like this:  http://upload.wikimedia.org/wikipedia/en/6/6a/Population_pyramid_example.svg

As you can see, it is essentially two bar charts facing in opposite directions.  I have made a sheet to calculate the data which is here:  https://docs.google.com/a/weldre4.k12.co.us/spreadsheet/ccc?key=0Aixg512qAqFgdHZMU25SUExtNEZKUmcydGM2YVhCTmc#gid=0

The problem with sheets is that I cannot see how to make the male side of the pyramid which is the reversed bar chart.  I have an excel template that does this but I would like to try to keep this on Google Drive for better sharing access and to embed in a site.

Thanks,

Steve

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

I present tin the following a formula based solution

yogi_Pull Row By Row Date HomeTeam AwayTeam HomeGoal AwayGoal For Unique Set Of Scores Filtered By HomeTeam

                                         Google Spreadsheet   Post  #1569
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-22-2014
post by marcfonteijn (https://productforums.google.com/forum/#!mydiscussions/docs/qcGDd0vNsHU)
Combining Unique and Filter?

this post is in addition to my earlier blog post
yogi_Pull Row By Row  Date HomeTeam AwayTeam HomeGoal AwayGoal For Unique Set Of Scores
http://yogi--anand-consulting.blogspot.com/2014/03/yogipull-row-by-row-date-hometeam.html

based on my best understanding of what marcfonteijn is looking for

yogi_Highlight Row (or part of a row) If A Specified Word Occurs Anywhere In The Row (or part of the row)

                                         Google Spreadsheet   Post  #1568
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-20-2014
post by Dustin Hawkins (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/-HRvlSDtmjM)
I need for rows A-M to change background color for 3 different texts

Greetings all. 

I'm very inexperienced with code and am having trouble modifying the provided formula to suit me.

Here's what I need:

If the word "no" is in a cell, I need for that row's background color to be red from columns A-M

If the word "yes" is in a cell, I need for that row's background color to be green from columns A-M

If the word "special" is in a cell, I need for that row's background color to be yellow from columns A-M

I attempted to change the formula provided earlier in the thread to execute these actions, but failed after multiple attempts.
Anyone know how I can modify the existing formula or what I need to do create a new formula that performs all of these actions within a single sheet?

Thanks for your help!

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

yogi_Pull Row By Row Date HomeTeam AwayTeam HomeGoal AwayGoal For Unique Set Of Scores

                                         Google Spreadsheet   Post  #1567
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-20-2014
post by marcfonteijn (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/qcGDd0vNsHU)
Combining Unique and Filter?

How can I use the result (2 column) of a UNIQUE function as the input for a FILTER function?

I'm using something like this:

=UNIQUE(D2:E16)

And now I have to manually enter the cells in multiple filter functions. Of course this won't work for long as my unique function will be returning different values. Show how can I make the function here below dynamic?

={FILTER(A2:E16;D2:D16=D19;E2:E16=E19);FILTER(A2:E16;D2:D16=D20;E2:E16=E20);FILTER(A2:E16;D2:D16=D21;E2:E16=E21)}

I've made an example sheet that has some sample data that I'm working on.

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

---

This function:

=FILTER(A2:C,MMULT((A2:A<TRANSPOSE(A2:A))*(B2:B=TRANSPOSE(B2:B)),SIGN(ROW(A2:A)))=0)

Described at https://productforums.google.com/forum/#!topicsearchin/docs/filter$20unique|sort:date|spell:true/docs/AbNGpZ5NqXI is almost what I'm looking for with the difference that I need to work on 3 column not 2... Is there a way to make the this function work with 3 columns?

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