yogi_Compute Sum Based On Values In Specified Column Pattern

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Barazoo said:
I am currently biting my nails over suming the values that are stored in a row of data.
I only want to sum the value of each 19th column in the row 3 starting at D3 -> the end of row 3.
I will be adding data as months go by to the right by adding more columns and hence in 19 columns later I will need it to pick up the next entry.
In summary D3+19columns over+another 19 columns over, etc etc etc need to be sum'ed
Thank you for any help,
I have tried quiet a few options and am either having partial or no success at all.
------------------------------------------------------------------------

yogia_Recreating Form from ResponseTable

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

I have shared this spreadsheet with a number of my colleagues on the Google Help Forum. Recently, I started to having difficulty sharing this with more viewers in response to their request. So I am adding this to my blog and also share it for any one with the link :
link to the Blog post ... http://yogi--anand-consulting.blogspot.com/2011/04/yogiarecreating-form-from-responsetable.html
link to the spreadsheet: https://spreadsheets.google.com/ccc?key=0AkHBcyclu11AdGZsUVpTQi1EOFZsNGVWaGhzZTM3WHc&hl=en&authkey=CJPsiPgH



Following is a view of the Blank Form that pops up when you click on cell F1 of ResponseTableWithRecreateForm


Following is a view of the Form as was Filled-In for ID 1003 that pops up when you click on cell H7 of ResponseTableWithRecreateForm


And in the following I present a Summary of Responses

yogi_ImportRange Function To Import Cells Based On A Specified Row Pattern

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

ovz79 said:

How do I add a set value of 6 to the cell value in this formula for 50 formulas on a spreadsheet: =importrange("abcdefg", "sheet1!d35")

The cells I am importing are spaced evenly at 6 intervals and I want to save time so I don't have to re-write this formula 50 times but can just drag it down the spreadsheet. In other words, cell 1 shows =importrange("abcdefg", "sheet1!d35") and cell 2 is =importrange("abcdefg", "sheet1!d41"), etc. without having to type in d41, d47, d53.

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

ImportRange function is an expensive function in that there is a limit on the number of ImportRange functions that can be used in a spreadsheet. So in the following solution I have made only one ImportRange function call and then operated on that to import the rows based on the specified pattern of rows.

The following image shows the spreadsheet on which we are going to make the ImportRange function call

yogi_Setup Simple Calculated Column In A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

prosper2000usa said:
Never been good with spreadsheets.
Simple thing I can't figure. Want my spreadsheet to have a single table something like this
Columns:
A: Job Name
B: Hours to Complete
C: Pay
D: Pay per Hour
Where A-C I will enter into the spreadsheet, but D I want to be a calculated column, something simple like =DIVIDE(C,B). Where do I enter the forumula and in what syntax so that it calculates automatically for each row as it is entered? I can add the formula in the first data row as DIVIDE(C1, B1), but that does nothing for subsequent rows that will be entered.

--------------------------------------------------
Well prosper2000usa, see the setup in Sheet1 for what you want to do ... as you add data in columns A,B, and C starting with row 2 down, computed value in column D is automatically calculated.

yogi_Compute High/Low For A Stock Over A Specified Range Of Dates

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet                      www.energyefficientbuild.com

wsround said:
I am looking for specific highs and lows for specific dates.
Ie: March 12, 2003 high and low. But not just that date.
I would like to be able to get an array for dates around it such as from march 1, 2003 to december 31, 2009
I can get the open close high and low for that day but not the 52 week high or low for that day
thanks for the help
-------------------------------------------------------------------------------

In the solution shown in Sheet1 I first extract the high/low values for all the dates in the range of interest and then compute the max/min value from those.

yogi_Compute Historic 52 Week High/Low For A Specified Date

Yogi Anand, D.Eng, P.E.                                  Google Spreadsheet                     www.energyefficientbuild.com

wsround said:
I have been searching for a way to get historical data for a stock in my spreadsheet.

