## Thursday, October 31, 2013

### yogi_Compute Number Of Unique Employees Working on A Task By Month

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 31, 2013
Counting number of Employees. Help!!
Here is a link to what it is I am trying to do:

There are 3 sheets:
First: the "Employee Production Tracking" Sheet - this is what I am trying to have count the number of different employees that completed tasks each month.
Second: the "Task Log" - This is what the employees will be filling in. They put in the task name (which is irrelevant for what I am trying to track on the first sheet), the name of the employee who completed it, and the month the task was closed in.
Third: The list on employees in Column A and which "division" they work for in Column B (as I need to track this as well).

I need to count how many different employees completed tasks in each month. Then I need to break it down and count how many different employees from each division completed tasks in each month.

For Example: in July, 6 tasks were completed, but what I need to know in Column B of the Employee Production Tracking sheet is that 3 different employees completed tasks in the month of July on this project. And then for columns C and D I need to know how many (of those three employees) were from division A and from division B. On the Employee Lists sheets it has a running tab of all employees working on this project in column A and what division they work for in column B.

I used [=COUNTUNIQUE(FILTER('Task Log'!B:B,'Task Log'!C:C="January"))] for the total number of employees for each month, BUT for the months that nothing has been completed yet it is returning a value of 1. How do I have it return a value of 0 for those months where nothing has (yet) been completed?

Is this possible or am I asking too much of Google?? :)

Thanks in advance for your help, I really appreciate it as I am stumped on this one.

Jessica
-----------------------------------------------------------------------------------------------

### yogi_Compute Row By Row Age In Years For BirthDays in Column A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 31, 2013
Automatically Fill in Age Upon Entering Birthdate
I'm wondering how you would automatically fill in the age upon entering birthdate.  Thanks for your help!

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

## Wednesday, October 30, 2013

### yogi_Count The Number Of Students By Type of Lunch Desired On A Specified Date

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 30, 2013
Count number of entries for a specific day
We are trying desperately to generate a lunch count that will only count the specific entries for the current day.  The screen shot has a formula of =if(B2:B=today(),count(C2:C,counta("Hot Lunch")),0) which is returning a 0 instead of 2 for the Daily Hot Lunch Total.  Please let me know what I am doing wrong or if there are any suggestions that will work better.

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

### yogi_Count Row By Row Instances Of Column C Names In Columns H And N Of Sheet Named responses

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 30, 2013
count how many times the name in Column A appears in my form response sheet "responses"
I need to count how many times a students name located in "names" appears on my "responses" sheet. columns where names will appear in several columns. I will attach the file I am working. Thanks to anyone that can help!
Attachments (1)
homework question help.xlsx
-----------------------------------------

## Monday, October 28, 2013

### yogi_Extract The Entry In Last Or LastButSpecified Column Of A Specified Row For Open Ended Set of Entries

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 28, 2013
How to find the last cell value at the end of a specific row
Hi,

I have a Google Spreadsheet which is adding a new column each month. I need to create a formula which finds the last value at the end of row 191. Is it possible to create a formula which can sit in column B, specifically B191, which is able to automatically reference the last cell at the end of row 191?

John
---------------------------------------------------------------------------------------------------------------------------------------------------

## Sunday, October 27, 2013

### yogi_Compute Running Balance Based On Income Fees Postage And Initial Balance

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 27, 2013
IF Statement giving error
I'm having a bit of a issue trying to figure this out.

I have created a spreadsheet that is supposed to be kinda of like a register

 Income Fees Postage Expense Balance \$77.00 \$9.46 \$1.69 \$65.85 \$155.00 \$18.75 \$1.69 \$200.41

The fees (eBay+PayPal) uses the following formula =IF(D3>0,(D3*.09)+((D3*0.029)+.30),IF(D3=0, ""))
The balance uses the following formula =IF(D3>0,((D3)-(E3+F3+G3)),IF(D3=0, ""))

