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
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.  


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!

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:


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

Thank you in advanced,


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?

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.


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?


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)


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.