Thursday, June 30, 2011

yogi_Put Items In A Column In Reversed Order

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Putting Items In A Column In Reversed Order

yogi_Apply VLOOKUP For Multi Row Multi Column Lookup To Compute Commission And Bonus

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
In this example I have applied VLOOKUP function for multi row lookup in Sheet1, and for multi row, multi column lookup in Sheet1a

Wednesday, June 29, 2011

yogi_Compute A Set Of Value With Intermediate Average Values

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

laurentJ said:
Complete a set of value with intermediate average values
I have a set of value that are generated automatically in a column after a sort and a unique function. I want to be able to sample that data with intermediate average values.
For example I have in the column B
And I would like to have in column C
3 = (2+4)/2
5 = (4+6)/2
7 = (6+8)/2
Problem is that I want to column to be dynamic and I do not want to enter manually those formulas in every two cells.

Tuesday, June 28, 2011

yogi_Parse Out Multiple Integer Values From A String In Multiple Cells And Add Them

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

sprkInh2o said:
Parse out multiple integer values from a strings in multiple cells, then add them together?
I have a spreadsheet, which contains the following
        A                    B         C
2 abc(100) abcd(-200)30    abc(200) 
I want to be able to parse out the integer values (within the parenthesis) from A2:B2, sum it up, then place it in C2 (note how it also takes negative values). In the example above, the result should be
          A                  B        C
2 abc(100) abcd(-200)30    abc(200)  100
So far I tried to use "=REGEXEXTRACT(A2, "\d+")" in C2 to extract number from the cells but
1. it only returns the first integer number - 100
2. it does not work for multiple cells

yogi_Count Number Of Specified WeekDay In A Given Time Period

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Termanator13 said:
how do I calculate the number of Fridays or any day of the week in a month?
I have a expence sheet, and I need to know how often I get paid in a month to better plan for this, this is on Fridays.
In the following I have provided solution to the problem in a more generalized way

Monday, June 27, 2011

yogi_Compute Age In Years As Of A Specified Date From Date Born

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

brennan106 said:
i need a formula that will show age based on year born. I need it to automatically update when year changes
If I'd like to determine the age on a certain date every year such as August 1st, what would that formula be?


Friday, June 24, 2011

