yogi_Count The Number Of Occurrences Of An Entity And Present The Results As Specified

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #576    Jun 03, 2012     www.energyefficientbuild.com.


user studenikin.Ivan said:
Report based on a single column
Hi everyone.

I have the following spreadsheet:

Item # |   Cars

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

  1        BMW

  2        Porsche

  3        Mercedes

  4        BMW

  5        Porsche

  6        BMW

How do I build a report where X axis is name of Car, and Y axis is number of times that corresponding car appears in the Cars column.

So in the example above desired Data Table (which the report is based on) should look as follows:

Car Name | Occurrence

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

Mercedes      1

Porsche       2

BMW           3

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

I suggested to studenikin.Ivan to use the following QUERY formula:

=query(A:B,"select B,count(A) where A is not null group by B") 

----

studenikin.Ivan responded:

Hi yogia,

Thanks! You solution works perfect for me except one thing. First row of the table looks weird:

Car         count Item#

                       count

BMW        3

Mercedes        1

Porsche        2

----

Well I used the label option in the QUERY function for the headers, and I ordered the result in descending order by occurrences -- so the report is almost identical to what the OP wanted

yogi_Chart Data For A Dynamically Increasing Or Decreaing Range Corresponding To Non-Blank Entries In Column B

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #575    Jun 03, 2012     www.energyefficientbuild.com.

user ffar said:
Lets say i have 

1 3.2

2 4.2

and I plot it using google line chart .

then I add 

3 5.4

4 6.5

It seems like I have to go back and change the range of the plot! 

This is too complicated because I have too many plots

--> Is there anyway I can automate this chart update? 

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

I told ffar ... YES!

Let us say your data is in columns B:C, the you can specify the range to be charted as B:C

chart will change it to something like B1:C100 if your sheet has 100 rows

in this case your chart would be dynamic ... it will grow/ shrink as you add or delete data in columns B and C

I hope this helps.

----

ffar came back with the following question:

Thank you for the answer. It works fine. The only problem is that I have this extended x-axis which has no data... can I resize it so the x-axis ends with my last existing data point? 

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

I told ffar ... YES -- with a workaround as presented in the following solution

yogi_Search A Multi Column Multi Row Range For A Specified Value And Return The Value In Column A For The Corresponding Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #574    Jun 03, 2012     www.energyefficientbuild.com.

user gras said:
Search multi columns and rows, return row far left value
I am just learning this. I would like to perform a search within a named range of columns and rows (Aug1stWk) find a unique value (WR) and return that row's 1st value (A person's name). I am looking at DGET but I am not quite sure if this will work, definitely not sure how to make it work.
------------------------------------------------------------------------------------------------

in the following I present a solution although I did not use the DGET function

yogi_Join Non-Zero Entries In A Given Set Of Non-Contiguous Columns And Transpose To Rows For Weekly Meal Plan By Day of The Week

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #573    Jun 02, 2012     www.energyefficientbuild.com.

the solution I have provided here is in reference to user TomSleeUK's question addressed in my earlier blog post http://yogi--anand-consulting.blogspot.com/2012/05/yogijoin-non-blank-entries-in-given.html
the meal data for the days of the week is laid out in range U17:AY35
this data is then summarized into 7 cells -- 1 day of the week per row.

yogi_Find 2nd 3rd 4th 5th 6th 7th 8th 9th And 10th Mode Of Values In A Range Covering Cells A1:E50

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #572    Jun 02, 2012     www.energyefficientbuild.com.

user klee025 said:
How to find the 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th, most common numbers in Multiple Spreadsheets 
Hey Guys,

got a question for you that I am trying to figure out.

I have 5 spread sheets. Lets just call them Spreadsheet A, B, C, D, E

In each spreadsheet, there are 6 columns of numbers, which I will select as an entire range.

But for arguments sake, lets just say A1:A50, B1:B50, C1:C50, D1:D50, E1:E50

I know how to get the most common number by using MODE.

Im trying to figure out a formula with MODE that will give me the 2nd most common number referencing Spreadsheet A, B, C, D, E with A1:A50, B1:B50, C1:C50, D1:D50, E1:E50

Then using the same model, get the the 3rd most frequent number

4th most frequent number

5th, 6th, 7th, 8th, 9th, all the way to the 10th most frequent number.

I would be so grateful to anyone that can clearly give me a copy-paste function that I can just incorporate right away.

Thank You!!

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

As of this time (Jun-2012) the MODE function in Google spreadsheet does not provide correct result ... so I have used a workaround to determine the values for different modes

yogi_Create A Column Of Random Values With ARRAYFORMULA

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #571    Jun 01, 2012     www.energyefficientbuild.com.

user mjs said:(http://productforums.google.com/forum/?zx=kljxr09cvfue#!searchin/docs/Creating$20a$20column$20of$20random$20values$20with$20ARRAYFORMULA()?/docs/IJjOn547twU/svf5wEZOD8YJ)
Creating a column of random values with ARRAYFORMULA()?
How can I create an infinitely-extending column of random values?  (i.e. so that as I add more rows, I also get more random numbers.)
I tried ARRAYFORMULA((ROW(A:A)*0) + RAND())
however this only seems to "evaluate" the RAND() once: I get an infinitely-extending column, but the value is exactly the same every time.  (If I recalculate the spreadsheet, I get a different random value.)
Michael

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

in the following I present some work around solutions to the problem

yogi_Compute Balance Row By Row From Multiple Credit And Balance Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #570    Jun 01, 2012     www.energyefficientbuild.com.

user Ijatinput said:
Help with a simple (but long) formula, please! 
Hi

I'm doing accounts with a spreadsheet.  Column B is credit, and colums C - Q are debits.  Please can someone tell me a simple formula to put in column T for the running balance, rather than typing it all out as in =SUM(T4+B5-C5-D5-E5 etc etc)

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

following is a solution to the problem