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

yogi_Rearrange Columns Of Response Table In Specified Order Based On Specified Number Of Beginning AlphaNumeric Characters

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


user Robby B said:
frequent reordering of column responses in forms spreadsheet
Firefox
windows 7
Others have reported the issue that the order in which questions are created in google forms is the order in which output is displayed in the spreadsheet regardless of how one changes the order in the form (which is often necessary). In my job I am creating new forms fairly often and they get changed (by myself and others) so keeping the questions in created order is rather difficult. 
I am looking for a function that can reorder the columns in a new tab/sheet. It would be helpful if my labeling scheme, (1a,1b,1c,2a,2b,2c, etc) which is at the start of each question, can be used in the identification (because this scheme is the only thing that will remain the same across all the forms I create). 
Any suggests?
-R
-----------------------------------------------------------------------------------------
I have used helper Sheet1a to do the prep work for finally rearranging the columns of the Response Table in Sheet1. I have generalized the solution by using the number of beginning characters of the question headers as a variable and also having the option of rearranging the questions in Ascending or Descending order.

yogi_Create A League Scoring Table Showing Ranks As 1st 2nd 3rd And So On

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


user Gary Colin said:
Problems creating a simple league table 
....
This simpler formula makes rest of my tasks a lot easier I think.
But I've another question Yogi... 
I had a very long-winded way of adding 1st, 2nd, 3rd etc as the first column in the league table.
So cells I3, I4, I6, etc on your table would become "1st", "2nd", "3rd" rather than just "1", "2", "3".
Any ideas?
Gary
-----------------------------------------------------------------------------------------
following is a solution to the problem ... my solution uses a single formula for the solution -- alright the formula is long but it is pretty straight forward solution. Perhaps a separate post on creating 1st 2nd 3rd etc. would be in order because that has application for many different types of problem ... but let us leave it to another time.

yogi_Setup Computed Column Formulas For Quantities And Prices As Specified

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

I have posted the following solution to the problem based on a question by user AWark ...
http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/5hVt7a1FIY0

The formulas for computed columns need to be array formula in row1 of the computed column for the formulas to work correctly and get automatically populated as the submittals get logged in the Form responses sheet

yogi_Join Non-Blank Entries In A Given Range Of Columns And Transpose To Corresponding Rows Per Specification

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

user TomSleekUK said:
Simplify this long formula?

https://docs.google.com/spreadsheet/ccc?key=0AsJ9Fp_9-2PadGl3R2QtSzkwUjlNTi1XNS1sbVhHMEE

C17:U34 - Is where the data is

V5:V11 - Is where I want the result

Currently I am using a really long formula that leaves trailing commas, but thats not really important.

If C17>0 then join C17 to U17, if not then do nothing.

Then do the same to the next row (C18) and join it on to the first result

In this case U17 and U18 are 0 so they should be ignored

I want a formula that does this for each row and joins the results together e.g.

1 Tuna, 1 Brown rice, 1 Banana

If U17 was 1 then it would be

1 Chicken, 1 Tuna, 1 Brown rice, 1 Banana

It would be easier to work on the sheet together so I can point and live explain what I mean; it's hard doing this not in real time.

Thanks in advance

---------------------------------------------------------------
following is a solution ... in regard to the needed formula in cells V5:V11

yogi_Expand An Item Count Into Corresponding Number Of Rows For Two Columns

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

this is an extension of the problem for which I had posted a solution in my following blog post:
http://yogi--anand-consulting.blogspot.com/2012/05/yogiexpand-item-count-into.html
-----
as an extension of the earler problem, user fearless said 
Okay, I have a solution that uses VLOOKUP in what I feel is a much cleaner approach.  I've posted my solution in:

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

The gist of it is that I use VLOOKUP as follows:

 =if(C23)>=$C$19,"",VLOOKUP(C23;$C$12:$E$18;2;true))

... where the first row of the array (C12, C13, ....) contains the running sum of the quantities so far. Take a look - it's relatively simple.

- ff

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

Great Job by user fearless.

There is generally more than one approach to a problem ... which solution is better is a mute point, because a solution has to fit the user's comfort level, project preferences, project constraints and so on. Also as situation evolves, one may begin to prefer another solution compared to the one one had chosen to use in a different situation, under different circumstances.

In any event, following is my single array formula without using any helper columns, or multiple helper formulas ... different YES! ... convoluted YES! ... better ... Don't Ask Me! ... it all depends on your situation.

yogi_Compute Sum Of Entries In A Column For The Last Specified Number Of Days

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

Based on a post by Adam ...
http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/LEYuTOmGLhw
I have created the following illustration