yogi_Conditionally Format Range B2:C4 -- If Cell <>"-" Color It Orange

Google Spreadsheet   Post  #2305

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-26-2017

question by: Clive J Smith

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

Conditional Formatting Custom Formula Operators

I use a drop down menu to assign a value to a cell lets call it B8  (eg: -, Ent, CS, AB, SD, RE etc.)

C8 contains other information

I wish to use the fact that B8 is populated by an entry other than "-" to change the background. (To make it simple to see if say, AB has dealt with something)

I have managed to do it with "Ent" as the default operator and have written the formula in Conditional Formatting  =$B$8<>"Ent" for range

B8:C8

This works fine, but when I use =$B$8<>"-" it doesn't work

Also, I wish later to copy and paste this formatting and the formula =B8<>"Ent" also doesn't work in any cell other than the source cell (B8) ie not in C8.

yogi_Data Validation For Cells E2:M To Alleviate Possibility Of Going Negative

Google Spreadsheet   Post  #2304

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-26-2017

question by: Evelyn Brooks

https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/9FQo8uiYjF0/RVgQeKCvBQAJ

How can I lock a cell ref from changing to a negative number
I have a shared sheet with different available products. I have a row with names for each person I share it with and a column with the quantity available, so I set it up with the sum formula so when each person puts in the quantity they would like it deducts the quantity left. My problem is once the total quantity left hits 0 people can continue to input an amount so the total becomes negative and I don't know who requested what 1st. 

Column D I have added this equation so I know how much product is left:

So I want to know if there is a way to keep the cell with the Available quantity from locking at 0? That way I know the product is sold out. 

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

yogi_For Entities In Column A (range A17:A) Create Specified Number Of Consecutive Rows For Each Entity

Google Spreadsheet   Post  #2303

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-26-2017

question by: Boris (fxpr)

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/dpacm9LquDY;context-place=topicsearchin/docs/authorid$3AAPn2wQc7O4EQsX1gkj1mKYiVqjACnXxOUJfDW9aP6REEoSb26rzIBkrBhpHOgIm3cS21f6-y3rVl%7Csort:date%7Cspell:false

Need your help with google sheets
Hi! 

Could you pls help me with this problem: how to convert 1 number to some words? 

I have the next situation:

There are X social networks (then "SN"). for example X=10.

On the each one I need to answer questions particular times, for example:

SN 1 = 2 answers

SN 2 = 3 answers

SN 3 = 1 answer 

On the sheets I need to get this table

List 1:

SN 1 = facebook   = 2 answers

SN 2 = youtube     = 3 answers

SN 3 = instagram  = 1 answer

List 2:

facebook

facebook   

youtube     

youtube     

youtube     

instagram  

instagram  

instagram  

___

Thank you

yogi_Compute Row By Row Time Differences From Base Value In Cell B2 With Times In mm:ss.sss

Google Spreadsheet   Post  #2302

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-23-2017

question by: Rene Englehardt

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

Difference in times (e. g.) motorracing events

I'd like to know how to achieve the following difference in time through GSheets

I have a couple of values that are essentially the ending times of a person (as an example from a motorsport result). Those look like MM:SS.SSS (30:55.112). Important is to have it calculate the difference from the fastest to the other times.

Example:

#1 30:55.112

#2 30:55.180 (+0.068) 

#3 30:55.413 (+0.301)

#4 31:01.226 (+6.114)

I need the values in bold to be calculated through GSheets.

yogi_Sort by Family_ID And Separate Family_ID With An Empty Row

Google Spreadsheet   Post  #2301

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-23-2017

question by: Montblac

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

Separating Using Empty Rows

 

How do I separate rows depending on their cell values? I need to create a family address book that lets me separate entries into family groups. I want to give entries a FAMILY_ID value and when I sort/filter using those values, I want them to be separated with an empty row. For example: 

FAMILY_ID          NAME

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

1000                    Tim

1000                    Shane

2000                    Mark

3000                    Steve

3000                    Jim

When Sorted / Filtered:

FAMILY_ID          NAME

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

1000                    Tim

1000                    Shane

2000                    Mark

3000                    Steve

3000                    Jim

Thanks.

yogi_Limit Number Of Instances Of A Specified Entity Of Data Validation List

Google Spreadsheet   Post  #2300

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-23-2017

question by: Sirisha Desai

https://productforums.google.com/forum/#!topic/docs/5rz1MUuWZ8o;context-place=forum/docs

Need help

 

I have a drop down column with m, n, o, p items which the participants can choose from. I also have a limit to each item. So item m can be chosen only by y people. Once the y count is reached, it should not allow any more entries for that particular m item. It's on first come basis. 

yogi_Using Regexmatch In Multiple Columns

Google Spreadsheet   Post  #2299

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-23-2017

question by: HoraceGZZ

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

Using ARRAYFORMULA and REGEXMATCH in 2 columns

Hello, what I'm trying to do is very simple. I need a single arrayformula (cell C1) that uses RegExMatch to verify if either column A or column B contain a specific text ("foo"). If any of the 2 cells on each row contain the specified text it will return a 1, otherwise it will return a 0. Preferably it should be a single formula at the top.