yogi_Sum Student Test Scores, Compute Percentage And Assign Grades

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Brozuful said:
Let's say I want to SUM, entire Row 2 (from A2, to E2), and the result of that SUM to appear on F2, on that same row.
Same with Row 3 and Row 4; I want the results of the SUM from Row 3, to appear in Row 3 (at F3 to be specific).
The same with Row 4; (I want the results of the SUM from Row 4, to appear in Row 4 [at F4 to be specific].
Important: (Because is school work and the list may increase) so if I want to apply this very exact same function (of SUM and adding result on same row; how can I apply this function automatically to... 100 rows, for example?

yogi_Consolidate A Dynamic Range Of Identical Sheets Into One Master

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

jalabiso said:
I need to have the SUM of the same CELL position from a range of worksheets with exactly the same layout.
In the cell C3 of the master sheet i would write:
=SUM(SomeSheetName1!C3;SomeSheetName2!C3;SomeSheetName3!C3;..and so on) All sheets (including master) will look the same. Just Master has the SUM of all sheets.
The problem is that the number of sheets to consolidate is variable, and not controlled by me. Therefore it would be too time consuming to keep the SUM line updated (always more than 40 sheets in that SUM)
I have no idea how to do it, but let's say I would keep the master in a different file, i could then reference all child sheets like this (with fantasy formula): =SUM(AllSheetsFrom(ChildDoc!C3:C3)
now, AllSheetsFrom is just fruit of my fantasy.
Is there anything real that could work that way?
Google Docs spreadsheet does not support the so called 3-dimensional ranges.
I had a little play with a brute force low-tech solution that would do the job even when sheets are added/deleted/modified as presented in the following:

Thursday, June 23, 2011

yogi_Sort HEX Numbers Listed In A Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

Amanitas said:
How do I sort numerically when using Hexadecimal values?
I have a list on numbers, all of which are Hex. how am I able to sort this column?
The SORT functions is based on sorting decimlal (base 10) numbers ... so I will use this aspect to sort the column of HEX numbers.

yogi_Compute Items Brought And Remaining At Multiple Events

Google Spreadsheet
BTFish said:
I am trying to keep track of how many t-shirts I still have left in stock as they get sold at multiple events. Basically the total t-shirts ordered is subtracted from the t-shirts sold at each event. I am currently using a VLOOKUP function to provide this calculation as I would like to keep the different t-shirt sizes separate, but I can only get the formula to work for one event. Whereas I'd like it to work for all events as they get added to the spreadsheet. I believe I need an array formula to provide the proper function for multiple events?

Tuesday, June 21, 2011

yogi_Match Entries In One Column With Those In Another And Highlight Matches

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
moocowz said:
Conditional formatting for multiple items
Is there an easy way to use conditional formatting for a large set of items?
Can I enter multiple items in one rule?

yogi_Count Number Of CharactersWithoutSpaces, Words, And Sentences In A Text String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
kattussz said:
Count the number of words and sentences
How do I count the number of:
1. characters without spaces;
2. words;
3. sentences
in a cell containing a string?

yogi_Make A PIE Chart With Concatenated Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
rubinab said:
How do I source concatenated data into a pie chart?
I am trying to source concatenated data (a cell with data combined from a few other cells) into a pie graph. I am finding that the data is being shown correctly, but the information in the concatenated cell is now showing up (I have the product name, $ value of product and % of inventory). What I see in the chart is the correct value in the pie chart (shaded area) but I do not see the details of each particular product as a label.
If I understand it correctly, your concatenated data is in column A
So in column B, I extract the numerical data from column A
Then using thhe data in columns A and B I make the PIE Chart as presented in Sheet1

Monday, June 20, 2011

yogi_Use Spreadsheet Data In A Web Page

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
hackernerd said:
Using Google Spreadsheet data on a website?
I use a Google Spreadsheet to keep track of a list of people and information about them (such as volunteer hours), and I was wondering if it was possible to use this information, that is being constantly updated by several people, on a website.
For example, someone could type their name and their number of hours would be displayed.
Here is a picture of Sheet1 which houses the database Table of  volunteers information and HoursWorked


and here is a picture of Sheet2 that captures the LastSubmitted volunteer UserName submitted via the website, and then runs a query to extracts the TotalHoursWorked by the volunteer


 Volunteer will have to SUBMIT his/her name via the following Form:

and the hours worked by the volunteer will be displayed as presented in the following:

yogi_Create A Bulleted List In A Cell Of A Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
UPDATED JUL-13-2016 ... added Sheet2_MultiLineBulletedItems

brk2490 said:
How do I use bullets inside of a spreadsheet?
How do I use bullets inside of a spreadsheet in Google Docs? (For example, I would like to be able to create a bulleted list inside of a cell).

yogi_Create A Bullted List In A Cell Of A Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

brk2490 said:
How do I use bullets inside of a spreadsheet?
How do I use bullets inside of a spreadsheet in Google Docs? (For example, I would like to be able to create a bulleted list inside of a cell).

yogi_Count Number Of Days Between Two Dates

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

oldPhil said:
The date function days360 returns 1 day between may 30 & June 1
DAYS360 function is based on there being 30 days in each of the 12 months in a year ... so it should not be used to count the number of days between two dates.

yogi_Count Cells In Specified Columns From Rows That Meet Specific Condition

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Sam Cycling said:
if a cell contains specific text, sum/count cells from multiple columns in same row if cells are not empty
I am having trouble working out what formula I would use to count the number of cells in a row that are not empty based on if say B101 contained A2:
I have about 20 rows of data, column B holds the names of schools, then columns: C, G, K, O, S and W hold names of children however there maybe some times where there is no child's name.
I need to compare the name of the school in column B against another cell further up the spreadsheet to see if they match, then if that row has the same school name, count/sum up the number of children in columns B, F, J, N, R and V.

Wednesday, June 15, 2011

yogi_Compute Time For Songs In PlayList

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
H-Rod said:
I d' like to sum time cells in that way.
For eg.
I have sheet 1 with column A with title songs and duration son in same cell each one.
Like this!
Song1 - 0:03:20
Song2 - 0:02:45
Song3 - 0:04:10
Song4 - 0:03:20
Song5 - 0:02:45
Song6 - 0:04:10
Now in sheet 2 I have column A with a validation drop menu with sheet1 column A data.
So I select what songs
Song2 - 0:02:45
Song5 - 0:02:45
Song1 - 0:03:20
I want to show and i want show total running time, for eg: in C4 cell in minutes
Sorry my english
Song Collection is in Sheet1
PlayList and the associated Time for songs in PlayList is in Sheet2

yogi_Sort Street Addresses In Ascending Order

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Stever17 said:
Sorting Street Address working strangely
I am using Google Docs Spreadsheet. There are addresses within a column. When I sort, they are sorted in the order below, i.e. first all the addresses starting with "1", then all the addresses starting with "2", etc. Is there a way to make it sort from smallest to largest house number within the street address?
1021 MARSH
1022 10TH
1028 SAN
104 E ST
106 CLAY
1061 BEACH
107 Livorn
1083 SUNNY
111 ROSA
1114 Lord
121 CLAY
1227 Carlton
In Sheet1 street addresses are sorted in ascending order by property number and in ascending order by street address 

I have added Sheet1a where street addresses are sorted in descending order by property number and in ascending order by street address

Tuesday, June 14, 2011

yogi_Make Up An ID From Specified Number Of Charecters From Different Cells

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
garyoh said:
Take three letters out of three different cells and combine them into another cell
I wish to create a unique customer number/reference by combining the contents of the three cells below
a1 001 a2 LLANDUDNO a3 MORRIS Taking the first three characters from each
And place in a new cell
In Sheet1 I have created a formula per garyoh's specification
in Sheet2 I present a more generalized formula using row numbers in column A

yogi_Count How Many Times Various Words Show Up In A Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

BeFunk said:
count how many time various words show up in a column and assigne each word a cell with the total.
in the following, I have words in column A starting with row 2 down,
list of unique words in column B starting with row 2 down
in column C starting with row 2 down, I have the number of times each word occurs in column A
and the total count of all the words in column A is in cell C1

Monday, June 13, 2011

yogi_Insert A Question In GoogleForm In A Specific Location And Have The Same Order In Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Karen Jean said:
How do you insert a question in a specific location and have the final spreadsheet keep the same order. These were questions posted in the form forum, but there was no implemented solution. Maybe I am missing something. Can someone give me a quick fix so I don't have to start a new form for the question I forgot.
Karen, the ResponseTable in the spreadsheet maintains the order in which the questions were originally created and that order can not be changed. However, you can create another sheet like I did in the following where I used the QUERY function to order the question in the right order of interest to me. As more responses are looged in Sheet1, the questions with their respective answers are automatically created in the order of interest in Sheet2.

Sunday, June 12, 2011

yogi_Insert A Chart Image In Blog And Then Resize The Image By Enlarging The Chart In Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
zodac said ...
I'm publishing the chart itself, using the "image" option. While the chart takes up most of the page when editing, the published image is aroung 600x400 pixels.
Is there any way to increase the size?

When a Chart is published as an Image, in the code that is produced, there are no attributes for adjusting the width and the height of the image

following is the code that was produced by Clicking on the top left hand corner of the Chart to Publish chart as Image (not Interactive Chart) ... code is shown without the opening and closing angle brackets ...
img src="" /

to facilitate having control over the width and the height of the Chart published as Image, I moified the code by adding the width and height parameters -- the modified code is hown below without the opening and the closing brackets ...
img src="" width=765 height=300 /

and finally here is the Chart with the height and width as specified

Thursday, June 9, 2011

yogi_Calculate Trade Aggregate For A Set Of Transactions

Yogi Anand, D.Eng, P.E.                                Google Spreadsheet                 
BGILL said ...
How do I calculate my trade aggregate?
How do I properly calculate my trade aggregates when you have more buys(BTO) than sells(STC)?
Desc. Action Qty P
Position 1 BTO 2 0.98
Position 1 BTO 4 0.67
Position 1 STC -6 0.53
Position 2 BTO 2 0.97
Position 2 BTO 2 0.90
Position 2 STC -4 1.25
Aggregate for Pos. 1 = - 0.243
Aggregate for Pos. 2 = 0.315
Combined Aggregate = 0.07
I can't seem to make this work in a spreadsheet of multiple positions where you have an irregular number of buys and sells.

Wednesday, June 8, 2011

yogi_Create WaterMark In Spreadsheet

Yogi Anand, D.Eng, P.E.                                    Google Spreadsheet                   

and here is an image of part of Sheet1

yogi_Sort An ItemList Per CustomSortList

Yogi Anand, D.Eng, P.E.                               Google Spreadsheet              
UPDATED Jul-21-2013          

In Sheet1 I have sorted an ItemList of WeekDays Per CustomSortList consisting of
In Sheet2 I have applied the formulation in Sheet1 to an ItemList of Names per specified CustomSortList

Tuesday, June 7, 2011

yogi_Write A Formula That Changes Entry In A Cell On Subsequent Days

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                   
xjustmex said ...
How do I write this formula in Google Spreadsheets.....
I have five cells each with a different color text in the cell:
A1: Red A2: Blue A3: Green A4: Yellow A5: Black A6: Done!
I have another cell B1 that is a date field where someone would enter a specific date.
I would like a cell C1 that would look at the date entered in B1 and show 'Red" for first day that was entered in the B1. As day 2 comes, I would like C1 to say Blue, then day 3 comes and would like C1 to say Green, and so one until it has ended at A6 showing "Done!" and stopping there.

Monday, June 6, 2011

yogi_Extract Numerics From Strings Containing Text And More

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
p0490 said ...
Extract / truncate characters (more)
is there a general solution if the source data contains a decimal ?
the source data would be:
+30.9 mg
-20,8 F (comma replaces the decimal)
the output should be:

Sunday, June 5, 2011

yogi_Increment Row References In A Formula By A Specified Number

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
ClaudioFlabarra said:
How do I increment a formula by more than 1 (i.e. 7)?
I keep a spreadsheet to manage business income. One sheet has each row for daily values, and another sheet has each row for a weekly summary. For the first week, I defined the range on my own... but I can't click and drag the formula for other weeks. The click-and-drag operation increments by 1, and I want it to go by 7. What is the easiest way to do that?
Claudio further added ...
I thought that two rows worth of entries would help Google Spreadsheets infer what I was after, but I was proven wrong. To illustrate:
The two sheets I have are called "Raw Data" and "Weekly". In the weekly spreadsheet, cell C2 is defined as: "=Sum('Raw Data'!C2:C8)", and C3 is defined as: "=Sum('Raw Data'!C9:C15)"; But when I click and drag, cell C4 is automatically filled in as: "=Sum('Raw Data'!C4:C10)" but what I'm after is "=Sum('Raw Data'!C16:C22)"

yogi_Separate Numeric Digit And Specified Letter From Stirngs And TotalUp

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
mtvernon said:
A cell contains both letters and numbers. How do I pull the number into a separate cell and the letter into yet another?
I'm new to spreadsheets in general and Google Spreadsheets in particular. I've looked all over these forums, but can't seem to find a solution to my problem. Here's the situation:

I have a cell that contains the cost of a resource in a card game. It's usually expressed using both a number and a letter (1W). Sometimes it's just a number (1). Other times, it's just a letter (W -- or U, B, R, G). To further complicate matters, variables appear from time to time (XW or, very rarely, X1W).

What I want is to take the cost column (1W, 1, W, XW, X1W, 3W, 8, WW, 2WW, XWW, et cetera) and break it up into additional columns representing each part. Then, I want to sum the parts into a number that'll indicate the "converted" cost. The cost 1W, for instance, should show a 1 under the column that represents numbers and a 1 under the column that counts letters other than X. It should sum up as 2. The cost 2WW should show a 2 under the column that represents numbers and a 2 under the column that counts letters other than X. It should sum up as 4. The cost XW should show a 0 under the column that represents numbers and a 1 under the column that counts letters other than X. The variable X should always be converted to 0. It should sum up as 1.
My question is, how in the world do I create a formula that sees 2WW and breaks it up as described above? I've tried writing the cost differently (1-W, 2-WW, X-W, et cetera) and pulling the number value left of the letter with =VALUE(LEFT(K2)). And I can count the number of letters using =LEN(K6)-LEN(SUBSTITUTE(K6,"W","")), but I'm not even sure what process is at work there; all I know is that it does work, at least so far. Then I just =SUM(L2:Q2) for the "converted" cost. Only, if there's an X left of the dash (X-WW), I get an error that says "Cannot parse text: X." Is there a way to have =VALUE(LEFT(K2)) always show a zero in place of X? And what of unusual cases in which the cost is X1W (X1-W or, possibly, XW or X0-W)? Perhaps =VALUE(LEFT(K2)) should simply not count X at all?

Saturday, June 4, 2011

yogi_Recreate Form Entries In Rows For All Submittals

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet           

manchi said:
How to add the inputs one below the other- New entries should be added after the last line..
Say for example, I am creating a form for Job Requirement.. It has multiple fields(like reporting manger, Job Duties, responsibilities etc).. Is there a way when somebody fills form, it gets added like a word document format(One below the other, Not adding in columns!)..I am not sure I am explaining correctly, But I can explain more if needed.

More info: If I have n fields, all the fields should get entered in n rows and the new entry should start from n+1 row and so on.

Manchi commented ...
Thanks Yogi, This worked.. This is exactly how I wanted and one more small request.. How to have it start from n+2 row(this way I will have a empty row between the entries).

I have added some more formulas in yogi_Sheet1, and yogi_Sheet1a; I have modified the formula in yogi_Sheet2b, and I have added yogi_Sheet2c.

Manchi commented ...
How to highlight the entire row of Timestamps (I want to highlight all the rows which have timestamp). I was able to just highlight the cell containing Timestamp not the entire row.
Google spreadsheet does not have a built-in feature to highlight an entire row. However, my workaround presented in Sheet2d might be of some interest to you.

Friday, June 3, 2011

yogi_Fill In Computed Results For All Rows of Form Data For Blood Sugar Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
anonymous said:
I have a diabetes-related blood sugar tracking sheet.
I use a form to enter new data as it's easier than trying to access the spreadsheet on my phone. Problem is, column H tracks my A1C, and each row must contain a formula that pulls from other columns to do the calculation. When I use the form to enter data, it doesn't do the column H calculation without me copying in the formula from another cell under column H.
So you need an array formula for column H
and you also need an arrayformula for column F
as shown in Sheet1 -- Columns F,G, and H with row 1 colored light brown were added subsequent to the creation of the Form questions shown with header rows colored Gray.

Thursday, June 2, 2011

yogi_Skip Columns With The ImportRange Function

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Pastor of Muppets said:
How do I skip columns with the ImportRange function
I am using the formula =importrange("abcd123abcd123", "sheet1!A1:G10")
"abcd123abcd123" is the value in the "key=" attribute on the URL of the target spreadsheet and "sheet1!A1:G10" is the range which is desired to be imported.
This works fine but imports data from columns A to G. What if I want to leave out column F, or columns E and F?

view of spreadsheet yogi_Skip Columns With The ImportRange Function - SourceData

I have added Sheet2 for the case where the columns to be Imported are delineated as a comma separated list -- and I have also considered the order in which the data is to be presented for unique columns in the comma separated list.

Wednesday, June 1, 2011

yogi_Search An Array And Return Addresses Of All Cells Matching Certain Criteria

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Forum Joe said:
How do I search an array and return addresses of all cells matching certain criteria?
If I have an array that looks like
Apples Bananas
Bananas Oranges
Oranges Apples
Bananas Oranges
Oranges Bananas
Apples Oranges
I want a function that will return the address of cells that match a criterion.
eg: FunctionName("Apples", A1:B6) will return "A1, A6, B3"
Is this possible?

yogi_Use Multiple Keywords In Conditional Formatting

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
vinhere0 said:
How can i use multiple keywords in "conditional formatting".
In my spreadsheet, i have a column which contains 30 rows with ceratin words. I want to change the rows colour based on the letters. If the word contains any of the letter "A" or "E" or "I" or "O" or "U", then i want to change the colour to red.
i don't want to write 5 different rules for this. I want execute this using one rule.
i tried with the folowing option.
Selected "text contains" from drop down box. And in the text area i entered : "A" or "E" or "I" or "O" or "U", and i checked the background colour and set it to red.
In Google Spreadsheet, at least as of now (Jun-01-2011), you can not use formulas for Conditional Formatting. In the following I present a workaround where column B (not the whole row) can be colored Red in entries in column A contain letters a, e, i, o, or u

In Sheet2, I keyed-in the letters of interest in cell B1 ... thus providing a more generalized solution