I have found the limited data that presents it in an array but what I would like to know is can I get the historical data on the 52 week high and low data. It should have been calculated when the stock was closed on that specific day but the only way I have been able to figure out to do this is to run a data search on my own spreadsheet and that just allows a certain number of calculations before the spreadsheet gets too big and then I can do any other calculations based on the 52 week high/low close and other data that I can get.

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

One can use high52 and low52 arguments for 52week high and 52week low in the GoogleFinance function for the current date. To get the 52week and 52 week low for a specified date, I first extract the high and low values for the dates in the range of interest and then compute the maximum/minimum value from those as presented in Sheet1.

yogi_Split Data With Dependencies

Yogi Anand, D.Eng, P.E.                                    Google Spreadsheet                    www.energyefficientbuild.com

serept said:
I have some Data with dependencies in the following format
Name | Id | Dependents
Alpha | 23 |
Gamma | 12 | Alpha
Beta |15 | Alpha; Gamma
Teta | 81 | Delta; Alpha; Kappa
The thing is there could be none or varying number of dependents (no max) for each Name. I want to have a column with dependent Id's based on names. Example a column with values "23; 41; 1" instead of "Delta; Alpha; Kappa"
Since VLOOKUP does not work with ArrayFormula it is not an option.
I tried combining INDEX,VLOOKUP, or FILTER with ArrayFormula but am unable to get a working formula.
This is the formula I am trying to use
=INDEX(B:B,ARRAYFORMULA(MATCH(ARRAYFORMULA(Transpose(split(C20,";"))),A:A,0)=C:C))
The part MATCH(ARRAYFORMULA(Transpose(split(C20,";"))),A:A,0) is giving me a set of row index values; but based on these row ids I need the actual Name Id's which I can concatenate.
----------------------------------------------------------------------------------
In the following solution I used primarily SPLIT and CONCATENATE functions to obtain the desired results.

yogi_Calculate Average Amount Per Month ToDate Starting A Specified Month

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet                      www.energyefficientbuild.com

AndreasDK said:
I would like a line sayng something lake "Monthly average buy sum", which looks at the dates for the purchases and divides TOTAL out with number of months from the start of the spreadsheet (April 2011) to the current month.

So that if I buy something next month for the price of kr. 1 (see example in document) the Monthly average sum would say 517,28 / 2 (April and May 2011).
Should I not buy anything in June no entry would be made with a date in June bout Average monthly sum should still include the month, so that it says 517,28 / 3 (April, May and June) as these months have passed since the start of the spreadsheet.
------------------------------------------------------------------------------------
In the following solution I have generalized it by specifying the StartingMonth in cell B4

yogi_Calculate Monthly Spending

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

AndreasDK said:
I have just made this spreadsheet.
UDGIFTER TIL BARBERING
Dato for køb: Varens navn: Butik: Pris
14-04-2011 Merkur 42C Proshave.dk kr 229
14-04-2011 Derby Extra skraberblade 10 stk Proshave.dk kr 25
14-04-2011 Proraso 50ml barberskum Eucalyptus / menthol Proshave.dk Gratis
14-04-2011 Forsendelse Proshave.dk kr 39,00
19-04-2011 Floid Aftershave splash 150ml Vintagescent.com kr 81,66
19-04-2011 Semogue 1305 barberkost Vintagescent.com kr 104,33
19-04-2011 Forsendelse Vintagescent.com kr 37,29
TOTAL: 516,28

The field showing the Total amount uses =SUM(D4:D10), whisch is fine but I would like to show average monthly spending beneath that line.
Is there a function that can take the sum of =SUM(D4:D10) and divide it with the numer of months since creation of the sheet?
--------------------------------------------------
Please note that this spreadsheet's Locale is set to Denmark ... so ,(comma) is used for decimal instead of . (full stop) and ; (semicolon) is used as argument separator in functions

yogi_Using SPLIT And REGEXREPLACE Functions To Separate Entries From A String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

