yogi_Compute Total And Line By Line Amount Owed By Cory For Expenses Covers By Dennis

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #788  Sep 30, 2012    www.energyefficientbuild.com.

user Dennybot said: (http://productforums.google.com/forum/#!mydiscussions/docs/_D8psjlNF94)
Is this the right place to ask for help with a simple formula?
Folks, I could really use some help with a simple doc I'm setting up to track expenses and payments between two roommates.
It is public, and located here:
https://docs.google.com/spreadsheet/ccc?key=0AmUd0B_TZtSHdEZlRnJtaVR4LVpHaXlBR2Nub19tbFE 
As you can see, I simply need one column to track one person's expenses, and another to track the other's payments, and an area that does the math. But you can see that it's not working as I need it to, because the balance for 'Cory Owes' should be zero.
Would someone be able to help me out with this, OR point me toward a good forum to take this type of thing to?
Greatly appreciated!!!
-----------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Lookup Monthly Number For An ID From A Table Of Multiple Sets Of IDs And Monthly Numbers

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #787  Sep 30, 2012    www.energyefficientbuild.com.

user beejizzle said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dYMOKdpPGVE)

Can I use vlookup to search in more columns than the left only?

I use =vlookup(A1; A2:I10;3; 0) right now. The table A2-J10 look liks this:

A1 = ID_to_search_for

ID | Jan | Price | ID | Feb | Price | ID | Mar | Price

10 | 11 | 1000 | 15 | 14 | 1400 | 9 | 8 | 100

99 | 11 | 90 | 77 | 14 | 200 | 21 | 8 | 400

44 | 11 | 900 | 12 | 14 | 1400 | 64 | 8 | 220

Vlookup can only search in the first, left, column. If the A1 is 9 I want to match with G2 and have the value from I2 Is there any formula or if statement that can make it search in all 3 ID fields?

Thanks

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

following is a convoluted solution using the VLOOKUP function 

yogi_Tabulate StudentName And Student's Best Score From A List Of Student Names And Scores In Another Sheet

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #786  Sep 29, 2012    www.energyefficientbuild.com.

user brianteacher said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/_YYNqLU2Cy4) 
Sort, filter and add using unique values in Google spreadheet
 ....
https://docs.google.com/spreadsheet/ccc?key=0AhfL5EbuOgCAdDlUMV9iandvRzdDQmFuQmVZZG0tanc

I would like the second tab (BestScores) to reflect each student's best effort from tab one (AllScores)

-------------------------------------------------------------------------------------------
following is a solution to the problem
 

yogi_Compute Frequency Of Grades By Possible Grade List

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #785  Sep 29, 2012    www.energyefficientbuild.com.

user brianteacher said: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/u9N4V0H8uFo)
Grade distribution

I found an old thread with a great spreadsheet example by ahab that shows a formula for counting the distribution of letter grades.

https://groups.google.com/d/topic/How-to-Documents/vvhEWGD2UCM/discussion

I was wondering if there would be a way to adapt such a formula to return "0" if there are no (for example) "D" grades.

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

following is a solution to the problem

yogi_Workaround For Inserting Indian Rupee Symbol In A Google Spreadsheet

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #784  Sep 28, 2012    www.energyefficientbuild.com.