The problem comes up when there's an expense. It sees that D3 (income) is blank and errors out. I've tried the following
=IF(D4>0,((H3+D4)-(E4+F4)),IF(D4=0, ""),IF(G4>0, H3-G4)) which does not work.

I think something like this will work, but I don't know the proper syntax, or if it's even possible.
=IF(D4>0, H3+D4-(E3+F3)), IF(D4=0 AND G4=0, ""), IF(D4=0 AND G4>0, H3-G4)

Any help would be greatly appreciated.
------------------------------------------------------------------------------------------------------

### yogi_Merge Data From 3 Sheets Filtered For 'Remote Cars' And Sort By Year In Ascending Order

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 27, 2013
Merge the data from Multiple Sheets to Main Sheet by Sorting and Filtering
Hi All,

I need some help in google docs formulas

i trying to merge the data from multiple sheets to one main sheet by sort and filter with particular product, but it was not working

i using iferror(sort(filter()...

Kindly Help in these.

Thanks
Attachments (1)
Workbook1.xlsx
-----------------------------------------

## Saturday, October 26, 2013

### yogi_Sort Source Data By Column A So That Sorted Data Has Year Numbers Without Comma

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 26, 2013
How to format years as numbers, but without commas
I want to organize a sheet chronologically, and I have all the years in one column. I can get them formatted as numbers, but this makes them look funny. The year '1963' looks like '1,963' and I don't like it. If I format the numbers as regular text, the comma goes away, but I can't sort chronologically! Any help would be greatly appreciated!
-------------------------------------------------------------------------------------------------------------------------------------------------

## Tuesday, October 22, 2013

### yogi_Extract From Table Unique Names Contacts And Unique Sets Of Associated Numbers Arranged In Ascending Order

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 22, 2013
merge/consolidate unique data into master row (???vlookup,unique,offset,match,index??not sure what to use)
My list looks similiar to this:

 Name Contact #1 #2 #3 #4 #5 Aker Wik 111 555 222 Aker Wik 222 555 Aker Wik 333 555 111 Aker Wik 444 555 Aker Wik 555 111 Law Firm Harold 777

I need to consolidate the # fields under a unique name. So I would end up with something like this....

 Name Contact #1 #2 #3 #4 #5 Aker Wik 111 222 333 444 555 Law Firm Harold 777

A long time ago I had worked it out but i cant find the old spreadsheet and havent used google sheets in a while. if anyone can help that would be awesome!!

thanks!
------------------------------------------------------------------------------------------------------------------
here follows my convoluted solution

### yogi_Compute Average_Of_K IT_Mode IQ_Mode And A_Mode For Open Ended Sets Of Assignements Starting From Column I And Extending To Right

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 22, 2013
Trying to use IF and MODE, ie pretty much lost
Hello:

I am pretty new to Google sheets and Excel type documents in general.  I am trying to get a bit more organized and spent hours and I am pretty much getting nowhere.  I am trying to have Google docs calculate a number of things, I uploaded just the skeleton copy for reference.

1.  First I was trying to find the average of only the columns that were labeled "K" for each row of students.  I understand that I could manually select each cell to add but I was hoping to find another method since I would have about 300 students and this would not be the answer.  That roundabout way ended was to add 2 more columns, one for the sum of all the values in the K columns for each row and the other was how many "K" columns existed which I seem to only need to add once unless I was looking for missing grades I guess.  I don't mind it as much, but if I could condensed it to just one cell showing the average for only the columns that are labeled "K" that would be great.

2.  The other is I would like to find a command that would take the Mode for all columns labeled IT, IQ and A for each row of students.  I tried several IF commands, but most of of the time I unknown error, unknown function, Parse error and so forth.  The last one I tried was =IF(J4:AH4,"IT", MODE J5:AH5).

Any help would be greatly appreciated on formatting and even setup ideas.

Thank you.

Attachments (1)