HarbyNotts said:
I am wondering if the INDEX function can be used to give one single column out from this construct.
For example, if the data is in A2:
USA, Washington D.C. ¬The Netherlands, Amsterdam¬UK, London¬Italy, Rome
and the general function was something like this:
split(transpose(split(L2,"¬")),",")
could there be a way to just get the capital cities alone, using INDEX?
--------------------------------------------------------------------------------
Here I have used SPLIT and REGEXREPLACE functions and with the limited number of test runs I do get correct desired results.
I had mentioned in my earlier post
http://yogi--anand-consulting.blogspot.com/2011/04/yogiusing-split-and-index-functions-to.html
I had only partial success ... as shown through various sets of sample runs shown in various sheets of that post
Then in the following post
http://yogi--anand-consulting.blogspot.com/2011/04/yogiusing-split-and-replace-functions.html
I had used the SPLIT and REPLACE functions to accomplish the desired output. I used this solution to run through all the sample runs that I had conducted in use with INDEX and REPLACE functions ... and these all seemed to give the correct desired result.



There was a problem with the use of the SPLIT and INDEX function combination involving certain alphabets -- I played around with a number of combination of words and letters ... and I was not able to find a definitive pattern in regard to which letters cause problem with use of the SPLIT and INDEX function combination -- in the examples through various sheets the names of Capitals of the countries in some cases had gotten truncated after one or more letters.
However, the use of combination of SPLIT and REPLACE functions did give correct desired results -- I had only made a limited number of sample runs, so making more runs may be needed to know for sure ... but so far it yielded the correct desires results.
And now with the use of SPLIT and REGEXREPLACE functions I also seem to get correct desired results.

yogi_Using Split And Replace Functions To Separate Entries From A String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

HarbyNotts said:
I am wondering if the INDEX function can be used to give one single column out from this construct.
For example, if the data is in A2:
USA, Washington D.C. ¬The Netherlands, Amsterdam¬UK, London¬Italy, Rome
and the general function was something like this:
split(transpose(split(L2,"¬")),",")
could there be a way to just get the capital cities alone, using INDEX?
--------------------------------------------------------------------------------
Well HarbyNotts, as I delineated in my earlier post
http://yogi--anand-consulting.blogspot.com/2011/04/yogiusing-split-and-index-functions-to.html
I had only partial success ... as shown through various sets of sample runs shown in various sheets of that post

In the following solution, I used the SPLIT function and the REPLACE functions to accomplish the desired output. I used this solution to run through all the sample runs that I had conducted in use with INDEX and REPLACE functions ... and these all seem to give the correct desired result.



There was a problem with the use of the SPLIT and INDEX function combination involving certain alphabets -- I played around with a number of combination of words and letters ... and I was not able to find a definitive pattern in regard to which letters cause problem with use of the SPLIT and INDEX function combination -- in the examples through various sheets the names of Capitals of the countries in some cases had gotten truncated after one or more letters.
However, the use of combination of SPLIT and REPLACE functions does give correct desired results -- I have only made a limited number of sample runs, so making more runs may be needed to know for sure ... but so far it yields the correct desires results.

yogi_Using Split And Index Functions To Separate Entries From A String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

HarbyNotts said:
I am wondering if the INDEX function can be used to give one single column out from this construct.
For example, if the data is in A2:
USA, Washington D.C. ¬The Netherlands, Amsterdam¬UK, London¬Italy, Rome
and the general function was something like this:
split(transpose(split(L2,"¬")),",")
could there be a way to just get the capital cities alone, using INDEX?
--------------------------------------------------------------------------------
In the following solution, I used the SPLIT function and the INDEX functions to accomplish the desired output. For some reasons which I can not explain now, using The Netherlands and Amsterdam, the name Amsterdam was being chopped off at d ... so that I will have to investigate later ... and in the mean time I substituted The Netherlands and Amsterdam with Japan and Tokyo which worked fine.



There is a problem with the SPLIT function involving certain alphabets. I have played around with a number of combination of words and letters ... although I have not been able to find a definitive pattern in regard to which letters cause problem with use of the SPLIT function ... in the following examples through various sheets, it seems the names of Capitals of the countries in some cases get truncated after one or more letters. Of course the number of cases studied here is too small to derive a definitive conclusion of even this pattern.

