yogi_Compute The Number Of Instances Of Specified Weekday Between StartDate And EndDtate Excluding Holidays

                                          Google Spreadsheet   Post  #1058
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user SteveL321 said:(http://productforums.google.com/forum/?zx=3mq12ihdx8pi#!category-topic/docs/spreadsheets/2RK8TenKwVM)
Count number or occurrences of each day in a date range, less holidays.
I'm hoping someone can help me with a formula.  I need to know how many Mondays, Tuesdays, Wednesdays, .... Sundays occur in a given date range; less any holidays.

I've got the following:

E2 = Start Date

F2 = End Date

H2 = Day to count

Range of holiday dates = C2:C15  

I've found a few solutions to count the days, but can't find / understand what i need to do to  check it against the list of holiday dates.

Anyone know how to make this work?

I'd love to understand how the formula works as well.

Steve

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

in the following I have presented a step by step approach leading to the final solution ... a number of formulas used here can be combined together, however to meet the intent of the question poster, I have presented formula for each step separately although in the real world solution the formulas would be combined together

yogi_Compute The Number of Attendees (Row By Row And Total) From Their Initials Entered As Comma Separated Values In Another Column

                                          Google Spreadsheet   Post  #1057
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user heathnewton-felix said:(http://productforums.google.com/forum/?zx=fuio1wk47f5e#!category-topic/docs/spreadsheets/weRJUfT8en0)

Counting specific text in cells

I was just wondering if this could be done.

I have a column called "confirmed attendees" and a column called "# of attendees".

In 'confirmed attendees' column, there would be a number of different initials. For example - ab,cd, ef, gh etc.

Is it possible to have '# of attendees' count the specific text in the 'confirmed attendee's column?

For example:

ab = 1

ab cd = 2

ab gh ef = 3?

Can this be done?

Thanks so much for any help!!

---

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

I stripped down some information, but this what the spreadsheet is like.

a-i = just event info

I usually put initials in the J column separated by spaces (doesn't really matter) - en, th, br, rk, js and fc to be exact.

I would like the # of attendees to reflect the number of initials.  SO if there's ag and en, then it would be 2. If there is en th br, then it would be 3.

I will then take that and multiply it by the corresponding I column to give a total value in L.

Does that help?

Thanks Yogi.

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

in the following I have created a computed range K5:K where I compute the number of attendees and cell K5 also delineates the Total # of Attendees ... the computed range updates automatically and dynamically as new information is entered in range J5:J

yogi_Compute Sum Of Hits In Column C For Each Number In Column A And Date In Row 5

                                          Google Spreadsheet   Post  #1056
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user jmitquestion said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/6KqqlRZXTlU)
Index Match Match - Works on 1st Column, Rest fail....Confused 
Hi

I am trying to run a relatively simple index, match, match on a range of data. 

I have written the following formula and it works in the first column

=INDEX(FCF2!$C$13:$C$132,MATCH($A2,FCF2!$A$13:$A$132,0),MATCH(B$1,FCF2!$B$13:$B$132,0))

However when I move the formula to the right it fails and i get the error message error: Reference out of range

My raw data is laid out as follows

Column A - Number - Value between 1-5

Column B - Date

Column C - Any Value

Raw Data

Number date HIts

1 2/4/2013 751

1 2/5/2013 3227

1 2/6/2013 3016

1 2/7/2013 3123

1 2/8/2013 2761

1 2/9/2013 1404

2 2/4/2013 7511

2 2/5/2013 31227

2 2/6/2013 30216

2 2/7/2013 31213

2 2/8/2013 27361

2 2/9/2013 14404

3 2/4/2013 7512

3 2/5/2013 32

3 2/6/2013 30

3 2/7/2013 323

3 2/8/2013 231

3 2/9/2013 14434

4 2/4/2013 7432

4 2/5/2013 3423

4 2/6/2013 312312

4 2/7/2013 344543

4 2/8/2013 232

4 2/9/2013 140

DATA OUTPUT

2/4/2013 2/5/2013 2/6/2013

1 751 #REF! #REF!

2 8511 #REF! #REF!

3 7512 #REF! #REF!

4 7432 #REF! #REF!

5 13 #REF! #REF!

Any help would be appreciated

Cheers

John

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

following is a solution to the problem

yogi_Create A Sister Sheet yogi_Sheet1 To Automatically And Dynamically Order The Data In Sheet1 By Specified Column

                                          Google Spreadsheet   Post  #1055
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user Joseph Marshall said:(http://productforums.google.com/forum/#!mydiscussions/docs/LeUWuXZlNCc)

Auto Sorting

I'm trying to figure out how to auto sort the information that is imputed into the attached spreadsheet via a Google Form. I need it to be sorted by station code (column C) in alphabetical order. I can do it manually but auto sorting would save me time as I have the information pulled into another spreadsheet utilizing the =importrange function and would like to only reference that spreadsheet without having to go and manually sort new submissions. Is there a simple formula to use for this function to work? 

Attachments (1)

Safety Alert 02-13.xlsx

24 KB   View   Download

---

Yogi,

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

In the sheet all information is populated by a Google Form on a daily basis. In sheet1 I need all submission to be automatically sorted by "station code" (column C) in alphabetical order. I currently must go into each spreadsheet and manually sort the information or my Master Database, =importrange function will not reflect up to date sorted information. Overall, I just need all new submissions to be automatically sorted in sheet1 by "station code" (column C) in alphabetical order. 

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

yogi_Pull Students First And Last Name In The Form Responses Sheet From Another Sheet

                                          Google Spreadsheet   Post  #1054
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user cgchamberlain said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/MN4YhBjHA1Q)
Using Form/Spreadsheet as sign-in sheet.

I'm trying to create a quick cheap sign-in system for my library.

I've created a Form where students enter their ID#. On the backend spreadsheet, I have Form Responses, a Reference Sheet that has their student ID# in column a, first name in Column B, and last name in Column C. I'm trying to create an Attendance Sheet that will pull ID# from Form Responses look at Reference Sheet and put their name with it.

I have successfully done this using vlookup. 

The problem I'm having is that every time a new response is submitted, it does not auto-populate the Attendance sheet. 

Is there an easier way to do this?

Link to Form, Link to backend Spreadsheet.

Thanks in advance!

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

in the following I have pulled students First and Last Name within the Form Responses sheet

yogi_WorkAround For Coloring Cells Of A String (in column B) Red if Number of Characters In String Exceeds Specified Number

                                          Google Spreadsheet   Post  #1053
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user giathobbits said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/wGCvIHq9x3c)
Color cell if longer than X characters
I want to create a simple spreadsheet to input strings of text.  I would like the cells to turn red if there over 140 characters.  In over my head with scripting and don't even know where to start.
------------------------------------------------------------------------------------------------------------

as of now (Feb-27-2013) Google spreadsheet permits conditionally formatting cells based on values in the cells, this WorkAround colors cells in adjacent column A

yogi_Compute Exactness Of Amount Computed (column C) And Amount Actually Written Out (column D)

                                          Google Spreadsheet   Post  #1052
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
user rajvivek said:(http://productforums.google.com/forum/#!searchin/docs/rajvivek/docs/_RY-7Hw3120/HEBtK1CT4XQJ)
EXACT formua is not working expectadly in few of the cells...

I am trying to use a simple formula : =ARRAYFORMULA(EXACT(C3:C7, D3:D7)) but it seems to be not giving the expected result at few of the cells.

Please see this sheet. The cell E4 should be telling TRUE instead of FALSE. Please tell me both : WHY it is not giving the desired result, and HOW to fix it.

Thanks!

Vivek

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

the discrepancy is because of floating point arithmetic ... the numeric part of the entry in cell D8 is actually 17 characters long in contrast to 4748.1 apparently 6 characters long that it appears to be... so the following proposed solution

yogi_LookUp The Month_Year Combo From Row 5 For The Latest Available Value Of The Corresponding Metric

yogi_Count Instances Of Each of The Digits 0 Through 9 in A Column Of Numbers

                                          Google Spreadsheet   Post  #1050
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
user Michael_M_11 said:(http://productforums.google.com/forum/?zx=d9dujze9rels#!category-topic/docs/spreadsheets/vTH5BKDhbg0)
How do I count individual integers in a range of numbers?

I have a range a numbers, 1, 2, or 3 digit, and I need to know the frequency of 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9.

For example, if I have:

1

2

3

11

15

26

27

28

29

So the output of the formula applied to the range should tell me there are:

1's - 4

2's - 5

3's - 1

4's - 0

5's - 1

6's - 1

7's - 1

8's - 1

9's - 1

I think what I'm needing is pretty straight-forward, but I just can't seem to find it on the forums.

Any help is greatly appreciated!

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

following is a solution to a bit more generalized problem in that you can enter as many integers as you like in column A and the integers may consist of any number of digits

yogi_Perform Row by Row Count Based On Multiple Conditions In Another Sheet

                                          Google Spreadsheet   Post  #1049
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
user the malaise said:(http://productforums.google.com/forum/?zx=d9dujze9rels#!category-topic/docs/spreadsheets/5Z5vgnAO1ng)
How do I write spreadsheet function that references multiple variables for email tracking?

I am trying to learn how to create functions in a Google Spreadsheet that I will be using to track success rate of template emails. Here is the example: https://docs.google.com/spreadsheet/ccc?key=0AppkK6kryA8qdGNuTmtjdXZiUklBZEg1N3JWMWpmcmc&usp=sharing

What would the functions be for 'Template Tracking' page in C2, D2 and E2?

Essentially if a value exists in 'Email Tracking' column D then count it (if nothing there, then don't) and the create sum in appropriate place on 'Template Tracking' page based on 'Template #' in 'Email Tracking' column C.

For basic example, I used the function

=countIf('Email Tracking'!C:C;"Template #1")

for Column B of 'Template Tracking' page

---

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

...it seems to work great for what I am looking for, though cannot seem to process 'zero' quantity (puts 1 instead). Any ideas of a workaround for that?

Greatly appreciate your help.

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

following is a solution to the problem

yogi_Compute Running Average Of Numbers In A Column

                                          Google Spreadsheet   Post  #1048
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
user Jamie Greenberg said:(http://productforums.google.com/forum/?zx=d9dujze9rels#!category-topic/docs/spreadsheets/ybNa-0e_kGk)

Autofill Spreadsheet cells with Formula Question

Hi everyone.  

I'd like to display a "running average" in one column of my table, so each cell in that column would have the following formula:

=AVERAGE(D2:D2)

=AVERAGE(D2:D3)

=AVERAGE(D2:D4)

...etc.

I was under the impression that if I manually entered the first few rows, selected them all, and then dragged the mouse downward through the remaining columns in my table...it would correctly keep the "D2" field the same and increment the "DX" field as appropriate. Instead it seems to be incrementing both fields, so I have to go in and manually fix each row. Is there any way to do what I want with less manual work? In this example it's not a huge deal....but as the formula's get more complicated (and reference a static constant that's stored in a specific cell elsewhere in the spreadsheet, it becomes a huge hassle.

What am I doing wrong?

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

following is a solution to the problem

yogi_LookUp Data In A Table (in Schedules sheet) And Compute Pricing In Another (Overview sheet)

                                          Google Spreadsheet   Post  #1047
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
user Peggy Albiz said:(http://productforums.google.com/forum/?zx=d9dujze9rels#!mydiscussions/docs/WFnC6HdJeMo)
Converting and Excel formula to work in Google
This formula works perfectly in Excel but when uploaded into Google Doc's it comes up with an error.  I did not create the spreadsheet but I am responsible for maintaining it.

How do I correct the uploaded Excel formula so that it can be used in the new Google Spreadsheet.

=HYPERLINK("@lookup(B9,UserVolumeDiscount,Schedules!B5:B12",USD_PerUser*(1-LOOKUP(D12,DiscountTable,DiscountRate))*D12)

Browser & Operating System (e.g. Firefox/Mac OSX): Windows 7  Chrome
Google Apps account 
Describe the steps you took that produced this error: Imported an excel spreadsheet.
Copy and paste any error messages:   #NAME? error

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

following is a solution to the problem

yogi_LookUp An Entity Name Such As Nationality From A Table Based On Values In Two Columns Such as First name And Last Name

                                          Google Spreadsheet   Post  #1046
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 25, 2013
user Plentira said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/5CNv7fipSls)
Help with formula to cross-reference values
Hello!

I would have used the search function, had I been able to formulate my question in a succinct manner. :-)

My workbook has a sheet in which all participants in a course are listed, including their nationality (sheet A). Another sheet (downloaded externally), has a list with all participants that have successfully received a certificate for the course (sheet B) - alas without their nationality.

I am trying to write a formula that would search sheet A for names present on sheet B, and when found add the corresponding value for 'nationality' in sheet B.

Is this possible? Thanks in advance for the help.

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

following is a solution to the problem