## Tuesday, November 26, 2013

### yogi_Extract Deposit No1 Deposit No2 And Deposit No3 (if applicable) For The Month And Year Of Specified Date

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
Finding the nth cell with a date in a certain month
Hi.  I'm working on a budget spreadsheet.  I get paid every 2 weeks.  I've come up with a listing of all of the days in the next year that I should expect to be paid.  Here's the first bit of it:

 Month Date of deposit Amount of deposit 11 11/15/2013 \$1,000,000,000.00 11 11/29/2013 \$1,000,000,000.00 12 12/13/2013 \$1,000,000,000.00 12 12/27/2013 \$1,000,000,000.00 1 1/10/2014 \$1,000,000,000.00 1 1/24/2014 \$1,000,000,000.00 2 2/7/2014 \$1,000,000,000.00 2 2/21/2014 \$1,000,000,000.00 3 3/7/2014 \$1,000,000,000.00 3 3/21/2014 \$1,000,000,000.00 4 4/4/2014 \$1,000,000,000.00

As you can see, we're pinching our pennies. :-)  Now, on another sheet, I'd like to set up three cells.  The first one is the date of the first deposit in a month.  The second is the date of the second deposit.  The third is the date of the (infrequent) third deposit of the month.  Like this:

 Current Month: 12/1/2013 Carryover First Deposit of Month 12/13/2013 Second Deposit of Month 12/27/2013 Third Deposit of Month N/A

I'd like the dates in the right column to autofill from the listing of paydates above.  I'm really coming up with a blank on how to do that, though.  It seems like it would be something like, "find the first entry with a month and year matching the month and year in the current month" and then again for the second entry and the third.  But how?  Any suggestions on an approach to this?  Many thanks in advance.
---
hanks for the reply.  The sheet can be seen at:

I've added notes to the cells that, I hope, explain what I'd like in each cell.  Thanks again for taking the time.
-----------------------------------------------------------------------------------------------------------------------------

## Sunday, November 24, 2013

### yogi_Sum Up Row By Row Total Amount Of Transaction per Client From Table Of Open-Ended Number Of Transactions Listed With Dates And Amounts

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
Sum only numbers in a single column or row containing mixed dates AND numbers
OK, I've searched, but cannot find the answer. I tried =SUMIF, but could not get that to work. This has to be simple, but it is eluding me...

I have a row which contains both numbers and dates

Col A          Col B         Col C         Col D        Col E       Col F         Col G        Col H       Col I             Col J
Client A   |   \$1120   |  08/02/13   |   \$250   |  10/22/13   |   \$303   |  11/10/13   ||   \$432   |  11/21/13   |   [TOTAL]

I want to sum the sales numbers (in Columns B,D,F, and H) and place answer in the [TOTAL] cell in Column J, but obviously not include the dates (in Columns C,E,G and I).

I will have to keep adding amounts and dates, moving to the right, so simply adding every other column would work, but would be tedious. QUESTION: What is a formula I can place in Col J that would automatically sum just the numbers and ignore the dates?

Thanks!
--------------------------------------------------------------------------------------------------

### yogi_Compute Appointment Date And Time In Appointment Location Time Zone Given Start Time And Date In EST Zone

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
Transit time for shipments
I am wanting to build a way to track shipments / truckloads for my company through a simple interface based on bill time and transit time to person recieving.  Is this best accomplished through sheets or a Google script?  I need to factor in a receiving window a the person receiving the shipment also based on the time.

Donny Sloan
---
Yogi,

I am trying to calculate the transit time from shipments from Dalton, Georgia to other plants based on ETA times.  On the first sheet in cell A3, the bill time and date is added.  In cell B3, the user selects the destination which contents are populated from a list on the second tab of the worksheet named "Data".  Cell # C3 does a VLOOKUP based on the destination in the data sheet and returns the time.  Cell # D3 performs the sum function and adds the times together.  I need to somehow calculate in where it returns the time for the timezone the destination is at and not EST as it is currently doing.  Please let me know if you need further information.

Donny
--------------------------------------------------------------------------------------------------------------------------------------------

## Saturday, November 23, 2013

### yogi_Compute Average Starting Time From A Table Of Starting Times

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
Average time when crossing midnight
Hello,

I'm trying to take an average of a list of times, some are before midnight and some are after.  As I'm sure you know, using a simple average won't work in this case.

Using the following three times:

11:35 PM
1:00 AM
12:30 AM

Using the AVERAGE function will yield an average of 8:41 AM which is definitely not correct.

Any help with the proper formula here?  Your assistance would be much appreciated.

Thanks,

Zack
----------------------------------------------------------------------------------------------------------------------------------------

### yogi_Given Starting Balance And Expense Line Items Compute Row By Row Running Balance

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
IF/OR Statement to keep a running bank balance
Hello All,