yogi_WorkAround For Including SubTotal Lines In Google Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

lathseka said:
I really find it hard as I am new to this. You helped me to sum by items, manufacturer and buyer. To continue on that, I want to know how to sum also each item within the grouping. As you can see to begin with there is only one apple row. Next to that row should be the sum for apple. The next 3 rows are Asparagus. At the end of Asparagus, there should be sum for asparagus etc..


Is this possible with keeping the query?
-----------------------------------------------
I think what lathseka is trying to do is insert SubTotal line like in Excel. Goggle spreadsheet does not have a
feature to add the SubTotal lines ... so in the following solution one way to include the SubTotal lines with my convoluted approach presented here

yogi_Filtering Data For A Given String Regardless Of Which Row Or Column It Is In

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

darrenmwinters said:
I want to search for a string contained anywhere within the data ... for the data in Sheet1 I want to search for the string FCF (shown in cell A2) regardless of which row or which column the string exists in
----------------------------------------------


In the following I present one way of doing this by 
first creating an array of 0s ans 1s where the row containing the specified string is populated with 1 and all the other rows are poulated with 0
then I filter the data with the array of 0s and 1s as the criterion
the result is presented in the sheet FilterRowsWithStringInA2

yogi_Create A Data Table Which Only Filters On Certain Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

darrenmwinter said ...
I have a table with lots of data containing feedback on projects. My spreadsheet has 10 columns. I can use a data table to query the data, but ideally I only want to filter by certain columns
What I'd like to do is have something like a data table, so I can (for example) filter everything out for project 10_025 (column B) and see everything from each row where the contents of column B is 10_025. And then to refine those results so I can see (for example) all the comments about project 10_025 that were left by Daniel
------------------------------------------------------------------------------

The original data to be filtered is shown in Sheet1

I duplicated data from Sheet1 in the sheet named yogi_FilterColBColI and then  invoked the newly added Filter feature in Google spreadsheet ... so

1) in yogi_FilterColBColI, I clicked on the Filter icon the one on the extreme right icon under the MenuBar

2) then I clicked on the dropDown arrow in column B ... and in the Filter box I unchecked every thing else

    so only 10_025 remained and clicked on OK 

3) then I clicked on the dropDown arrow in column I ... and in the Filter box I unchecked every thing else 

    so only Daniel remained

then

what I get is the Table from data in Sheet1 that has been Filtered for ... 10_025 in column B and Daniel in column I as shown in the following image that I have reproduced from sheet yogi_FilterColBColI

yogi_Publish A SingleCell Or A Range Of Cells Without Title SheetName ScrollBars

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Manticore said:

I'd like to embed a single cell from a spreadsheet on my web site. I basically know how to do it, and I can get rid of the sheet name, but I'm still getting 'extra' stuff - I just want the cell.  Nothing else!

 I'm managing a few sites for a couple marinas, and I have their fuel prices listed on their main page.  As you can imagine, those prices change frequently.  This is a side-gig - I have real job - so when they let me know they need changes I have to wait till I can get to my computer and update the page in Dreamweaver and then republish the page.  Not a big deal, but I'd love to just be able to go into google docs, update he price and have it update on the page.  Does that make sense, or am I way off base?!

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

From within the spreadsheet, 

click on File ... 

then click on Publish as a web page

Sheets to publish ... Sheet1 (or the sheet of interest if other than Sheet1)

check the box ... Automatically republish when changes are made 

then click on Start publishing

in Get a link to the published data choose HTML to embed in a page

Choose Sheet1 (or sheet of interest if other than Sheet1)

for range ... key-in A1 (or another cell(s) if other than A1)

in the iframe code that is generated ... change &widget=true to &widget=false

use this iframe code in your web page -- you will also have to adjust the

width of the cell(s) to be published and the width paraneter in the iframe 

code to get it just right so that only the contents of the cell are published

without the scroll bars

see the illustration below

