yogi_Expand An Item Count Into Corresponding Number Of Rows

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #563    May 26, 2012     www.energyefficientbuild.com.

user fearless said:
expand an item count into a number of rows
[Using Chrome/OS X, but that's probably not relevant.]

Consider the following on one sheet:

quantity, description, accumulated

0, pony, 0

4, lollipop, 4

2, rainbow, 6

1, fairy, 7

On another sheet, I'd like that to expand to be:

item, description

1, lollipop

2, lollipop

3, lollipop

4, lollipop

5, rainbow

6, rainbow

7, fairy

that is: I get <quantity> rows of each item.  I can do this with a nested IF statement, but there's a nested IF for each item, so it gets unwieldy very quickly.  I'd prefer NOT to resort to scripting.  VLOOKUP holds promise, but I haven't been able to make it jump through this particular hoop.

Any suggestions?

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

following is a solution to the problem ... for clarity I have posted the results in the same sheet ... posting the results in another sheet is not a problem

yogi_Select The First Blank And First D In Any Column Of Given Table And Post Values In Column A Of Corresponding Rows

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #562    May 26, 2012     www.energyefficientbuild.com.

user Chewy3399 said:
Selecting the last data
I have a spreadsheet that a friend and I both use a form to enter data in. One column either has a value of blank, for my data, or a D for his data, what function could I use to retrieve the last entry for each of us, it can be in two separate cells.
----

Is a sample of my data, basically what I want to do is extract the time column for the last entry for D and for "Blank" and put that time stamp in another cell.
--------------------------------------------------------------------------------------------------------------------
following is my proposed solution to the problem

yogi_Compute Average Of A Growing Set of Numbers In A Range Row By Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #561    May 26, 2012     www.energyefficientbuild.com.

user Trillium said:
Infinite average
I am trying to create a total percentage using averages. however i do not want to continue changing the formula to coincide with the number of entries. i want the formula to add all of the entries as they are added. for example lets say i have 5 entries. i have (1+1+0+1+0)/5 to give .60 or 60%. yet if i added a cell it would be (1+1+0+1+0+1)/5 instead of being divided by 6. how do i get the formula to recognize the added cell and account for it in the division of total numbers. also how do i get the formula to ignore blank cells in the total number of cells
------------------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Create A Timestamp Column If The Entries In A Column To Its Right Are Changed

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #560    May 25, 2012     www.energyefficientbuild.com.


user sally Sally said:
Timestamps
How do you create a time stamp column in a new sheet of an existing spreadsheet?  I want it to time stamp each time the column to the right is edited.

Thanks!

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

couple of thoughts ...

1) you can explore using a Google Apps Script for this -- that would be pretty straight forward

2) a formula based approach as presented in the following  at best is a convoluted workaround 

yogi_Sum Up Sheet1_B:D In Sheet2 And Even If A Column Is Inserted In Sheet1 Sheet2 Should Still Show Sum Of Sheet1_B:D

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #559    May 25, 2012     www.energyefficientbuild.com.

user murality123 said:
Sum - issues
using google docs, want to do a sum from other spread sheet, but when i insert a column the values are changing.

Example: 

1] Sheet1 has numbers in coulmn B, C, & D

2] Sheet2 have done sum of Sheet1's B C & D

3] If i Insert a Column in Sheet1 B... total in the sheet2 is showing a sum of column C, D & E.

Would like to know how do i get the values of only colum B C & D's values in the other spread sheet... even if i insert columns in Sheet1 - sum value should change only if the value changes in column B,C, & D

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

following is a solution to the problem

yogi_Autonumber Lines Within Cells Containing Text Row By Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #558    May 24, 2012     www.energyefficientbuild.com.

user barrybaker said:
Is there a way of auto-numbering lines WITHIN a cell? 
Is there a way of auto-numbering, or bullet pointing lines within a cell?

1. Like this
2. and this

Like this:

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

there is no direct way to acomplish what the OP wants ... 

in the following I present a workaround for this

yogi_Count Instances Of An Entity Or A Combination Of Specified Entities In A Column

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #557    May 24, 2012     www.energyefficientbuild.com.

user jmcddonal said:
Looking for Google Doc equivelent formula to COUNTIFS
Can someone help me with this? I'm not really very good with Excel and I need to create a Google spreadsheet that I would normally use a COUNTIFS statement.

Have a spreadsheet has a list of computer laptop hostnames where the first three letters of the hostname determine whether it is a PC assigned to a Faculty member, or non-Faculty admin staff, with a total for each.

Here's a sample of the spreadsheet data I am working from

e1 Hostnames:

e2 LTFAAA

e3 LTDBBB

e4 LTDCCC

e5 LTDDDD

e6 LTDEEE

e7 LTDEEE

e8 LTSFFF

e9 LTSGGG

e10 LTCHHH

Total Faculty PCs (LTF*) - This works OK: =COUNTIF(E2:E10,"LTF*")

Total Admin PCs (LTD* and LTS*) THIS IS THE ONE I CAN'T FIGURE OUT

Total Academic PCs (LTC*) - This works OK: =COUNTIF(E2:E10,"LTF*")

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

following is a solution to the problem