Wednesday, April 30, 2014

yogi_Pull The Names And Percentage Of Those Who Were InvitedAndAttended From Lists Of WhoWereInvited And WhoAttended


                                         Google Spreadsheet   Post  #1621
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-30-2014
post by Ravel Fagundes: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/1dKa8DkQ0_A)
How can I compare two lists and get a percentage of results in common
Hi, 

I Would like to know how can I compare two columns and get a result of names in common and percentage of presence. 

Column A (Guest list names) 
John 
Paul 
George 
Ringo 

Column B (List of names that were present) 
John 
Paul 
George 

Column C (List of names in common between the lists) 
John 
Paul 
George 

Column D (Percentage of presence) 
75% 


Sorry for my english.
----------------------------------------------------------------------------------------------------------------------------------------------------


Sunday, April 27, 2014

yogi_Count Instances Of Years Listed In Range Consisting Of Specified Starting And Ending Column


                                         Google Spreadsheet   Post  #1620
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Prequel: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/zirdsPTg2tE)
Countif to get multiple results from same range
2010
2011
2010
2011
2012

Think about I want to count all of the years here.

2010 = 2
2011 = 2
2012 = 1

I don't want to combine the result, I want them to be seen seperately, just like above.

Multiple COUNTIFs can do that BUT if I want to add more cells I have to change ALL ranges of all COUNTIFs. I want to change the range just once for all formulas for each time I add more entries and they are using the new range all together. I'm guessing the only way to do that is to have different COUNTIFs but have the range information from somewhere else. I'm not sure if it's possible to have one single formula to get multiple results, listed seperately. Oh, and, I have multiple columns, if that makes any difference. (I couldn't use a single countif formula for counting a single criteria in multiple columns, for example)

Note that the "s" here is for plural, I can't use "countifs", I don't use the new version of Google Docs.

Thanks in advance.

-------------------------------------------------------------------------------------------------------------------------------------
in the following is a solution to a bit more generalized problem

Saturday, April 26, 2014

yogi_Add To yogi_signs Items From Inventory That Have Not Already Been Assigned


                                         Google Spreadsheet   Post  #1619
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Jacob W. Kerr: (compare 2 columns of address to creat a list of missing address)
I am making a sheet that keeps track of properties that have real estate signs put out for them.
I have a pull from another sheet that has the master inventory that pulls all properties with a available status.
In this document I have a tab that has validated field of street address that are manually selected, a vlookup attached to that field to pull the info for the sign (rest of address, beds, bath, price)
I want a section at the top that would be a list of address that are in the pull (available properties) but not not already on the page. The user would see what new address need to added in the correct area of the document.

so basically I want to compare two columns, a master list and a smaller list and get a new column that has the items missing from the master list.

-Jake

---
https://docs.google.com/a/spinhouses.com/spreadsheets/d/121GSMPxbi21xe5PC_jaFKHL0Qz3VJKr5m_373B_xE58/edit#gid=0

let me know if you need further explanation.

-Jake

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


yogi_Compute Rider Payouts By Name And Date Or Range Of Dates Based On Data In 'Fees List'


                                         Google Spreadsheet   Post  #1618
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Margot Con: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dIJM6a-TF5E)
Trouble with Arrayformula and sumfilter or sumproduct
Hi,

I'm having trouble finding a sum of data filtered for two different conditions (date, and name)


The "Fees List" sheet contains the bulk of the data that I'm looking at. The "Rider Payouts" is where I'm trying to use the functions. In column B is a SUMIF function that gets the total $ per name for the whole Fees List sheet. I want to also be able to find totals by date, or even/especially by date range. 

I tried both of the following formulas, in columns F and G, to see which I could get to work:
=ArrayFormula(sumproduct(('Fees List'!B$5:B=A3)*('Fees List'!A$5:A=H$1)*'Fees List'!G$5:G))

=ArrayFormula(SUM(IF('Fees List'!B$5:B=A3,IF('Fees List'!A$5:A=G$1, 'Fees List'!G$5:G,0),0)))

Both formulas result in 0 for all rows.

I put a date in G1 as a reference point to try and filter by that date. I'd actually ideally like to be able to use a range of dates, but I wasn't sure how to do that, and wanted to get the rest of the function working before I played around with that.

Any thoughts on what I'm doing wrong? A syntax problem or reference error??

thanks!
---------------------------------------------------------------------------------------------------------------------------------------------------


yogi_Compute Sum Of Quantities By Various Types of Equipment From Merged Data (from a number of sheets)


                                         Google Spreadsheet   Post  #1617
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by Michael Lance: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/7LtZTjNHCQs)
How do you combine like information with query and sum?
Here is my spreadsheet:
On the "By Vendor" sheet I am using Query to pull data from several sheets organized by vendor. What I am missing is how to get the formula to add up all of the like gear types and their quantities into a single listing for that gear type with the total of the specific gear type added up. 

Example right now it looks like this...
12 Circuit DogHouse1
12 Circuit DogHouse1
12 Circuit DogHouse1
12 Circuit DogHouse1
12 Circuit DogHouse1
12 Circuit DogHouse2
12 x 12 10'8
12 x 12 5 Way Corner5
And I'd like it to compile it like this if possible...
12 Circuit DogHouse7
12 x 12 10'8
12 x 12 5 Way Corner5

For some reason I can't figure out the correct syntax for adding the sum function in...
Maybe there is a better way?
Thanks! -M
------------------------------------------------------------------------------------------------------