yogi_Splitting Text Entry With Numerics Without Suppressing Leading Zeros

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

QE2 said:

Spreadsheet field contains property parcel numbers in the format:  a99 999 999. I am using SPLIT formula =SPLIT(A1," ") to break the field apart into three chunks each in a separate column. SPLIT is deleting leading zeros on numeric chunks. I have set all columns to "plain text". Still deletes leading zeros. Please advise. 

Here is some data:col A: D02 003 030 

incorrect result: 

col B: D02 col C: 3 col D: 30

desired result: 

col B: D02 col C: 003 col D: 030

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

In the following solution in Sheet1, I have assumed that the string to be split starts with a non-zero character

yogi_Populate One Dropdown List From A Second

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Stoickk said:
I am trying to find a way to populate a dropdown list based on the selection from a second dropdown list. Short version, my spreadsheet is used for tracking personnel in an organization. When personnel are assigned to a unit via dropdown list, I would like to be able to assign them to a sub-unit based on the first unit that was selected. Is this possible with google docs?
Basically, if I select Alpha Company (Sheet2, cell A2) from the dropdown in Column C, I want a dropdown list in Column B to be populated with the three subunit names from Sheet2, cells B2, C2, and D2. I want the same thing to happen if I select Bravo Company or Charlie Company, with the dropdown list in Column B changing based on the selection in Column C.
--------------------------------------------------------

Well, I think it is best explained by looking at yogi_Sheet1

yogi_Count The Number Of Times A Letter Appears In A Range Of Cells

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

jgicking said:

This is probably a simple answer but here we go...I am trying to count the number of times a letter occurs in a column on a spreadsheet.  

I have tried using the countif function but continue to get errors - either unknown name range errors, circular dependency errors or parse errors.
Ex:  

In the column B4 through B57 I wish to know how many times the letter S occurs.

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

Well, the case of a discreet letter (a single letter by itself) is easily handled by using the COUNTIF function ...
=countif(B4:B9,"S")

However, in the following solution I am going to take the case of computing how many times a letter, say S occurs in words or phrases in the range of interest B4:B9
And then of course I can extend this formulation to count the number of times any letter, S, or a, or p, or w, or p occur in the range of interest

yogi_Compute Decreasing Return And Cumulative Sum

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

andie856603 said:

o.k row b is the amount of points you received for the very first animal so if i breed 1 african elephant i received 2601 points i enter 1 (animal bred) in row c....d,e,f then calculates how many points i will receive for 2nd 3rd 4th bred.

As i breed more i update c each time d,e,f will change to show points to be gained for next 3 animals bred 

what i would like h to show is the total points i have gained from the total number already bred 

eg if C was 4 (i had bred 4 animals) H would show 7112

hope that clarifies for you 

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

For the diminishing return with a factor of 0.75 for breeding #2 and subsequent breedings, I used the INDIRECT function to compute the value for successive breedings uto the total number of breedings specified in column C, and the cumulative sum of the value of the breedings in column H

yogi_Automatically Highlight Dates That Fall Within A Specific Month

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

kanting said:
Hi there - I am rather desperately looking for help on this issue, so thank you in advance for your suggestions!
I have a spreadsheet used to plan the editorial content of my school newspaper. We get a lot of information months in advance, and have typically just thrown it onto our google doc. But we've noticed that we've started to miss a lot of stories, so I've come up with a way to add an "alert" to rows that are taking place within the month.
In column A of my spreadsheet, I have used =IF( AND( C3>=TODAY() ; C3<=EDATE(TODAY();1)) ; 1;0) to give the value 1 if the date in column C is within a month of today. Then I have added a Changed color with Rule to have a yellow marker come up when A is equal 1. This isn't ideal, but it works. The problem I have is that if I insert a row, I have to drag the formula down to the new cell in order to get the marker. This is extremely inconvenient when you have a lot of people editing a document. Is there a way to get the function to appear automatically? Or, better yet, is there another way I could automatically highlight cells in a column that are in the upcoming month?
---------------------------------------------------------------------------------------

