Sunday, August 13, 2017

yogi_Return X If The Row (columns A:E) Does Not Contain one Of The Specified Items

Google Spreadsheet   Post  #2225

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-13-2017
question by: Dana Sturdivant
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/-EnxHCZ-MIA;context-place=mydiscussions

Return "x" if row does not contain "dog" OR "cat"

I need to write a formula that returns an x if the adjacent row does not contain any of the specified text strings. 

Example: Specified text: "Dog" "Cat"

Row 1 does not return an x because it has the text "Dog" and/or "Cat" 
Row 2 returns an x because it does not contain the text "Dog" or "Cat" 

DogCatBirdFishTurtle
FishGerbilHamsterDuckRabbitx
I have attached a sample case with text in cells A2:E5, and with the desired result in F2:F5

https://docs.google.com/spreadsheets/d/1_i4maxtQlfPGQ5yUTur_YWyO31CXwrO5VPmb8tmTDhA/edit?usp=sharing


Saturday, August 12, 2017

yogi_Compute Average Of Multiplier Values (read from Sheet2) for ROTX Entries Which Have Date in Column B = That In C3

Google Spreadsheet   Post  #2224

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-12-2017
question by: Sergio Ochoa
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/QXBCSG75IFk;context-place=mydiscussions
How do I combine these averageif and lookup functions?
I gave it my best shot and I am admitting defeat. Can someone help?

I am looking for a way to get the average values of the letters in sheet 1, column A with those that match column B with the value in C:2 (7/31). The result should be 2.4 (Average of 10,2,1,0,-1).

Sheet 2 has the purpose of holding the multiplier values so I can go to one place and change them if need be.

Thoughts on how to do this?

Sheet 1
A
B
C
1
ROTX
Date
Searching for
2
A
7/31
7/31
3
B
7/31
4
C
7/31
5
D
7/31
6
F
7/31
7
A
8/2
8
A
8/3
9
A
8/4
10
B
8/5
11
B
8/6

Sheet 2
A
B
1
ROTX
Multiplier Value
2
A
10
3
B
2
4
C
1
5
D
0
6
F
-1


Thursday, August 10, 2017

yogi_Conditionally Format Entries In E4:E -- Yellow Red or White Depending On Date Due

Google Spreadsheet   Post  #2223

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-10-2017
question by: Brittany Berridge
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/62Q07ikdCXs;context-place=mydiscussions

Conditional Formatting for due dates

 
Hello, I am setting up a spreadsheet that contains a due date and a date turned in column. I have the conditional formatting set up to highlight the upcoming due dates (7 days out) but I would like the formatting to go away if the 'date turned in' column contains data. 

Wednesday, August 9, 2017

yogi_Pull Info From Another Sheet If Any Of The Specified Columns Contain A Specified Term

Google Spreadsheet   Post  #2222

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-09-2017
question by Eliza Brennessel :
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/YuVsWBYkIOg;context-place=mydiscussions

Auto Populate Data from Master, onto new sheet, validated by 5 possible different columns?

Hi there -

I have a MASTER list with the following columns: 

B:First
C: Last
D: Email 
E: Phone
F: DEPT 1 *data validated
G: DEPT 2 *date validated
H: DEPT 3 *data validated
I: DEPT 4 *data validated
J: DEPT 5* data validated

I would like to create a sheet that auto populates First, Last, Email, Phone, of anyone who has the letters "PC" in any of the "DEPT" columns (F:J).

Please help! I think it's an array formula, possible data lookup, but I'm so lost!

Thank you!!

yogi_Using Query Function To Add A Subtotal Line For Each Order Number And Grand Total At The End

Google Spreadsheet   Post  #2221

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-09-2017
question by Peter @ Kwik Kopy :
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/dJCTOLxcVyo;context-place=mydiscussions
Sheets Query add subtotals
Using a query in sheet

Is there a way to have the query interject a line with a subtotal

data example

Orange 5
Orange 6
Apple 10
Apple 6

Query result

Orange 5
Orange 6
Subtotal Orange 11

Apple 10
Apple 6
Subtotal Apple 16

Grand Total 27

thanks if anyone can help

Tuesday, August 8, 2017

yogi_Sum Up Numbers Several Sheets By Different Dates

Google Spreadsheet   Post  #2220

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-08-2017
question by Benjamin Ohashi :
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Uqd_Dqn_FXA;context-place=forum/docs
Copying Formula Down
I am trying to copy the following formula down an entire column. When I drag the cursor down it does not copy the formula correctly. 

I need this to carry down column D and E. 

