Thursday, June 25, 2015

yogi_Count No Of Instances By Status Based On Multiple Criteria

                                           Google Spreadsheet   Post  #1970
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Jun 25, 2015
question  by Kanagalakshimi Ravinttirin:!topic/docs/u5hDKalwJPI;context-place=forum/docs
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:
5/25/2015JMQBR42156273Work In Progress
5/25/2015D17V0P1156273Work In Progress
6/3/201560LP8X1156524Work In Progress
5/29/2015B8J2B2S156551Work In Progress
5/31/2015GZR0QH1156573Work In Progress

And expecting output such as
Expected Output
No of Ticket
No of Tag
Status of Ticket
NewWork In ProgressResolved

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

                                           Google Spreadsheet   Post  #1970
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Jun 23, 2015
question  by Josh Rosenberg:!mydiscussions/docs/Pq4-EnhnLuI
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

                                           Google Spreadsheet   Post  #1969
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Jun 21, 2015
question  by Naveen Nagaraja:!category-topic/docs/spreadsheets/ZVw2xo9TbaE
Google Sheet search multiple values

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


How can I do this in Google sheet

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

1abc123abc, def, 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


Saturday, June 20, 2015

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

                                           Google Spreadsheet   Post  #1968
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Jun 20, 2015
question  by AlexIR:!category-topic/docs/spreadsheets/Ywg92-ccxRs
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:

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: 

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

How do I stop this from happening?

Thanks in advance,



Thursday, June 18, 2015

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

                                           Google Spreadsheet   Post  #1967
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Jun 18, 2015
question  by TN Dispatch:!category-topic/docs/spreadsheets/vEoANsedz5g
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
now if the current time hits 18:30 i want to see
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
and when 14:45 comes around it will show
when 15:00 comes around it will show
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

                                           Google Spreadsheet   Post  #1966
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Jun 15, 2015
question  by mpacker:!mydiscussions/docs/kB4Ca0yfyM4
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.
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

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.
I've modified the spreadsheet.

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.