yogi_Compute Sum Of Quantities Of Products Listed In 'RIEPILOGATIVO' By Each Month And Whole Year From The Transactions Listed In 'MERCER'


                                         Google Spreadsheet   Post  #1616
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2014
post by ANTONINO L: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/L3igpL-dKyY)
FILTER O FORMULA FOR PROBLEM

this post supplements the solution presented in my following blog post:
yogi_Compute Sum Of Quantities Of Products Listed In Sheet2 By Month From The Transactions Listed In Sheet1
http://yogi--anand-consulting.blogspot.com/2014/04/yogicompute-sum-of-quantities-of.html
---
Hello, 
I've tried and I can not get it to work. 

Set directly in the spreadsheet, I would be grateful if you could help me. 


thanks
----------------------------------------------------------------------------------------------------------------------------------------------------------
I have presented the solution in sheet named yogi_RIEPILOGATIVO

Friday, April 25, 2014

yogi_Compute Sum Of Quantities Of Products Listed In Sheet2 By Month From The Transactions Listed In Sheet1


                                         Google Spreadsheet   Post  #1615
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-25-2014
post by ANTONINO L: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/L3igpL-dKyY)
FILTER O FORMULA FOR PROBLEM
Who can help me to solve this problem: 
I would like to sum ​​the number of packages in a column having a specific code for each month. 
How can I do?
Thanks


SHEET 1SHEET 2
DATISUMMARY
CODDATAQ.TA'CODDESCRQ.TA' GENQ.TA' FEBQ.TA' MAR
6401/03/2014564PROD15
222713/02/201442227PROD2105
222711/03/201453339PROD3
345609/01/2014133456PROD413
222727/02/20146
---------------------------------------------------------------------------------------------------------------

yogi_Apply Conditional Formatting (red yellow orange green background) To Columns C F And I For Project Completion Status


                                         Google Spreadsheet   Post  #1614
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-25-2014
post by cory kelly: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Ve1m8m3imbQ)

Thursday, April 24, 2014

yogi_Apply Conditional Formatting To 'Project Due Date' Column Based On MileStones (flags) In Table C1 to E6


                                         Google Spreadsheet   Post  #1613
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
post by cory kelly: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Ve1m8m3imbQ)
Conditional Formating - How To: Change cell color according to date range
I've been searching everywhere and can't find the answer, but I know it's got to be out there.

I have tasks that have a scheduled "complete by" date. I want them to change colors as real time gets closer to that complete date.

If A1 is equal to or less than 7 days away, turn background red (This is the only one I can get working...)

If A1 is greater than 7 days away and less than or equal to 14 days away, turn bg orange.

If A1 is greater than 14 days away and less than or equal to 30 days away, turn bg yellow.

If A1 is greater than 30 days away, turn green.

Any tips would be great! Thanks
----------------------------------------------------------------------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem


yogi_Split A String Into Its Constituent Characters And Sum Up The Numeric Digits Ignoring The Non-Numeric Characters


                                         Google Spreadsheet   Post  #1612
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
post by Gillermo: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/b4zmtDKeIe8)
Divide string to rows?

this is a followup question to the one addressed in the following blog post:

yogi_Split A String Into Its Constituent Characters
http://yogi--anand-consulting.blogspot.com/2014/04/yogisplit-string-into-its-constituent.html

Follow up:
What about the SUM array thing for this method? For digits that is. Just sum them up and not print the parts one per row.

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


yogi_Split A String Into Its Constituent Characters


                                         Google Spreadsheet   Post  #1611
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2014
post by Gillermo: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/b4zmtDKeIe8)
Divide string to rows?
How do I divide a string in a cell to multiple cells?

Example:
String in a single cell = GOOGLE

Result (each new line is a new row and cell):
G
O
G
L
E


My guess is that it is accomplishable with ArrayFormula, MID and LEN but I can't make it work.
------------------------------------------------------------------------------------------------------------------------------------

following is a solution using ARRAYFORMULA, MID. and LEN functions


Tuesday, April 22, 2014

yogi_Conditional Formatting For Rows If Column A Has 'DA' And Any Of The Columns B C D Is Blank


                                         Google Spreadsheet   Post  #1610
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-22-2014
post by Megan Cook: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Es8WX9qpDzU)

this solution supplements the solution presented in my following blog post:

yogi_Mark YES Row By Row If Column A Has 'DA' And Any Of The Columns B C D Is Blank
http://yogi--anand-consulting.blogspot.com/2014/04/yogimark-yes-row-by-row-if-column-has.html



yogi_Mark YES Row By Row If Column A Has 'DA' And Any Of The Columns B C D Is Blank


                                         Google Spreadsheet   Post  #1609
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-22-2014
post by Megan Cook: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Es8WX9qpDzU)
Finding Blank Cells
Can I create a formula that will tell me if one cell out of a range is blank, while also filtering so that one column meets a criteria?

For example, I attached a screen shot of a sample sheet. Would there be a way to set up a formula that references the data in column A1:D8 and returns an error message if column A = "DA" and any of the other columns are blank. I don't need to know what information is missing. 

Basically, I want to add something to a Dashboard that would indicate that the information is not complete and which magazine has incomplete information. It wouldn't matter if it was one cell or twenty. Just something that would indicate to the editors that they need to go back in double check their work. 

Is that even possible?
Attachments (1)
Screen Shot 2014-04-22 at 5.03.57 PM.png
45 KB   View   Download

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