## Monday, July 30, 2012

### yogi_Split A Master List Into Several Parts By Specified Group Of Letters Such As A-F G-M And N-Z

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #664 Jul 30, 2012

*www.energyefficientbuild.com*.

*user PriestessMars said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/la7WxKGBM4Q)*

*Splitting a Master List into three separate lists based on letter of alphabet*

*I have a Master List, filled with 200 or so names. Is there a way to separate that list into three smaller lists by last name, (for example, Sheet 1 would contain last names that beginning with the letters A-F, Sheet 2 G-M, and Sheet 3 N-Z) where the lists would be updated as the master list is updated, without doing it manually?*

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

following is a solution to the problem

### yogi_Extract Range Of Cells Covering Last Occupied Row And Column Of Sheet1 Into Sheet2 So That Other Cells Of Sheet2 Can Be Manually Edited

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #663 Jul 30, 2012

*www.energyefficientbuild.com*.

*user PBD Records said:(*

*http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/usiaRKVatTU)*

*Adding all data from one column to another column on seperate spreadsheet*

*Hi i'm going to try to explain this as best as I can and please excuse my phrasing I am a total spreadsheet noob :)*

I have 2 sheets "Sheet 1" and "Sheet 2". In Sheet 1 column A as shown below I have multiple values under the header "Product #" and also some blank rows. I need an equation that will transfer all the non blank values from sheet 1, column A to Sheet 2 column A. The most important part though is I need this equation to work no matter how many values I have on Sheet 1. I also need the blank rows of sheet 2 left after the transfer to be manually editable (so in this example A7 and below). For example if I wanted to manually add the value Z80 to A7 on sheet 2

I have 2 sheets "Sheet 1" and "Sheet 2". In Sheet 1 column A as shown below I have multiple values under the header "Product #" and also some blank rows. I need an equation that will transfer all the non blank values from sheet 1, column A to Sheet 2 column A. The most important part though is I need this equation to work no matter how many values I have on Sheet 1. I also need the blank rows of sheet 2 left after the transfer to be manually editable (so in this example A7 and below). For example if I wanted to manually add the value Z80 to A7 on sheet 2

**Sheet 1****Sheet 2**__A B________A B__*1| Product # | 1| Product # |*

2| Z50 | 2| |

3| Z51 | 3| |

4| Z52 | 4| |

5| Z53 | 5| |

6| Z54 | 6| |

7| | 7| |

8| | 8| |

9| | 9| |

I left an example spreadsheet that might be easier to visualize than my representation above.

Thanks so much in advance for any and all help!

2| Z50 | 2| |

3| Z51 | 3| |

4| Z52 | 4| |

5| Z53 | 5| |

6| Z54 | 6| |

7| | 7| |

8| | 8| |

9| | 9| |

I left an example spreadsheet that might be easier to visualize than my representation above.

Thanks so much in advance for any and all help!

### yogi_Rearrange Data By Entities In Column B C D And Set Of Specified Number of Columns

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #662 Jul 30, 2012

*www.energyefficientbuild.com*.

*user aleksialkio said: (http://productforums.google.com/forum/#!searchin/docs/aleksi/docs/n4xP-jmDtXQ/KBmKsp4Y5XkJ)*

*Using query to reorganize the player details*

*Hi,*

*I'm trying to organize the spreadsheet to a new way. I'm wondering is the query function able to do this and I have tried to think it with many ways but I do need some help. Perhaps I'm thinking too difficult :-)*

*I have a form that collect the information from one team to one row. I need better way to show them and I will make individual sheets for every age group to publish. I made an example sheet what I need to do.*

*https://docs.google.com/*spreadsheet/pub?key=0AkMVQyM7UcFddGg0SG56LTFlRVNZZTVIZGNzUHFJLVE&output=html

*Thanks, Aleksi*

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

following is a solution to the problem

## Sunday, July 29, 2012

### yogi_Arrange Multiconditional Sum By Month Customer And Product Size As Delineated By User

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #661 Jul 29, 2012

*www.energyefficientbuild.com*.

*user urbis said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/rMjbFiShFoA)*