user ketan joshi1966 said: (http://productforums.google.com/forum/?zx=d088tq41gvii#!category-topic/docs/spreadsheets/cnRk6xunnL4)
How To Add Indian Rupee Symbol
How To Add Indian Rupee Symbol In Google Drive Spreadsheets
-----------------------------------------------------------------------------------
until we have a font with Indian Rupee symbol or the unicode approved for the Indian Rupee symbol

I suggest we use the following convoluted workaround as presented herein

yogi_Make A Computed Column To Have The Dates In A Column Presented In Users Specified Style (yyyy-mm-dd)

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #783  Sep 28, 2012    www.energyefficientbuild.com.

user unicom said: (http://productforums.google.com/forum/?zx=d088tq41gvii#!category-topic/docs/spreadsheets/1Or85dznYHA
auto correcting a date format
Could anyone point me toward a help page or help me set up some kind of formula where by when customers insert a date into a form it can be automatically changed to produce one date format only.  Some choose for example dd.mm.yy or dd.m.yy or dd.mm.yyyy I have clear instructions as to how dates should be entered but invariably they are ignored and the various forms of date entry are accepted by the form and I end up manually correcting them.  I would like to either restrict entry to dd\mm\yyyy or create some kind of formula that automatically corrects it.  

Any suggestions would be welcome.
---------------------------------------------------------------------------------------------
I have assumed here that you had the column receiving the dates formatted as plain Text and the entries in the Form are acceptable date entries -- if the Form entries are not acceptable dates, then the problem would be a little more complex.

yogi_Sort Data Of A Specified Column Only Given A Table (Using Sort Command And Alternately Sort Function)

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #782  Sep 28, 2012    www.energyefficientbuild.com.

user GiveDark said: (http://productforums.google.com/forum/?zx=d088tq41gvii#!category-topic/docs/spreadsheets/5-A4_I3thYI
Script to sort certain columns in spreadsheet 
I want to write a script that will sort part of a spreadsheet by one column, but keep the rest of the spreadsheet unsorted.  The column that I am sorting by (say column B) is always the same.  I a couple other columns to follow the sorting of column B (so that the rows stay together).  So far this is the equivalent of -> Data -> Sort Sheet by Column B.  However, I do not want the rest of the columns to be sorted.  They should stay exactly the same.

Does anyone have any hints on how to write a script for this?  Even tips on doing part of this would be helpful, such as how to select only certain columns.

Thanks
-----------------------------------------------------------------------------------------------
see the following illustration

yogi_Compute Number Of Sales By Salesmen On A Specified Date In Another Sheet

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #781  Sep 28, 2012    www.energyefficientbuild.com.

user Brian Podolak said: (http://productforums.google.com/forum/?zx=357yj7i8jxeu#!category-topic/docs/spreadsheets/N35AHazUJ6k)
COUNTIF or SUM? Im so confused.
Basically I have a workbook with 2 sheets

SHEET1 has data in it like this

A                                                     B
9/7/2012 13:14:00                          Brian
9/10/2012 10:19:00                        Bob

On Worksheet SHEET2
I want to total the occurrences of Brian and Bob based on a date I have in C7
So this way I can see

Brian  1
Bob     1

I use this sheet to tracks sales by my sales reps.
=ArrayFormula(countif(INT(SHEET1!A:A),S4))  
gets me the sales for the day (s4 is todays date)
I try to complete it like this
=ArrayFormula(sum((INT(SHEET1!A:A),S4))*(countif(SHEET1!F:F,J10)))
Where F is the reps name
and crash and burn.
No hurry, but I am on a step ladder with a towel around my neck. I am sure will be something simple.
Thanks in advance gang!
------------------------------------------------------------------------------
following is a solution to the problem as best I understand your question

yogi_List NewMembers And Quitters From Membership Roster For Years 2011 And 2012

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #780  Sep 27, 2012    www.energyefficientbuild.com.

user ThNic said: (http://productforums.google.com/forum/?zx=cybaz6xii5cb#!mydiscussions/docs/rYWjd0y-M6I)

Beginner needs help on Filter / Query

Hi there,

I am a beginner of spreadsheet programming, although somewhat apt at Excel.

This is my question: I want to compare happenings in different years from the same table. Specifically: who did pay membership fees last year but not this year.

My setup is with columns that all have named ranges;

Name (select from in-cell drop-down validation list in another sheet);

Bankaccount number (vlookup from another sheet);

Date (to be entered manually);

Amount (to be entered manually);

Transaction (select from in-cell drop-down validation list in another sheet);

Reference (entered manually)

Remarks (entered manually)

Entries are either downloaded from the bankreport, shuffled around and pasted into the spreadsheet, or entered if there are few transactions.

So: for several people I have their yearly membership fee entered in the same sheet, but with a different date.

I now want to filter out those who were paying members last year, but have not renewed their membership this year. And I want just 1 list of names.

I have tried something like =filter('Fin.Transacties'!B:F; ('Fin.Transacties'!F:F="2012")+('Fin.Transacties'!F:F<>"2011")) , but that does not work, because it is the wrong syntax for what I want: it returns everything except 2011.

Also: it does not give me 1 list of names, but each entry for each year.

Who can help?

----

.... this is the link: https://docs.google.com/spreadsheet/ccc?key=0AlvMPBJLI6OCdHJPM0RuNGxVV1VaSVVUcGpLMFhqX1E

----

....I added two colums (T & U) with notes in the sheet. In fact I am after the delta of the two lists that you have generated, and represent that delta in one list:

• list 1 = names that are on the 2012 list, but not on the 2011 list. Those are the people that are new members.
• list 2 = names that are on the 2011 list, but not on the 2012 list. Those are the "quitters". 

I hope my question is clear now. If not, let me know.

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

following is a solution to the problem for listing Newmembers and Quitters

yogi_Count Unique Products In A List And Present Results Sorted By Count In Descending Order In Another Sheet

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

user Hoopsnl said: (http://productforums.google.com/forum/?zx=63d4ber84sra#!category-topic/docs/spreadsheets/DCAr1YxtwjQ
Count unique, sort and filter
Hi,
Hopefully someone can help me with this.
On the tab "LIST_1" in row A4:A I have put all kind of products, now I want to do some statistics in tab "STATS".
I would like to know (COUNT) how many times a product name appears in A4:A and the product counted most should appear on top;
[tab "LIST_1"]
Column A
Product X
Product X
Product X
Product X
Product A
Product A
Product B
The outcome should be:
[tab "STATS"]
Column A        Column B
Product X          4                    
Product A         2
Product B         1
Hopefully someone can help me with this..
Thx!
---------------------------------------------------------------------------------------
following is a solution to the broblem

yogi_Tally Whether Congressmen Vote For A Particular Bill And Turn their Votes Into 1s And 0s

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

user Greg Feresntein said: (http://productforums.google.com/forum/?zx=63d4ber84sra#!category-topic/docs/spreadsheets/ugkepH--cGs
Data Count Help
I'm tallying whether congressmen vote for a particular bill and I need a way to turn their votes into 1s and 0s. The data I have represents the congressmen by name, and I want to create a unique row for each one, where columns are bill votes and rows are the congressman's unique record.

Here's the layout I want:

Column A: Names (Tom, Dick, Harry)

Column B: Education Bill (1, if tom votes, 0, if he votes against)

Column C: Bill #3....

Here's the layout I have:

Column A: Name

Column B: Bill #1 - voted for, listed by name

Column C: Bill #1 - voted against

Column D: Bill #2 -...

I need it to be flexible enough that I can add in bills as they come up. Right now, what I'm doing is placing all the names in the first column, and running a CountIf(A:B, A1)-1 for each bill, which tallies whether there's a name in the Bill column that matches the name column. But, this is combersom and is causing problems when I add bills. 

I'm hopeful there's a better way. Thanks!!

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

following is my convoluted solution to mimic what I understood Greg wanted in terms of layout ... just for getting the count of For and Against the layout does not have to be this convoluted

yogi_Compute Stats For A Dynamically Growing Table Both In Number Of Columns And Rows

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #777  Sep 23, 2012    www.energyefficientbuild.com.

user Hermann the german said: (http://productforums.google.com/forum/?zx=efkljiwxbna#!category-topic/docs/spreadsheets/eVGdjbqkJZM)
What is the best way for an automated event query?

I want to query an e-mail list of about 50 members to regular weekly events. The events (occurred 1 or not 0) should be entered without my help in a table. There, there is some simple statistics with the values​​.

Each member should be allowed to enter only their own messages. The feedback could e.g. via the e-mail answer.

So far I have conducted in such a table by hand and entered the e-mail replies. Now I want to automate the whole thing.

Are there any ideas on this. Which Google tools should I use for this project? Calendar, Drive / docs scripts? A solution should not be too sophisticated. I would not invest more work into the project than I save.

Thank you for your suggestions and tips!

Hermann

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

the following solution provides a degree of automation

I have converted the affected formulas to array formulas so that more data can be entered in more rows without having to drag down formulas

the only two formulas that have to be dragged to the right when one adds more names to the right are the formulas in cells

G2

and

G4

Further automation of this is possible in the sense that one can create a Google Form via which the persons with the pagers can submit their relevant information via the Form and the information will be automatically logged in a so called Form Responses sheet ... but that may or may not be of interest here.

For more automation, one should explore using Google Apps Script.

yogi_Split A Set Of Numbers In Cells Of A Column Row By Row

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #776  Sep 23, 2012    www.energyefficientbuild.com. 

user Maia Gatuna said: (Regular expression as an alternative to split with ArrayFormula?
Hi!

I have been searching around for a while, I am quite new to google docs & excel so please bear with me.

I am creating a spreadsheet with a form to register the participation of people in a charitable raffle. In the form, people tell which numbers (from 000 to 1000) they want to reserve and buy. The idea is to allow the person to choose several numbers separated by space (e.g.: 45 68 900 789) up to a maximum of 10 numbers

My first idea was using SPLIT to separate the input into multiple cells in order to follow the calculations. Something like this:

=ARRAYFORMULA(IFERROR(COUNT(SPLIT(C2:C; " ")))

so that if C2 = 10 20 30 40,

the output would be D2 = 10    E2 = 20    F2 = 30   G2 = 40

BUT, as I read in this forum, split DOES NOT WORK with arrayformulas, this is a reported issue. I found as an alternative Regular Expressions, and as I am completely lost in this field, I copied a formula I found in another post. This formula split a list of numbers  separated by ";"

My question is> Could someone please tell me how to modify this formula so that it splits numbers separated by SPACES and not by ;?

See my spreadsheet. Sheet 1 is what I want to achieve, Sheet 2 is the formula with RegExpr that I found on this forum:

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

Many, many thanks in advance!

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

The SPLIT function does work with arrayformulas ... however it doesn't split all the components of the first row cell

so in your case it should work fine except that the first row will simply read Number ... see the solution in my following blog post:

yogi_Make A Computed Column For Student Scores In Sheet1 And Extract ClassRoom Records By Teacher

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #775  Sep 22, 2012    www.energyefficientbuild.com. 

user cgraffius said: (http://productforums.google.com/forum/?zx=k0b6vmf3deg6#!category-topic/docs/spreadsheets/njaVeLNIgu8)
Spreadsheet to aggregate specific data
Hello-

I'd like to the data received from a form in a spreadsheet to aggregate specific information and view the summary of this. For example, each math teacher is filling out a form for each student. The math teacher provides her name in the form. I would like to sort so that only the data for that teacher is aggregated into a summary. Any tips or scripts for this? Thank you!

Courtney

I'm using Google Chrome.

----

...here is the link...

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

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

following is a solution to a bit more generalized problem

yogi_How To Delete A Comment And The Red Triangle (The Comment Marker) Associated With The Comment

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #774  Sep 22, 2012    www.energyefficientbuild.com. 

user Alice BC said: (http://productforums.google.com/forum/?zx=jsaua8jog7d0#!category-topic/docs/spreadsheets/cL-_6CYKlg0)
How do you erase a comment written on a spreadsheet? 
I've tried a few things. The help site says I can edit or delete, but when I use the Edit on the toolbar, it doesn't work.

Also, does anyone who has access to the link have the ability to see my comments, and make their own?

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

here we go ...

yogi_Compute Number Of Children Of SubGroup Given The Number Of Children Of Each Member Of A Group

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #773  Sep 22, 2012    www.energyefficientbuild.com. 

user cadmia said: (http://productforums.google.com/forum/?zx=jsaua8jog7d0#!category-topic/docs/spreadsheets/0BPLft_pCkU)
Comparing Two Ranges
Hello - 
I'm trying to pull together a summary comparing a smaller group to a larger group - For example:
    
Children
Jane    1
Betty    2
Susan  3
Frank   4
Scott    5
Ernie    6

Then I have a summary page with a subgroup that only contains Jane, Betty and Scott, but I want to know how many children just those 3 have. I would expect the answer for that subgroup to be 8.

Right now I am doing a bunch of SUMIF statements (e.g. SUMIF = Betty + SUMIF = Jane + SUMIF = Scott), but I'm sure there is an array or something I could be using, or perhaps a SUMPRODUCT formula. Please help?
----------------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Sum Values In column C For Specified Name In Column D And Specified Month In Column A

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #772  Sep 22, 2012    www.energyefficientbuild.com. 

user moldow said: (http://productforums.google.com/forum/?zx=jsaua8jog7d0#!category-topic/docs/spreadsheets/TrC6NdsUs3k)
How function use?

Good Morning. I have question. My table look like this:

Month    |    Description    | Price   |  Person

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

January  |  Some text here1  | 10,50€  |  John

January  |  Some text here2  |  3,60€  |  John 

January  |  Some text here3  | 40,89€  |  Bob 

February |  Some text here4  | 23,60€  |  John 

February |  Some text here5  | 30,78€  |  Bob 

February |  Some text here6  |  8,56€  |  Bob 

I need function, which do follows:

Select person John, where month is January and do sum(Price)

Some inspiration for me? Thank you very much.

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

following is a solution to the problem

yogi_Find Conditional Sum Of Costs By Name Based On Entry Y In Column J Of Sheet Requests

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #771  Sep 21, 2012    www.energyefficientbuild.com. 

user Chrisnetika said: (http://productforums.google.com/forum/?zx=vay6zqqzi38u#!category-topic/docs/spreadsheets/zmt3unYQNQM)
Sum and IF
On one sheet I want to track spending of ocho that is linked to another sheet that has transactions.I want it in a sense where user has a balance and on another sheet thru lookup it takes all transaction that user is in and subtracts the cost and shows current balance.Obstacle is if I have a transaction with sent = n or blank then I dont want it to be counted in spent unless there is a Y.Method im trying is.. if it doesnt have a Y and is blank.. it turns total spent to false , and if I set statement where if it is blank or n to then be 0 .. even if any other transaction has a Y it will keep show total spent as ... 0 I have 1 transaction that is N and another that is Y .
----
Sheet
sample data as shown

and other sheet that is tracking what is spent is in requests.
I dont want transaction to use their balance unless it is sent via Y in Sent? column
--------------------------------------------------------------------------------------------------
following is a solution to the problem