yogi_Conditionally Format -- Highlight Names That Meet Specified Criteria (noted in cells F26:G28)

Google Spreadsheet   Post  #2234

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-31-2017
question in: Whitney Andersen

https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/QQbNLslmQlQ;context-place=forum/docs

Data validation
I have a doc with a series of Data Validations and I wanted to have a cell be flagged if another cell has a certain drop down entered unless a drop down in another cell is checked. Ill share the sheet and I wrote the explination of what im trying to do in Cells F26 - G26, and F28-G28

https://docs.google.com/spreadsheets/d/10HJ9_pGL9a823Xi2fGTOdO-1eeOnPqt0OmpXRTft2NY/edit?usp=sharing

yogi_Going Row Wise Conditionally Format -- Highlight Subsequent Instances Of Names That Have Already Occurred

Google Spreadsheet   Post  #2233

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-29-2017
question in: TCs Friendly Circle

https://docs.google.com/spreadsheets/d/17zzR1I1FWLxeL8LGLSTGfl9kS-tRKVBwn50YHYIaIUs/edit?usp=sharing

Going Row Wise Conditionally Format -- Highlight Subsequent Instances Of Names That Have Already Occurred

yogi_Conditionally Format (Highlight Duplicate Entries In Column A While Ignoring A Specified Character In The Entry

Google Spreadsheet   Post  #2232

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-27-2017
question by: Paulo Miguel

https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/6bODIHZwWbg/syOPinS7CAAJ

Issue with asterisks and conditional formatting

I have the following in column A:

12345

12345*

1234567*890

And the following in conditional formatting:

=countif(A:A,A:A)>1

The issue is that 12345* gets marked when it obviously shouldn't be. After a bit of testing I found out it was the asterisk likely messing up with the formula, since 12345* does match with 1234567*890 if wildcards were active (which they are), just like 1234567*890 would be marked if something like 1234567XXXXX890 was in the column.

So my question is if there's any way to either disable wildcards for this formula or if there's any way around this issue.

Any help would be appreciated.

yogi_Conditionally Format (Highlight Duplicate Entries In Column A While Ignoring * Character In The Entry

Google Spreadsheet   Post  #2231

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-27-2017
question by: Paulo Miguel

https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/6bODIHZwWbg/syOPinS7CAAJ

Issue with asterisks and conditional formatting

I have the following in column A:

12345

12345*

1234567*890

And the following in conditional formatting:

=countif(A:A,A:A)>1

The issue is that 12345* gets marked when it obviously shouldn't be. After a bit of testing I found out it was the asterisk likely messing up with the formula, since 12345* does match with 1234567*890 if wildcards were active (which they are), just like 1234567*890 would be marked if something like 1234567XXXXX890 was in the column.

So my question is if there's any way to either disable wildcards for this formula or if there's any way around this issue.

Any help would be appreciated.

yogi_Conditionally Format 'Ingredients To Avoid' That Are In 'Ingredients List'

Google Spreadsheet   Post  #2230

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-26-2017
question by: jorasdou

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/xUn8Kb5GZvs;context-place=forum/docs

Possible to enter a list of items in one cell and have matches in other cells highlight?

 

Apologies in advance, because I'm not tech- or formula-savvy. I have a skin condition that requires me to be avoid certain ingredients; I can usually remember most of them, but sometimes it's hard to remember all of them. While I'm shopping for products online (because I'm still trying to find things that help), I'm having to read through ingredient lists to look for problematic items, usually multiple times in order to check for each thing.

I'm wondering if there's a way to set up a Sheet and formula for this:

-Ingredients To Avoid are already listed in individual cells

-I copy/paste a product's full ingredients list into a specific cell (a large merged cell)

-Any word matches between the two lists would result in that ingredient's cell being highlighted on the Ingredients To Avoid list

I set this up for example -- the full list of ingredients has Alcohol Denat, Castor Oil, and Fragrance, so three cells are colored in:

The Ingredients To Avoid list isn't complete, so I'd need to know how to change the formula to incorporate the additional items. But is this even possible? Or will it not work due to the product's full list of ingredients not being listed in individual cells?

yogi_Compute Row By Row Running Total Of Value By Type Where Date is Less Than Or Equal To Date In Current Row

Google Spreadsheet   Post  #2229

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-20-2017
question by: Henrique Magalhaes

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1cjhkWHcKC4;context-place=forum/docs

Array formula with sumif with less than condition

Hi, I have this sheet with the following columns of data:

Column A - Date: this is the date of a transaction.

Column B - Type: this is the type of the item

Column C- Value: this is the value of the transaction

For each row, I need to determine 2 values:

Column D. the sum of all values of the current type, where date is equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;A2;B:B;B2).

Column E. the sum of all values of the current type, where date is less than or equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;"<="&A2;B:B;B2).

However this sheet will be used with a great amount of data, and I need it to auto populate new rows with these formulas. Arrayformula does that well for the first one, and I changed it to: =arrayformula(if(row(A:A)=1;"sum of type in current date";sumif(A:A&B:B;A:A&B:B;C:C))).  Sumifs didn't work so I changed it to a formula with sumif and & operators.

However I could not think how to make it for the column E formula. Is there any way to use arrayformula with sumif (or sumifs) and "<=" condition? If not, is there any other way to auto populate the formula to new lines?

Thanks

yogi_Compute Running Total Of Value By Type Where Date is Less Than Or Equal To Today's Date

Google Spreadsheet   Post  #2228

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-20-2017
question by: Henrique Magalhaes

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1cjhkWHcKC4;context-place=forum/docs

Array formula with sumif with less than condition

Hi, I have this sheet with the following columns of data:

Column A - Date: this is the date of a transaction.

Column B - Type: this is the type of the item

Column C- Value: this is the value of the transaction

For each row, I need to determine 2 values:

Column D. the sum of all values of the current type, where date is equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;A2;B:B;B2).

Column E. the sum of all values of the current type, where date is less than or equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;"<="&A2;B:B;B2).

However this sheet will be used with a great amount of data, and I need it to auto populate new rows with these formulas. Arrayformula does that well for the first one, and I changed it to: =arrayformula(if(row(A:A)=1;"sum of type in current date";sumif(A:A&B:B;A:A&B:B;C:C))).  Sumifs didn't work so I changed it to a formula with sumif and & operators.

However I could not think how to make it for the column E formula. Is there any way to use arrayformula with sumif (or sumifs) and "<=" condition? If not, is there any other way to auto populate the formula to new lines?

Thanks

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

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?

Hello!

I need help creating a formula.

SAMPLE DOCUMENT:
https://docs.google.com/spreadsheets/d/1JeqPA69Jwcp75VlivtjeJDzWs2l-ccnC4ZRGsYQTIW0/edit?usp=sharing

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

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" 

Dog	Cat	Bird	Fish	Turtle
Fish	Gerbil	Hamster	Duck	Rabbit	x

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

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