*How to transfer data*

*I ask for help, how to make the table ENTER to transfer data into a table TABLE.*

Signs named (Steve, Marc, Bill) by format (10500x795, 1030x790, 525x459) I transferred to the month in which the table is in the months to ENTER

https://docs.google.com/spreadsheet/ccc?key=0Anv13BZwny-VdER6QU1OOFdrZ1NWVjFuLThGMWM0Nmc

Signs named (Steve, Marc, Bill) by format (10500x795, 1030x790, 525x459) I transferred to the month in which the table is in the months to ENTER

https://docs.google.com/

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

following is a solution to the problem

### yogi_Compute Current Balance Row By Row Where A String Is Part of Entries Of Name Column In Another Sheet

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #661 Jul 29, 2012

*www.energyefficientbuild.com*.

*user MysticEve said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/IcxyKBKF_Ds)*

*How to use VLOOKUP where the search criterion is only part of the text string*

*Goal: look up user's name and return their point balance.*

*Problem: some users have multiple user names listed within the same cell.*

*So far I know that using =VLOOKUP(A1;Sheet1!$A$1:$D$11;*2;false) works for Single usernames. However I have cells that has multiple usernames for the same person recorded within the same cell. How do i modify VLOOKUP to search within the cell?

*Spreadsheet can be found here:*

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

following is a solution to the problem

## Saturday, July 28, 2012

### yogi_Set Up A Leaderboard That Stays Current As Scoreds Are Updated

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #660 Jul 28, 2012

*www.energyefficientbuild.com*.

*user Yoshiman03 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/oL--36yzuB0)*

*Vlookup with sorting?*

*Basically I have a table, the table contains a list of names, their scores for 6 weeks (1 week per column) and column for their total score. What I want to do, is have seperately but on the same sheet is a leader board, that will sort and list the players based on their scores.*

*Here's the table thus far, each of the scores in this sheet actually are a lookup from another sheet within the spreadsheet so its not just the value*

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

following is a solution to the problem

I have laid it out in such a way as to allow adding more weekly data to the right of column L, and more names at the bottom of column F

I have laid it out in such a way as to allow adding more weekly data to the right of column L, and more names at the bottom of column F

## Friday, July 27, 2012

### yogi_Count Instances Of Strings In A Column And Compute Net Difference By Item

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #659 Jul 27, 2012

*www.energyefficientbuild.com*.

*user Jabus said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/9naciXcSJcY)*

*Spreadsheet - How to Compare Two String Columns and Add New Rows From Forms?*

*Hi there,*

*I'm creating a bit of an awkward spreadsheet, it's going to end up having 2 columns, 1 with the name of the product and the second with the words add, remove or disregard. An example of the spreadsheet:*

*A B*

*Printer || Add 1*

Printer || Add 1

Printer || Add 1

*Printer || Remove 1*

*Computer || Add 1*

*Computer || Add 1*

*Computer || Add 1*

*Computer || Remove 1*

*I want to get a formula that will compare grab the name of the product in column A and then count how many "Add 1"'s there are and subtract how many "Remove 1"'s. So the end result should show a total tally of Printers = 1 and Computers = 2*

*Note the following may hurt to read: I feel like I'm going about this a really long way around but I don't recall seeing a function, or perhaps my brain just is over complicating things. Because what I ended up doing is creating 4 new columns the first being an IF statement that generates a 1 if column B says "Add 1" and a 5 (random number) if false. Then the same thing for the second column (colmn D now) where I do an IF statement to see if the item is a Printer or not giving 1 or 0 as the true or false. Then my third column does an IF to check it column C is a 1 and column D is a 1 then that amount of add printers is 1 (which i then sum) then same thing for removing printers but I use the the 5 in the column C to add to the 1 from column D (confirming its a printer) and so that if 5+1 = 6 then it's a 1 for the remove column and then sum that up then the the sum of column e is subtracted from d to get the right answers.*

