## Thursday, June 25, 2015

### yogi_Count No Of Instances By Status Based On Multiple Criteria

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 25, 2015
Count of Status based on 2 criteria
Hi all,

Hope you can help me out.
Maybe it is super simple, but I'm unable to figure it out.

I have this table:
 Date Tag Ticket# Status 5/25/2015 JMQBR42 156273 Work In Progress 5/25/2015 D17V0P1 156273 Work In Progress 6/3/2015 7NLQ9T1 156503 Resolved 6/3/2015 B50Q9T1 156503 Resolved 6/3/2015 DX78XS1 156503 Resolved 6/3/2015 3KTK6V1 156523 Resolved 6/3/2015 60LP8X1 156524 Work In Progress 5/29/2015 B8J2B2S 156551 Work In Progress 5/29/2015 6KMTRR1 156566 Resolved 5/29/2015 68H4VS1 156572 New 5/31/2015 GZR0QH1 156573 Work In Progress 6/1/2015 365D7M1 156750 Resolved

And expecting output such as
 Expected Output Date No of Ticket No of Tag Status of Ticket New Work In Progress Resolved 6/3/2015 3 5 0 1 2 5/29/2015 3 3 0 2 1 5/31/2015 1 1 0 1 0 6/1/2015 1 1 0 0 1

I am all with success for No of Tickets and tags. But unable to do the counts for Status. Anyone?
-------------------------------------------------------------------------------------------------------------------------

## Tuesday, June 23, 2015

### yogi_Delineate 'Yes' Or 'No' Depending On Whether Set Of 9 Rows Starting From Row 3 Down Contain 'http' in Any Of The Rows Of Column A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 23, 2015
How to determine whether "http" text appears within a range of cells in a column

## Sunday, June 21, 2015

### yogi_For Values In Each Row LookUp Value In Column 2 Of LOOKUP TABLE And Concatenate Resulting Values Separated By A Comma

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 21, 2015
Hi,

I have a google sheet question

I have search data as follows

X|  Y
1 | abc
2 | def
3 | xyz

I have a row of data as follows
A| B | C
1 | 2 | 3

Now I need to search data in columns A , B and C with search data table above ( column X and Y) and then have a cell filled with value

abc,def,xyz

How can I do this in Google sheet

Thanks,
Naveen
---
Hi Hyde,

Thanks for the information. I was looking for the first solution and it works good.
Is there a way to add a NULL check to vlookup formula ( suppose there is column D is a empty cell and I want to include it ) ?
Thanks again!!

 X Y A B C D G 1 abc 1 2 3 abc, def, xyz, 2 def 2 3 1 3 xyz

Notice Column D is Empty but still we try to compare and then in Column G we have a , due to this .

I tried ISBLANK function but could not make it work

Naveen
-----------------------------------------------------------------------------------------------------------------------------

## Saturday, June 20, 2015

### yogi_Compute For Data In Several Columns Row By Row Average Of Numbers And Associated Percentage

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 20, 2015
Stop Forms from shifting rows in Sheets
Hi everyone,

I have a small survey in Forms which asks for the participient's number of Facebook friends and the likes for his 5 recent profile pictures. Thus the colums in the Response Sheet looks like this:

Form Responses 1:
--------------------------------
A: Timestamp
B: # Friends
C: Likes Picture #1
...
G: Likes Picture #5
--------------------------------

For the calculations I have another sheet with three columns:

Calculation:
--------------------------------
A: #Friends: ='Form Responses 1'!B2
B: Average Likes: =AVERAGE('Form Responses 1'!C2:G2)
C: Percent: =(B2/A2)
--------------------------------

I wrote the Formulas into Row 2 and then copy-pasted them over all rows below.

Now here is the catch:
As soon as someone makes a new entry to the form, the respose inserts a new row to the Responses sheet, which also creates a gap in the calculations:

Calculations:
--------------------------------
A2 = Form Responses 1'!B2
A3 = Form Responses 1'!B4--------------------------------

How do I stop this from happening?

Alex

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

## Thursday, June 18, 2015

### yogi_Compute Times For Specified Number Of Tasks Given Start Time And Number Of Tasks Per Hour

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 18, 2015
Schedule Builder
Hello i am looking for a formula that will build be a schedule based on a start time and number of trucks per hr
so if the start time is 14:30 and i need 1 trk every 4 hours then i will get a schedule that looks like this
14:30
18:30
22:30
02:30
06:30
now if the current time hits 18:30 i want to see
18:30
22:30
02:30
06:30
10:30
say i change the trks per hr to 4 trks every hr and the schedule starts at 14:30 the schedule will look like this
14:30
14:45
15:00
15:15
15:30
and when 14:45 comes around it will show
14:45
15:00
15:15
15:30
15:45
when 15:00 comes around it will show
15:00
15:15
15:30
15:45
16:00
and so on i have attatched the spreadsheet and highlited in yellow where i want the values to appear and in green is where i will put the start time and trks per hr.

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

## Monday, June 15, 2015

### yogi_Compute Sum Up Numbers For Specified Number Of Months With Each Month Data Provided In Specified Number Of Columns

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 15, 2015
formula to add every 3rd column to the left
i have a spreadsheet that has three columns for each month  we will call them x1,y1,z1  next month i insert three new columns after z1 and we will call them x2,y2,z2 and so forth.

there are a few columns at the very end and one of them i'm trying to get total for last 12 months of x.   i can do this with a formula i change each month but i'd really like some way to say take the column 3 to the left + 6 to the left + 9 to the left... till i get a total of 12 then when i insert to columns it automatically changes to the last 12...
any ideas?
hope that's understandable.
thanks!
---
Hi Yogi!

Here is a link to a sample of what i'm trying to do:

if you look at columns AL, AM and AN  those are always the last three columns.  Each month i add three new column (in this case AI, AJ and AK).

The formulas in AL, AM and AN are the ones i'm trying to figure out how to get a formula so that I don't have to edit each month.

The formula in AL3 is currently
=AH3+AE3+AB3+Y3+V3+S3

after AI-AK have been added what i really want AL3 to do is be the sum of V3+Y3+AB3+AE3+AH3+AK3  (basically dropping S3 and adding AK3)

same thing for AM and AN columns....  I'm wanting AL to AN to always be working off of the last 6 months.

does that make sense?

I really appreciate the help.
---

There are two tabs now.

Month1 and "next month"

at the end of each month i add three columns.  the sheet would change from "month1" to what's in "next month".

when i do this i have to manually edit the formulas in ao3 to aq5

For example:

in month1:ao3 the formula is the sum of  v3+y3+ab3+ae3+ah3+ak3

when i add the columns next month i want a formula in ao3 to automatically change so i don't have to change ao3 to y3+ab3+ae3+ah3+ak3+an3

the blue column should always be the sum of the 6 grey columns to the left of blue column.. when 3 new columns are added..

if played around with arrayformula and other things but i can't figure out how to write one that does this.

Thanks
--------------------------------------------------------------------------------------------------------------------------------