I am developing a simple accounting system that categorizes expense and income for an event. I have created a Chart of Accounts on a Sheet which assigns every income and expense category and number. Another sheet is the General Ledger. Every transaction at the event will go into this Sheet and be categorized with the amount of income or expense on detailed. I would like to create a final column on the General Ledger which keeps a running balance after each transaction (row) of what the cash balance is. Starting cash for the event is \$5000.

I tried doing this by creating 2 formulas. One in J2 and one in J3 on the General Ledger Sheet. The J3 formula I expanded down the column. Even though these formulas seem logically right, the result is that the formula keeps subtracting (giving the ELSE result) rather than adding when the condition of an income category is met.

I'm sorry if I don't know the terminology for this stuff. I am a beginner with programming .

Here is a copy of the Spreadsheet if anyone can help.

Thanks,

Nmax
--------------------------------------------------------------------------------------------------------------------------

### yogi_Cross Tabulate Sum Of Expenses by category and Year

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
Does Google Sheets have a function equivalent to the Cross Tab query in MS Access?
Does Google Sheets have a function equivalent to the Cross Tab query in MS Access that will produce a table where the row headings are listed categories of sales and columns are years?  Each cell is a sum of sales.

An example of the SQL for this in ACCES is:

TRANSFORM Sum([q By Town].Sold) AS SumOfSold
SELECT [q By Town].Town
FROM [q By Town]
GROUP BY [q By Town].Town
PIVOT [q By Town].Year;

Thank you

wem3rd

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

### yogi_Replicate Numbers According To Specified Criteria (number of digits and leading 0s and without comma)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
I can't have numbers beyond 999 without commas?
I tried to duplicate a cell with 9644 in it, and all the other cells say "9,644". I have tried changing the format to plain text, normal, and a few others - nothing has changed it.
Attachments (1)
---

## Friday, November 22, 2013

### yogi_Compute Sum Of Expenses By Specified Month Year And SheetName

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
Using a cell to reference a sheet name in a Sumif formula
Hello,

I am creating an accounting spreadsheet that i would like to accommodate ease of adding account to the doc to the summary page.  The doc will work as follows:

In the Summary tab the account template column will be copied when a new account is being added,  change the year row if needed (B6 in this example) and then type the name in the account name cell (C3 here) that references a tab name copied from and renamed from an account tab template.

The cell I need help with is C7 (in "summary" tab), which will sum all expenses for the month of january.  The problem i am having is getting the formula to accept the sheet name when trying to reference cell C3.

I looked this up... thought i found a solution, but anything i tried returns an error.  The closest I've come is the following that returns an error "Argument must be a range":

=(sumif(""&C2&"!B:B","<2-1-&B6&",""&C2&"!E:E"))-(sumif(""&C2&"!B:B","<1-1-&B6&",""&C2&"!E:E"))

thanks again,

keith
------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Compute Count Of Colors By Date From Data In Columns A And B And Cross Tab Count Of Colors By Color And Date

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 22, 2013
Query function
im really a lazy bastard and dont like to make toooo many formula.. so here i go..

Data on the Table 1. plus the =query formula gives me the result on the right

`=QUERY(A1:C, "select B, sum(C) group by B pivot A label R 'Name'")`
``` ```

Table 1.
 A B C DATE NAME # RESULT ↓↓↓ 16-Nov red 1 16-Nov orange 2 Name 2013-11-16 2013-11-17 2013-11-18 16-Nov blue 3 blue 3 3 2 16-Nov yellow 1 indigo 2 1 17-Nov indigo 2 orange 2 1 3 17-Nov red 3 red 1 3 2 17-Nov orange 1 yellow 1 2 1 17-Nov yellow 2 17-Nov blue 3 18-Nov indigo 1 18-Nov red 2 18-Nov orange 3 18-Nov yellow 1 18-Nov blue 2

but what if there is no column C and the table looks like Table 2.
what (simple) =query formula should i do/use to achieve the same exact result?

Table 2.
 A B DATE NAME 11/16/2013 red 11/16/2013 orange 11/16/2013 blue 11/16/2013 yellow 11/17/2013 indigo 11/17/2013 red 11/17/2013 orange 11/17/2013 yellow 11/17/2013 blue 11/18/2013 indigo 11/18/2013 red 11/18/2013 orange 11/18/2013 yellow 11/18/2013 blue 11/16/2013 orange 11/16/2013 blue 11/16/2013 blue 11/17/2013 indigo 11/17/2013 red 11/17/2013 red 11/17/2013 yellow 11/17/2013 blue 11/17/2013 blue 11/18/2013 red 11/18/2013 orange 11/18/2013 orange 11/18/2013 blue 11/16/2013 orange 11/18/2013 blue 11/16/2013 orange

i find =query really helpful for lazy bastards like me in making a table..
and one more thing, how can i format the date?
been struggling for hours now..