*But that's a horrible solution, anyone have an idea on how I can compare the two columns? and then have new formulas form as people respond to a form? I feel like I'm doing such a poor job of this.*

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

let us have a look at the following solution to the problem

### yogi_Keep A Running Count Of Monitored Calls For A Specified Agent By Month

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #658 Jul 27, 2012

*www.energyefficientbuild.com*.

*user AnswerFirst said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dkRx3tKpfww)*

*How to calculate the number of items in one column dependent on values in an another column (similar to =arrayformula)*

*This is way too hard to explain without just sharing the sheet:*

https://docs.google.com/spreadsheet/ccc?key=0AliYViHYAwaNdGRBb1JxOHo5REJlMHl6OU5Ld3VId2c I need to auto-populate column B with the cumulative number of calls that an agent (column F) has made in the month (column A) ... I found this formula that seems to work for any given row (i.e. this would work for row 2):

=arrayformula(sum((A:A=6)*(F:F="ACM - Alaina McGinnis")))

but I can't figure out how to make this work for all rows without manually rewriting the formula in each row.

It doesn't matter if this is solved with a formula or a script but I need the value to be calculated each time the form is submitted. Thoughts, suggestions? Thank you!

https://docs.google.com/

=arrayformula(sum((A:A=6)*(F:

but I can't figure out how to make this work for all rows without manually rewriting the formula in each row.

It doesn't matter if this is solved with a formula or a script but I need the value to be calculated each time the form is submitted. Thoughts, suggestions? Thank you!

*----*

*Thanks yogia,*

To answer your questions:

1. The data is realistic; this is the exact sheet and form we use in production.

2 & 3. The calculation that I'm struggling with is on Sheet1: in column B I would like to calculate the total number of monitored calls that an agent (column F) has had for any given month (column A) So, if Jane Doe (column F) was monitored on 3 calls in the month of July (represented by a 7 in column A) then the value in column B would be 3. I've manually filled in the values for B2:B10 to show you what I would expect to see there. Cell B11 has been left blank

4. I'm not sure a separate summary table would work for this as I need the value in column B to populated automatically as rows are added to this sheet. This spreadsheet is populated based on the results of a form (which is visible to the public in case you want to glance at it.)

Thank you for your help and time! :)

To answer your questions:

1. The data is realistic; this is the exact sheet and form we use in production.

2 & 3. The calculation that I'm struggling with is on Sheet1: in column B I would like to calculate the total number of monitored calls that an agent (column F) has had for any given month (column A) So, if Jane Doe (column F) was monitored on 3 calls in the month of July (represented by a 7 in column A) then the value in column B would be 3. I've manually filled in the values for B2:B10 to show you what I would expect to see there. Cell B11 has been left blank

4. I'm not sure a separate summary table would work for this as I need the value in column B to populated automatically as rows are added to this sheet. This spreadsheet is populated based on the results of a form (which is visible to the public in case you want to glance at it.)

Thank you for your help and time! :)

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

following is a solution to the problem

### yogi_Compute Stats For Specified Entities For Data In A Table For Variable Sheet Names In Another Sheet

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #657 Jul 27, 2012

*www.energyefficientbuild.com*.

*user claire graham said:(*http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/HG_OXeI_tAM)

*How do you find an average using data from 2 separate sheets?*

*Hi,*

*I am working on a Spreadsheet (populated by a form).*

*I need to generate a sheet (named Analysis) that shows the average age of the users, this information is currently on 2 other separate sheets(named NAM and NPM).*

*The age information is shown in column I on both sheets.*

*I entered the following*

*=AVERAGE('NAM'!I3:I20000;'NPM'*!I3:I20000)

*which appears to be fine when there is no data, however, when data is entered the following error appears*

*error: Circular dependency detected.*

*I also need to show average ages for particular groups, (average male age, female age).*

*I have been successful in showing averages from data on just one sheet but combining 2 is causing me a headache.*

*All help would be appreciated.*

*with thanks Claire*

*----*

*Hi Yogia,*

*Thanks for getting back to me,*

*Here is the link to the Spreadsheet.*

*I am concerned with the Sheet named 'Nursery Analysis'. This needs to reference several other sheets.*

*Initially I am concerned with Cell B9 on Nursery Analysis.*

*I would like to find the average age (in months) of all the boys who have completed a Baseline.*

*This information is found on sheets - BaselineNAM13/14 and BaselineNPM13/14 in columns G (male) columns I (age in months).*

*Currently there should be 8 male Baseline entries and the average age across both sheets should be 38.5.*

*This showing cohort information across 2 classes of nursery children.*

*I hope I have included all the information you require.*

following is a solution to the problem

## Tuesday, July 24, 2012

### yogi_Count Submissions By Every Month Of The Year Assigning Value of 0 For Month With No Submissions

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #656 Jul 25, 2012

*www.energyefficientbuild.com*.

*this is in response to a question by use Nieboy1 in http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/4cRXTQh4ml8*

### yogi_For Entries In Range B2:M7 Show Duplicated Entries In B12:M17

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #655 Jul 24, 2012

*www.energyefficientbuild.com*.

*user TimeyMaster said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ROCiXzPNKs0)*

