## Thursday, February 28, 2013

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
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

## Wednesday, February 27, 2013

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
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!!
---

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
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

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

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)
---
Yogi,

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
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?

---------------------------------------------------------------------------------------------------------
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
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)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 27, 2013
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

## Tuesday, February 26, 2013

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
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
---

...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?

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

following is a solution to the problem

### yogi_Compute Running Average Of Numbers In A Column

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
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)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 26, 2013
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.

Browser & Operating System (e.g. Firefox/Mac OSX): Windows 7  Chrome
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

## Monday, February 25, 2013

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

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