This would be the desired result:

	A	B	C
1	foo	cvb	1
2	ert	fooer	1
3	sdf	asd	0
4	qwe	foo	1
5	food	rty	1
6	qwer	xcv	0

Edit: here's an editable version of the above example: 

https://docs.google.com/spreadsheets/d/1VUFjL2c2kWhC_rhGlJRTJ7qVlC4svHeHSTmFZT11XFk

I tried the following formula but it doesn't work:

=ARRAYFORMULA(IFERROR(IF(OR(REGEXMATCH(A1:A,"foo")=TRUE,REGEXMATCH(B1:B,"foo")=TRUE),1,0)))

The following formula works but the results take 2 columns (C and D) instead of just one:

=ARRAYFORMULA(IFERROR(IF(REGEXMATCH(A1:B,"foo")=TRUE,1,0))

The following formula works but I have to copy it down instead of being a single formula at the top:

=IFERROR(IF(OR(REGEXMATCH(A1,"foo")=TRUE,REGEXMATCH(B1,"foo")=TRUE),1,0))

Is this possible or do I have to use the copy down version?

Thanks in advance

yogi_Show Count And Average Of Values By Person In DashBoard

Google Spreadsheet   Post  #2298

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-23-2017

question by: John Paul Banaag

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

How to average and count the value of a specific person in a raw data?
Hello,

Thank you for your interest in viewing this topic. I'm posting this as I really need all the help I can get from you all.

I'm trying to create a sheet that has a dashboard and a raw data which would allow me to see on the dashboard the scorecard of each person by week.

I made a sample below which would let you all see what I'm trying to create. I'm only asking for the formulas that I would need to use in the green cells which you would see in my sample. Please access the link to see the sample:

https://docs.google.com/spreadsheets/d/1j1rsfsX5zkl_KrWlYKt5Mt4CEwLTW5yEqZ6Li-tUVyU/edit?usp=sharing

I'm looking forward to all of your comments.

Thank you!

yogi_Conditionally Format Discounted Values That Are Below Bottom Line Price

Google Spreadsheet   Post  #2297

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-22-2017

question by: tayde

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

How do I format my sheet with colors for each cell?

I have a discount sheet I created, where each column shows me the individual price of an item when a specific discount percentage is applied. I've added a column that corresponds with the cost of an item that I don't want to go under. How can I format the cells to mark as red if a discount will go under the bottom price of an item? 

(So here I would like the 20% through the 35% of the $200 item to be marked red. I know I can do this through the conditional formatting, but is there some way to format it for the entire sheet instead of formatting each row individually?)

Bottom Line	PRICED AT	10%	15%	20%	25%	30%	35%
$200.00	$250.00	$225.00	$212.50	$200.00	$187.50	$175.00	$162.50
$380.00	$1,100.00	$990.00	$935.00	$880.00	$825.00	$770.00	$715.00

Edited

yogi_Extract Row By Row Substring Using Query Function

Google Spreadsheet   Post  #2296

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-22-2017

question by: DaveGoogle

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/-GlmlC1qgaQ;context-place=topicsearchin/docs/authorid$3AAPn2wQc7O4EQsX1gkj1mKYiVqjACnXxOUJfDW9aP6REEoSb26rzIBkrBhpHOgIm3cS21f6-y3rVl%7Csort:date%7Cspell:false

How do I use substring / MID formula with Google Query?
Hi,

Below is my basic query.

=QUERY(Tracker,"select K,M,N,A,F")

This returns all the columns I need. However, I want to extract a substring of column M. Something like below:

=QUERY(Tracker,"select K,substring("M",2,7),N,A,F")

Can anyone advise the correct syntax?

Thanks

yogi_Compute Number Of Days Parked From In Out Data For Parking

Google Spreadsheet   Post  #2295

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-21-2017

question by: UliTol

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

Need help counting dates between dates

Hey,  I am a bit puzzled,  can I get some help with a formula to help count between dates.

The parking data tab shows a patron entry,   it shows the time in and time out

Say person A parked form 09/28/17 to 10/03/17

and person B parked from  10/02/17  to 10/04/17

I need to track how many people parked per day.

so it would look something like this

10/1 - 1

10/2 - 2

10/3 - 2

10/4 - 1

and so on.

https://docs.google.com/spreadsheets/d/1j9-n4VTMzGgJDN0COqRvdL-L19LCtNLk_IDbqmEt1SY/edit?usp=sharing

Thank you!

yogi_Custom Number Format Cells With k M B T And Vice Versa

Google Spreadsheet   Post  #2294

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-21-2017

question by: SharkSheaker

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

Transforming any number typed into a cell into a k,M,B,T... shortened
There are cells, for example cell A1, where a very high number can be typed into. now the number inside that cell should be formatted to k,M,B...

for example 25.300.000 would usually turn into 2,53E+7 but i want to have 25,3 M

when numbers have to many non zeros, the value is rounded up. for example 123.475.345.876 turns into 123,5 T

because:
k stands for kilo
M for million
B for billion
T for trillion
Q for quadrillion
Qi for quintillion

etc.

thank you for any help