*Looking for and Displaying Duplacuats*

*Target List Target1 Target2 Target3 Target4 Target5 Target6 Target7 Target8 Target9 Target10 Target11 Target12*

*Primary Target 372:562 370:561 372:574 380:558 380:562 385:575 366:561 375:557 380:576 381:576 390:560 383:567*

*Decoy 1 Target 378:562 389:571 390:560 390:555 392:561 386:572 386:549 380:568 371:548 371:549 381:548 381:544*

*Decoy 2 Target 382:571 373:564 386:553 385:572 385:574 388:570 358:546 357:546 381:558 377:559 379:561 377:564*

*Decoy 3 Target 386:549 383:574 381:575 381:576 388:538 380:578 376:565 371:567 372:567 376:568 384:568 384:567*

*Decoy 4 Target 380:579 379:579 378:571 378:559 374:564 380:565 388:569 388:572 387:572 387:573 386:573 386:574*

*Decoy 5 Target 374:571 367:573 373:565 388:588 375:571 376:569 384:574 376:575 384:575 385:579*

*379:575 379:574*

*the two cords marked in red are duplicate i was hoping to have these highlighted or displayed in some way so if i type two in like this it is easy seen so i don't make the mistake and hand out two of the same cords in a battle is this enouth info or do you need more?*

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

following is a solution to the problem

### yogi_Set Up Computed Columns For Row By Row Computations In A Form And Also A Grand Total

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #654 Jul 24, 2012

*www.energyefficientbuild.com*.

*user Amber0803 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KEKsqI3M9kY)*

*Last row does not populate calculations after form submission, countif formula*

*Hi All,*

I know this has been addressed before but I can't seem to find an answer that fits my needs, that I can understand anyway.

I have a form that basically calculates commission on certain items, they are set up as checkboxes so all of the items that are checked

go into the same cell. To calculate how much each is worth I have a column for each item and I use a COUNTIF formula to search that one cell (the E column) for

specific text. My problem is that for each form submission, the formulas don't copy down. I know I need to use an array formula but I can't get it to

work. I've also read that I can't use a COUNTIF formula in an array and that's where I get completely lost...

I have formulas in columns G:M that I need to repeat

These are a few examples of the formulas I am currently using:

=IF(COUNTIF(E2,"*Super*")>0,COUNTIF(E2,"*Super*")*4,"")

=IF(COUNTIF(E2,"*16*")>0,COUNTIF(E2,"*16*")*2,"")