In Google spreadsheet, as of this time Change color with rules (conditional formatting) one can not use formulas for conditional formatting of the cells ... so I used formula in column A to comute whether dates in cells C2:C are in the month = current month + value in cell A1 ... and then I used the Change color with rules formatting to change the Background color of the cells to Yellow if the value in corresponding cell of column A is TRUE. I formatted the TEXT and BACKGROUND color of cells in A2:A to be YELLOW.

yogi_Compute Total For Sales Data Grouped By Item_Manuf_Buyer

Google Spreadsheet

lathseka said:

I have a spreadsheet with Date, ID, Buyers, manufacturers, items, quantity, total. I want to sort by item, then by manufacturers, then by buyers and get sum for every item by manufacturer and buyers and insert a new sheet items, manufacturers, buyers, total and get a grand total also.

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

latseka's sales data is in the sheet named Sales, and I used the QUERY function to group the sales data by Item, Manufacturer, and Buyer and then the corresponding sum from sales data

yogi_Compute Running Total Of Entries Successively Multiplied By A Constant

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

andie856603 asked:
what formula do i use to keep a running total of X when this number decreases by .75
o.k i'll try to explain as best i can this is for a game i'm playing
number 1 for example is worth 2601, #2 is .75 of #1(1951), #3 is 0.75 of #2 (1463) and so on
what formula would i use so when i change #1 it will add it
i basically want to do X + .75of X+ .75 of Y + .75 of Z and so on
hope you understand this
----------------------------------------------------------------

In the following solution, the starting entry is housed in cell A1, and the multiplying factor is housed in cell B1 ... so here we go

yogi_Retrieve Info From Form Input Into Multiple Rows of SheetA Into One Row Of Sheet B

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

eamonm asked:

The ultimate objective is to post an assignment from multiple teachers on the web so students can see what the home work is. The way I thought of doing this is to have a form that teachers enter their information in, the form is very basic - their name, description of assignment.

The spreadsheet form the form looks something like (this is Sheet1):






Here is my design logic - if may not be the most direct so if there is a better solution I'm open to it.

1. Each day the teachers use the form to enter more assignments, so there would be 4 rows inserted each day Monday through Friday.

2. In the spreadsheet I create an additional sheet called Intermediate that has the following columns:

I populate the CurrentDate manually myself from 04-18-11 to the end of the year (this has only Monday through Friday dates). My thinking is that I use a formula in Intermediate that takes CurrentDate and gets information from Sheet1 to populate Intermediate.

Where I'm stuck is with getting the four rows of data from Sheet1 into one row in Intermediate. Logically I think I would do something like the following formula from Intermediate:

Each teacher name column would have a lookup formula that would use the manually entered date CurrentDate to lookup information from Sheet1. The resulting data from the above example would look like:

The Post column would have a formula that compares the system date (true current date) to the date in column CurrentDate. If system date is equal or greater than CurrentDate then Post column = "Y".
-------------------------------------------------------------------------------------------------------

yogi_Compute Date For A Coming Weekday Such As Monday

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Waqar's Blog asked:
How do I get the date of Coming Monday or sunday or any coming day
--------------------------------------------------------------------------------------------------
following is a formula based solution5

yogi_Splitting and Summing Values in Columns Entered as Text

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

mikemillsjr asked:
I work at a Sprint independent location and we maintain a daily "Store Monitoring Report"
In this report we keep track of how many new activations and upgrades each employee processes each day.
There are two columns New Accounts and Upgrades.
Inside of each of these columns we record how many activations and accessories we processed per employee.

For example:

New Accounts Upgrades
Mike 1/1 1/1
Joe 3/2 3/3
John 5/3 4/4

My question is, how can I calculate a column and get it to output the correct totals? New accounts for example should read 9/6 and upgrades should read 8/8.
Pleas help!

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

In the following solution, entries in columns B and C are Text entries

yogi_Compute HoursWorked EarliestStart LatestFinish By Day

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Salamosam said:
In an employee's time log, how do I calculate the duration of a single day using the start/finish times in diff. cols??