Is there a way to rewrite the formula to be able to drag it down and repeat the formula correctly? 



Monday, August 7, 2017

yogi_Subtract Row By Row From Given Dates Number Of Days Based On Specified Criteria

Google Spreadsheet   Post  #2219

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-07-2017
question by risen2connquer :
https://productforums.google.com/forum/#!topic/docs/64k4Q3E5UpM;context-place=mydiscussions
Need Formula To Calculate Number Of Days To Subtract From Date
I'm trying to figure out how to write a formula which would populate the cells in a column on my first DATES sheet by subtracting day values from a manually entered date in another column. The days to be subtracted are determined by manually entered PLACE values in the same row which correspond to varying numbers as defined on the second DATA page.

There are two conditions which determine whether the PLACE value is valid, however:

Rule 1: If a PLACE value on the DATES sheet is not the colour orange (or if its neighbouring STATUS cell contains data, whichever is easier for a formula to calculate), the days corresponding to this particular PLACE value are not considered.

Rule 2: If there is more than 1 instance of the same PLACE value in the same row (which conforms to Rule 1), only one instance of this value is considered.

Please see the attached example and let me know if something is not clear. I really appreciate any help with this!

yogi_Get sum of columns depending on the data in a few other columns Per Specified Pattern

Google Spreadsheet   Post  #2218

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-07-2017
question by Dan Burton :
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/JR2kroEIu48/5e6jRo5XAQAJ;context-place=mydiscussions

Get sum of columns depending on the data in a few other colums

 Hi,

Sorry for the repeat post, I found the post below, which answers what I need to a point. My difference is that I need to look (using this example) for "Anton" in 6 or 7 different columns, not sure how to add the grouping of columns to get my final total. Any help would be greatly received,

Dan
Big Russ said:
Further to Ahab's example, you also need to add information about the column of data you are summing, so assuming you meant your example to be:
    Column B     Column C

    Anton           196,47
    Niclas           1000
    Anton            433,85
    Niclas           130
    Carl              700
The formula should be =SUMIF(B:B,"Anton",C:C) - It is possible to exclude the sum_range as Ahab specified, however I have had mixed results with how the function behaves and tend to explicitly define all aspects of the function for consistent results.

yogi_Using Query Function To Add A Subtotal Line For Each Item

Google Spreadsheet   Post  #2217

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-07-2017
question by Peter @ Kwik Kopy :
https://productforums.google.com/forum/#!topic/docs/dJCTOLxcVyo;context-place=forum/docs
Sheets Query add subtotals

Using a query in sheet

Is there a way to have the query interject a line with a subtotal

data example

Orange 5
Orange 6
Apple 10
Apple 6

Query result

Orange 5
Orange 6
Subtotal Orange 11

Apple 10
Apple 6
Subtotal Apple 16

Grand Total 27

thanks if anyone can help


Sunday, August 6, 2017

yogi_count instances of cell data depending on the timestamp month

Google Spreadsheet   Post  #2216

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-06-2017
question by ErinNJ:
https://productforums.google.com/forum/#!topic/docs/EXndDa6C0bU;context-place=forum/docs

How do you count the occurances of cell data depending on the timestamp month? I've tried

How do you count the occurances of cell data depending on the timestamp month?  I've tried COUNTA, COUNTIFS, IFS and SUMIF unsuccessfully.

I have a survey with multiple choice checkboxes (as well as single answer questions).   I'd like to sort my results by April, May, June etc based on their timestamp.  I've been able to calculate results of all entries, but not separated by month.

My data is in the tab "RawData" and my results, which I'd like to populate, is in the "Analysis" tab.

I'd love any help, please!!  It would make my day!


Saturday, August 5, 2017

yogi_Pull Into Summary Sheet Data That Is Both In Two Other Sheets

Google Spreadsheet   Post  #2215

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-05-2017
question by Justin Chu Lee:
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/8KOXuradLVA/ZbPnfjqZAQAJ
Finding out if this is actually possibly on google sheets
Hi everyone,

I am working on a google spreadsheet project right now, and I am trying to see if this is possible.

On the sheet I am working on, I have three separate tabs. The second and third tab has a serious of names, separated by first and last name, that number in the hundreds. 

Now I am trying to figure out if there is a way or an equation I can use on the first page that will let me know if there are any names that match on the second and third sheet and if those names could automatically be transferred to the first page. 

For example; if the second and third sheet both have a "Dave Lee" I would like to see that name pop up on the first page immediately when it recognizes that it is the same.

Is that possible? If it isn't, any suggestions on how I can make this task easier for me?

Thank you,

Justin