=IF(COUNTIF(E2,"*Pineapple*")>0,COUNTIF(E2,"*Pineapple*")*1,""

Hoping someone out there can help me, I've been banging my head against my desk for hours...

I know this has been addressed before but I can't seem to find an answer that fits my needs, that I can understand anyway.

I have a form that basically calculates commission on certain items, they are set up as checkboxes so all of the items that are checked

go into the same cell. To calculate how much each is worth I have a column for each item and I use a COUNTIF formula to search that one cell (the E column) for

specific text. My problem is that for each form submission, the formulas don't copy down. I know I need to use an array formula but I can't get it to

work. I've also read that I can't use a COUNTIF formula in an array and that's where I get completely lost...

I have formulas in columns G:M that I need to repeat

These are a few examples of the formulas I am currently using:

=IF(COUNTIF(E2,"*Super*")>0,

=IF(COUNTIF(E2,"*16*")>0,

=IF(COUNTIF(E2,"*Pineapple*")>

Hoping someone out there can help me, I've been banging my head against my desk for hours...

*----*

*Hi Yogi,*

Here's a sample sheet/form:

https://docs.google.com/spreadsheet/ccc?key=0AkzKlHQOFyfDdDZIUWJXV3NYR05iVzhvM3M3NW45S1E

The form populates columns A-F, then the rest of the columns are calculations.

The formulas for the calculations are correct in the first two rows (which had to be manually put in after a form submission) but when a new submission is entered, like the last row,

the formulas do not appear. Each calculation column searches column E for a certain criteria then multiplies it by the correct number. At the end, the bonus column adds 1 if columns

H and I are on a single order. And then the total adds the entire row.

Thanks in advance for your help!

Here's a sample sheet/form:

https://docs.google.com/

The form populates columns A-F, then the rest of the columns are calculations.

The formulas for the calculations are correct in the first two rows (which had to be manually put in after a form submission) but when a new submission is entered, like the last row,

the formulas do not appear. Each calculation column searches column E for a certain criteria then multiplies it by the correct number. At the end, the bonus column adds 1 if columns

H and I are on a single order. And then the total adds the entire row.

Thanks in advance for your help!

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

following is a solution to the problem

### yogi_Generate A List Of Numbers Given Starting And Ending Sequence As Specific Numbers Or Via Range Names

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #653 Jul 24, 2012

*www.energyefficientbuild.com*.

*user Chrisnetika said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Mji-S-FK89I)*

*Creating Array To list a range of numbers*

*Im trying to list specied range as an array.*

Basically I have 2 cells for example A1 and A2, A1 countains number 23 and A2 contains 44

I want to build an array so that in a column each row has number from that range.. for example

A

1 23

2 44

3

4 =Arrayformula(A1:A2)

5 23

6 24

7 25

8 26

9 ... and so on till 44

Basically I have 2 cells for example A1 and A2, A1 countains number 23 and A2 contains 44

I want to build an array so that in a column each row has number from that range.. for example

A

1 23

2 44

3

4 =Arrayformula(A1:A2)

5 23

6 24

7 25

8 26

9 ... and so on till 44

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

following is a solution to the problem

### yogi_Set Up A Spreadsheet For Row By Row MultiConditional Output

*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #652 Jul 24, 2012

*www.energyefficientbuild.com*.

*user ularnaga said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/XaTChzo5SKs)*

*Multiple outcome if cell varies*

*Hello!*

*I have a calculation and i want to have multiple outcome.*

*So if the cell P3 has a value of "1 to 49" or "50 to 299" or "300 to 499" or "500 to 9999" then it will use one of the 4 different calculations below.*

*P3*PrintSmall49less*

*P3*PrintSmall50to299*

P3*PrintSmall300to499

P3*PrintSmall500to9999

P3*PrintSmall300to499

P3*PrintSmall500to9999

*I know how to use "IF" but then i can only use two choices, and not 4 like i want to.*

*=IF(P3>49;P3* PrintSmall50to299;P3**PrintSmall49less)

*Thanks!*

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

following is a solution to the problem ... i have generalized the problem a bit and I have also included an array based formula

Subscribe to:
Posts (Atom)