Employee Time Log

Date StartTime FinishTime Duration CumulativeDuration MinStart MaxFinish
2/11      8:30      12:00      3.5                3.5     8:30
2/11     12:00       2:00      2                  5.5
2/11      2:00       4:00      2                  7.5               4:00
2/12      9:00      11:00      2                  2       9:00
2/12     11:00      12:00      1                  3
2/12      1:00       4:00      3                  6
2/12      4:00       5:00      1                  7                 5:00
2/13      9:00      12:00      3                  3       9:00
2/13     12:00       4:00      4                  7                 4:00

I got this far, that is I can find the cumulative duration for each day. But I want to do the calculation another way to check for errors. I want to find the max finish time of one day and the min start time for the same day and find the difference. I can find the min and max times I'm looking for, but they are in different columns and uneven rows (see Min Start and Max Finish), and I don't know how to manipulate them to make the calculation.


In the following solution, I have used Military Time (24 hour clock) to avoid any ambiguity about morning (AM) time or afternoon (PM) time.

yogi_Table Chart Interactive_with Sort Capability

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_Compute Time Difference in Milliseconds

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

copspt said:
I am doing this spreadsheet where I keep track of time trials/lap times in a game.
What I wanted to do is to subtract time between 2 players laps to get the diference (mm,ss,mls)
i.e. 1.20.121 - 1.20.122 = -0.001
The problem is that Google Apps does not have a time format with mm,ss,mls, therefore I cannot do the subtraction.
Is there a workaround for this?
-------------------------------------------------------------------------------------
In the following solution I entered the Times in column A as Text entries ... then computed the difference between successive entries by coercing the text entries into their numeric equivalent ... and then used the TEXT function to have the ouput as HH:mm:ss.SSS in Hours, minutes, seconds, and milliseconds

yogi_Extracting And Rearranging Data From Single And Multiple Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

canned.meat.eater said:
The desktop sheet contains data about the desktop, user, etc, but most importantly it contains the serial numbers of the monitors that are used with it.
"desktops"
A B C D
SN: User: Used w/:
1234 john abcd efgh
5678 jane ijkl mnop

On the monitors sheet, I want to populate information in this sheet based on the information on the desktops sheet, but it will look something like this:
"monitors"
A B C
SN: Used w/: Contract #:
abcd (???1234) ab12
efgh (???1234) ab12
ijkl (???5678) cd34
mnop (???5678) ab12

To try to put it into words, I need to populate monitors!:B2 with the value of desktops!Ax by finding value of monitors!B1 in either desktops!C or desktops!D...

I realize that it might be easiest to put all the info on one sheet, but for ease of finding, reading, printing I would like to seperate the data out, hopefully without having to input it multiple time and trying to keep track of it.

This might be easy, but I haven't been able to figure it out. And I might not have done a great job explaining what I need to do...sorry.
---------------------------------------------------------------------------------------

Well, I requested canned.meat.eater to share his spreadsheet ... to which I haved added thr following sheets

yogi_monitor1
yogi_monitor2
yogi_DesktopsAndLaptops
yogi_monitor3

yogi_query cell Value by Date according to today date

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Placeb0 said:
I've got a table with three columns: Date (past and future, in several days difference between each value) and Data 1, like this:

Date Data1
01.04.2011 1.25
03.04.2011 2.58
07.04.2011 12.5
12.04.2011 5.6
15.04.2011 11.3


I need to filter/query in a separate sheet the value of Data1 according to current date, or last Data 1 values if the date in Date column is not equal to today date.
for example: if current date is 06.04.2011, i need it to return the Data1 value of 03.04.2011, and if current date becomes 07.04.2011, than to show the Data1 value of 07.04.2011
-----------------------------------------------------------------------------------------------

Well here we go ... in the following I used the FILTER function to extract the desired result in cell A1 of Sheet2. To alleviate the confusion of month or day numbering I used lettered month in the dates.

In Sheet3, I have used the QUERY function to extract the needed value in cell A1