Wednesday, December 13, 2017

yogi_Material TakeOff Computation From Item Length and Quantity Table

Google Spreadsheet   Post  #2320

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-13-2017
Need IF function help - If value falls between two numbers

Jambs TOTAL:2410 ftHeaders TOTAL:990 ftSills TOTAL:990 ft
LengthQty.LengthQty.LengthQty.
6 ft1208 ft60860
6 ft1624 ft81481
8 ft683.5 ft343.534
4.25 ft108 ft585
8 ft43.5 ft23.52
12 ft218 ft1181
13.5 ft24 ft141
14 ft28 ft181

I have three sets of cells that as seen above, one for Window Jambs, one for Window Headers, and one for Window Jambs. Next to each ft you'll see a number of quantity (i.e. 6ft. and 120 mean there are 120 6ft. Jambs on one type of window, 162 6ft. Jambs of another). 

I want to create an IF function that if, say, a number falls equal 6, the qty numbers associated will be added so I'll know how many 6ft. lengths I have. Same thing for if it's less than or equal to 4 ft. This way I can figure out how many 10' lengths I'll need and can fit a 6' Jamb with a 4' Header or 3.5' Sill. 

I want to similar functions for the other numbers as well, but if someone can start me here, that would be appreciated.  

Thanks.

yogi_Collate Multi-Column Data To A Single Column

Google Spreadsheet   Post  #2319

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-12-2017
Collating Multi-Column Data to a single ColumnFIRST TIME POST EVER - NEED HELP!
Greetings,

I am attempting to collate data from Columns A-H into a single column.

Ultimately, I would like for the Single Column List to look like:

A1
B1
C1
D1
E1
F1
G1
H1
A2
B2
C2
D2
...

If there is a blank value, I would like for it to skip and got to the next column.


Thank you in advanced,

Amanda

Tuesday, December 12, 2017

yogi_Compute Row By Row Total Score From Entries In Columns D to O

Google Spreadsheet   Post  #2318

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-12-2017
How to "tally" questionnaire answers in excel from Google Forms?
FIRST TIME POST EVER - NEED HELP!

I am trying to easily tally these answers from students. I have points for the answers yes and no as well. I want to add these answers to the tally of other answers. What is the formula for that:


So...where it says "score" I want it to also add the yes/no numbers that are in parentheses.

Thanks! 

I know someone here knows how to do this. I have been searching for this answer and understanding for an hr.


yogi_Substitute Each Letter In A Name With A Number

Google Spreadsheet   Post  #2317

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-12-2017
Formula to replace every letter with a number
Hello, I have a list of employee names that I want to convert to numbers for employee IDs.
I'd like to use a formula to replace every letter in a cell with it's corresponding number, 1 through 26.

For example, the name "johnsmith" would be represented as 101581419139208
"a" equals "1" , "b" equals "2" , etc, etc.

Is there an easy way to accomplish this with one single formula?

THANKS IN ADVANCE


Monday, December 11, 2017

yogi_Conditionally Format Unfilled Scores In Range F2:I

Google Spreadsheet   Post  #2316

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-11-2017
If, and, then conditional formatting?
Hi all, 
I am somewhat of a novice looking for expert advice! 

I have a sheet where columns A-E contain student data and teachers add 4 points of data ('scores' if you like) in columns F-I. Last year, we had many teachers forget/ neglect/ overlook one or more of the 'score' columns so I wanted to add a formula where if one (or more) of the columns was blank, it would show up as bright yellow.
Problem is, I only want this for rows where student data is entered ie. not for rows where we wouldn't expect any data entry. 

I am thinking this is the worded version of what I want...
IF column B has an entry AND any of column F-I is blank THEN highlight the blank cell in bright yellow
but not sure what that looks like in terms of conditional formatting/ composing an actual formula.

Would appreciate any help that anyone has to offer.
Many thanks, Erin

yogi_Conditionally Format Cells Based On Stock Availability

Google Spreadsheet   Post  #2315

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

Add cells and change the color depending on the result (medium complexity)

Hi!

I want to change the colors of the cells based on some aritmetical operations.

I need it to control the stock. Every month it is needes a different quantity of each item and I want to see with colors which months are "covered" with the current stock.
It is needed that you can indicate easily which will be the first month that you want to take in account.
Besides, I want that change the background color too with the material wich is ordered but it is still pending.

We can use an screep if it is needed

More explanation here:

Thanks in advance!! 

Friday, December 8, 2017

yogi_Conditionally Format Row By Row For Highest Value In The Row

Google Spreadsheet   Post  #2314

Top or Bottom Ranked Values
Does Google Sheets have a Conditional Format to create a rule for "Top or Bottom Ranked Values" as Excel does?  If Google does, I can't find it.  I could rally use it.

yogi_Conditionally Format Based On Number Of Days To Ship

