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     www.energyefficientbuild.com.    Jun 25, 2015
question  by Kanagalakshimi Ravinttirin:
https://productforums.google.com/forum/#!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:
DateTagTicket#Status
5/25/2015JMQBR42156273Work In Progress
5/25/2015D17V0P1156273Work In Progress
6/3/20157NLQ9T1156503Resolved
6/3/2015B50Q9T1156503Resolved
6/3/2015DX78XS1156503Resolved
6/3/20153KTK6V1156523Resolved
6/3/201560LP8X1156524Work In Progress
5/29/2015B8J2B2S156551Work In Progress
5/29/20156KMTRR1156566Resolved
5/29/201568H4VS1156572New
5/31/2015GZR0QH1156573Work In Progress
6/1/2015365D7M1156750Resolved

And expecting output such as
Expected Output
Date
No of Ticket
No of Tag
Status of Ticket
NewWork In ProgressResolved
6/3/201535012
5/29/201533021
5/31/201511010
6/1/201511001


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     www.energyefficientbuild.com.    Jun 23, 2015
question  by Josh Rosenberg:
https://productforums.google.com/forum/#!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     www.energyefficientbuild.com.    Jun 21, 2015
question  by Naveen Nagaraja:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ZVw2xo9TbaE
Google Sheet search multiple values
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!!

XYABCDG
1abc123abc, def, xyz,
2def231
3xyz


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


                                           Google Spreadsheet   Post  #1968
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 20, 2015
question  by AlexIR:
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!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:

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?

Thanks in advance,

Alex

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

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     www.energyefficientbuild.com.    Jun 18, 2015
question  by TN Dispatch:
https://productforums.google.com/forum/#!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
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


                                           Google Spreadsheet   Post  #1966
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 15, 2015
question  by mpacker:
https://productforums.google.com/forum/#!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.
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.
---
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.

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