yogi_InsertHH:MM:SSforCurrentTimeAtTimesAndHold

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

glrcbs said ...

I need a shortcut to insert the current time of day in hours, minutes, and seconds, into a spreadsheet cell, preferably in HH:MM:SS format and need to be able to do that multiple times in a spreadsheet

I've tried  =NOW()

which correctly inserts the current time (and date) but whenever I add a new time, all those cells are updated to the newest time

I've tried ctl+:  

but it puts zeroes where the seconds should be

And I've tried     

=mid(index(ImportHtml("http://www.time.gov/timezone.cgi?Eastern/d/-5";"table";3);2;1);2;9)

which works the first time I use it in a spreadsheet. Every time I use it after that, it enters the same time as the first one.

Any suggestion?

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

As you have already noticed Ctrl+: or Ctrl-Shift-; as Gill suggested embed the current time in hh:mm:00 (with seconds as 0)

I have the following workaround for you using Data Validation from a List ... 

the List consists of one cell with the following formula ...

=mod(now(),1)

Check it out and see if this would work for you!

yogi_GroupOfCellsToChangeColorWhenAnotherCellHasX

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

LADTHOMPSON said ...

I am trying to format a group of cells to change when another cell has an "X" input to it. 
How can I do this in google?

This works in excel through conditional formatting.  However, google does not have conditional formatting or I am not doing something right.  For example.  Cells "A2" through "G2" (all cells in the same row) have different text entered ranging from names to numbers.  What I want to have happen is when cell "H2" has an "X" input, I want those previous cells to either have the information formatted to "Strikethrough" or become a lighter color of text.

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

The following workaround using Change color with rules in an adjacent row, say row 3 
might be of some interest to you. So here we go

yogi_Count Comma Separated Values From Cell With Multiple Conditions

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

Jogi said ...

How can i count comma separated values from a cell with multiple conditions?

My sheet looks like this:

Keywords                    Categories
aeroplane, plane, car       category 1
car, bicycle, plane         category 2
plane                       category 2

Desired output in second sheet:

Keywords    Category 1    Category 2    Sum
car             1             1          2
bicycle         0             1          1
aeroplane       1             0          1
plane           1             2          3

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

In my proposed solution, I first computed the unique items and then I computed the number of times the item appears in each category ... so here we go

Jogi commented:


These (formulas in Sheet1) are quite sophisticated formulas :) Frankly, I was looking for a simpler solution, and not so automatic. I don't want to count every single keyword, just some chosen few. So, let's say this is my desired output:


    A        B          C       D
Keywords Category_1 Category_2 Sum
car          1          1       2
bicycle      0          1       1
aeroplane    1          0       1
plane        1          2       3


I am  looking for a formula I can use for each cell separately. For example, in B2: count how many times "car" can be found in column A of Sheet 1 where the side cell from column B = "category 1".
------------------------------------------------------------------


In response to comment from Jogi, I have added Sheet2 in this post ... where formula in cell B2 is ...

=ArrayFormula(sumproduct(iferror(sign(search($A2,'Sheet1'!$A$2:$A))),
('Sheet1'!$B$2:$B=B$1)))

yogi_CountResponsesBetweenCertainRangeOfNumbers

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

DanM77 said ...
I have a column on a spreadsheet which shows the responses from a form. The responses are a number between 1 & 99.
I want to show a graph or the responses but in order to do so, I think I really need to group the responses into ranges (probably seperating it into 10 ranges of around 10 numbers). I was thinking a pie chart might be best but the problem that I'm having really is getting a count of numbers between ranges.
----------------------------------------------------------------------
Well, I have setup ranges of upto 10 numbers from 1 to 10, 11 to 20, 21 to 30, 31 to 40, and so on as far as we want to go within the limit of number of columns in the spreadsheet. So here we go ...

yogi_Form CheckBox Questions Split Into Own Columns

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

emitsignal said ...

I am collecting registration details from schools participating in my charitable programme so I can match them. They're asked to select as many options as apply to them, but the results end up aggregated into single cells. Although these are comma delimited so I can split them in Excel, they end up spread across different columns which makes it impossible to sort meaningfully so I can match the schools properly. 

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

I have assumed there are 5 CheckBoxes for each question

I have used the following layout from Andre (ahab)'s fine solution posted to emitsignal's question in Google Docs Help Forum. I had a little play with it and I present my following solution:

-----------------------------------------------------------------------------
update May-23-2011
I have added a scoring scheme as shown in sheet named TestScoresForYandNresponses
whrere both Y and N responses are assigned a score as shown in cells C4 and C6
and Test Scores for submittals are shown in column I

yogi_FormForWeekDayMealsLunchOrDinner

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

Please view the spreadsheet above for open slots ... and then if you are interested in hosting Lunch or Dinner for a given day or days, fill in the FORM below



and SUBMIT. Your Form submittal will update the spreadsheet as presented in this post (you will have to refresh /reload the page on the browser to see the update in the spreadsheet.

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

The following is a graphic representation of which slots for hosting Lunch, or Dinner for Linda have already been taken up ...

yogi_PlaceFormDataInSpecificLocation

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

mjbutterfield said ...
I am trying to set up a way for subcontractors to submit bids via a form and have them populate an estimate.  Here are the couple challenges I am trying to overcome.

Here is a simplified version:

Let's say I have 3 categories- foundation, framing, and sheetrock.  I need to look at the form responses and determine which category the response is supposed to be in.  I then need to place that amount in the spreadsheet for that category.

The second issue is that I would often like to have more than one bid in each category so I need to somehow have the data be placed in the next cell to the right if there is already another bid populated for that category.  I understand a lot of the functions within spreadsheets but could use some advice on the smartest way to accomplish this.  Below is a simplified example of the end product I am looking for:

Category         Vendor 1   Bid 1    Vendor 2  Bid 2     Vendor 3   Bid 3

_______________________________________________________________

Foundation      Joe         1000        Mike        1200

Framing           Jim         2000

Sheetrock        Tim         1000        Bill           900       Frank       1100
--------------------------------------------------------------------------------------------
In Sheet1, I have proposed a solution using the QUERY function ... the resulting layout in Sheet1 does not quite match the layout as requied by mjbutterfield. If the resulting layout in Sheet1 would do, we are all done.

However, to meet mjbutterfield's requirement, I present a solution in Sheet2 where I build up on the work done in Sheet1
Sheet3 however has a more straightforward solution using primarily the FILTER function along with the CONCATENATE and the SPLIT functiuons.
In Sheet4, I provide another QUERY formula based solution which is like the TRANSPOSE of the solution in Sheet1.