Google Spreadsheet   Post  #2313

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-05-2017
How to conditional format dates while referencing another column within the sheet? Help!
Hi,

I need to create a conditional formatting request for a column (column J) to turn RED if the date of another column (column F) is GREATER THAN 48 hours from the current date and yellow if GREATER THAN 24 hours from the current date.

I need to know if there is an easy way to do this in sheets or what is the formula I should be using. Please help me.

Wednesday, December 6, 2017

yogi_Conditionally Format Cells In B2:B If They Occur In 'CHECKOUT SHEET'!B4:B

Google Spreadsheet   Post  #2312

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-05-2017
Conditional formatting by number from sheet1 that strikes through same number in sheet2
I have a spreadsheet that we are going to use for inventory and checkout via asset numbers.
Sheet1 has the checkout log, and sheet2 has the inventory.

In sheet1, I have column B serving as the asset numbers, which is a data validated drop-down list.

The same numbers in the drop-down list in column B on Sheet1 are listed in sheet2 in column B.

I want an asset number in sheet2 to have a strike-through when sheet1 has the number added in column B.

For instance... If Sheet1 asset number 12345 is selected from the drop-down menu, then strike-through asset 12345 on Sheet2. 


Tuesday, December 5, 2017

yogi_Conditionally Format A Block Of Cells Depending On Current Time

Google Spreadsheet   Post  #2311

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-05-2017
question by: greenscreen20
https://productforums.google.com/forum/#!topic/docs/ThrfEl-r5S0;context-place=forum/docs
Conditional formatting based on Time
Hi guys,

This is a bit of a 2 part question, but it doesn't seem to work well on google sheets.  It seems fairly straight forward.


I want to do a conditional format of one cell, based on the comparison of 2 different cells.


Example, a set of cells turn GREEN if the current time is greater than a certain number.  To break it down further, if it's past 6:00am I want a set of blocks to turn green.  If it turns 6:30 a different set of blocks turn green, and the ones from before go back to normal.



For some reason the sheet doesn't format properly when I try an expression.  I was doing a conditional format such as =A7>S2, comparing A7 (time in the time block) compared to S2 (current time aka =(now() - today()) ) Some of the cells change color but not in the way I would expect. Here is the spreadsheet displaying what I want without the expressions, this is an editable link so if someone has the time / knowhow I would really appreciate it! https://docs.google.com/spreadsheets/d/11YajgdnKuXFLqAOeCkVzZIzQ95fb1ew501sSEsDfJAQ/edit?usp=sharing


Monday, December 4, 2017

yogi_Annual goal split into the months with auto update based on results that month

Google Spreadsheet   Post  #2310

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-04-2017
question by: Heather Etzel
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Q_AUXk4GQyk;context-place=forum/docs
Annual goal split into the months with auto update based on results that month
I am looking to split up our annual goal of lets say 96 (this will need the ability to be changed due to market conditions for the year we are projecting) active listing into a monthly goal to achieve that 96. BUT with the ability to have the numbers auto update for the following months to still achieve that goal based on the actual number obtained in prior months.

So say the 96 is projecting we need 8 listings a month starting in January.. and we end up obtaining 10 listings.. I want it to auto adjust to maybe say like 6 listings in Feb and 8 in the remainder months etc.. can this happen and update as we plug in the actual obtained listings somewhere.. possibly a hidden sheet that feeds the numbers? 

The yellow is where I want the figures to be shown. Thank you. 


https://drive.google.com/open?id=1y2C6i34JxMZavkHO0c0Wn5tXB2FjotV4qUTHPBARPCo

yogi_Create A DataValidation List By Filtering Original Data For Specified Condition

Google Spreadsheet   Post  #2309

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-04-2017
question by: Samjco
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/miB-3bOsHsQ;context-place=forum/docs

How to add conditions to a filter

I have this example sheet:

It allows me to pull in data into a dropdown on sheet1 based on data in sheet2 where there is an "X" in the availability.
The filter that does this on sheet2 is:
=FILTER(A2:A,B2:B="x")

However, I am trying to extend the filter a bit by not showing the name in the list where B2:B = "X", if I have selected it on sheet1


Sunday, December 3, 2017

yogi_Count of Case Sensitive And Non-Case Sensitive Key-Words In Cell A1

Google Spreadsheet   Post  #2308

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-02-2017
question by: appah
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/RJ8D5GyKKwE;context-place=forum/docs

Case sensitive count of occurences in a cell

 
Hi Everyone

I am trying to create a keyword density checker in my sheet, but i'm having problems getting the formula to ignore cases..

I've currently been using this formula:

=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)

It does the trick but only for exact, case sensitive matches..

This is an example where the text is the blue cell and the text to find the frequencies for are in the green cells: https://docs.google.com/spreadsheets/d/1asP1NHI-UqW0Xu7LxOGY0CCFi6_agfw1u0fAJmhWFPY/edit?usp=sharing

Can any of you help?

Thanks.