Saturday, August 19, 2017

yogi_Add a Specified Number Every Nth Day From A Specified Date

Google Spreadsheet   Post  #2227

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-19-2017
question by: MathMan123
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/yK3diow3dHg;context-place=forum/docs
How to add a certain number every 30th day from a specific date?
If possible? I really hope someone might be able to help me out here if you have any ideas on how to do it please share it! 
Thanks in advance. 

     - Peter Hansen

Friday, August 18, 2017

yogi_Find The Lowest Priced Vendors By County

Google Spreadsheet   Post  #2226

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-18-2017
question by: Ben.V
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/aiqY1HL93QY/p-KBW7kYCgAJ

Search for duplicates in Col B, compare number in Col C, highlight lowest number?

 
This sheet shows the following:
Vendors in column A
Counties in column B
Prices in column C
I often need to review lists like this to find the cheapest vendor in each county. Many counties have more than one vendor. Currently, I have to do this manually.
Here's what I would like a formula to do:
1.  Scan the list of counties in Col B.
2.  Find duplicate county names. This will show where there is more than one vendor per county.
3.  Compare the prices in column C for the duplicate counties.
4.  Highlight the lowest number.
For example:
See Rows 36 and 37.
Two vendors cover Hardeman.
One vendor costs 65. The other costs 95.
I would like the system to find this and highlight the 65.
Thank you very much in advance for any suggestions!!!
Ben

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