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

                                          Google Spreadsheet   Post  #1434
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
question by treesloth (http://productforums.google.com/forum/?zx=jqpgnmfd9ysa#!mydiscussions/docs/gl4_Ca-ZoaA)
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:

https://docs.google.com/spreadsheet/ccc?key=0Am_N19EG0WCfdG9Sdk1ITUFLSWxEN3hnZ3RSRmRsRUE&usp=sharing

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

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

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

                                          Google Spreadsheet   Post  #1433
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
question by waynemichael (http://productforums.google.com/forum/?zx=qvjxb3fcz80g#!category-topic/docs/spreadsheets/6UJjOhpGd_I)
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

                                          Google Spreadsheet   Post  #1432
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
question by dslon1971 (http://productforums.google.com/forum/?zx=io3sc132vv5a#!mydiscussions/docs/lht9d1smQ_k)
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.  

Thanks in advance for all of your help.

Donny Sloan

---

Yogi, 

Thanks.. Here's the link...https://docs.google.com/spreadsheet/ccc?key=0AtCSE3F3jSGbdDR2bEVGbWw3OW83NlpnX2RxOTl6TGc&usp=sharing

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

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

yogi_Compute Average Starting Time From A Table Of Starting Times

                                          Google Spreadsheet   Post  #1431
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 24, 2013
question by Zack18 (http://productforums.google.com/forum/?zx=983hltbeamzk#!category-topic/docs/spreadsheets/770nvlLkVb4)
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

                                          Google Spreadsheet   Post  #1430
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
question by landau calrissian (http://productforums.google.com/forum/?zx=ff6715r8y4lx#!category-topic/docs/spreadsheets/vfw34Ry3CGg)
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.

https://docs.google.com/spreadsheet/ccc?key=0Ai5PuqDumFcldEktTmIxYlQxT3UtUjRKSXBsSW5ycnc&usp=drive_web#gid=0

Thanks,

Nmax

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

yogi_Cross Tabulate Sum Of Expenses by category and Year

                                          Google Spreadsheet   Post  #1429
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
question by wem3rd (http://productforums.google.com/forum/?zx=6r9xv8nc97g1#!mydiscussions/docs/C9vsIXg-N-0)
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
---
https://docs.google.com/spreadsheet/ccc?key=0Al0I31hHV207dEVydnJTVkJIdTVfdFpCaXVBQlowdUE&usp=drive_web#gid=0
-------------------------------------------------------------------------------------------

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

                                          Google Spreadsheet   Post  #1428
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
question by ergalthema (http://productforums.google.com/forum/?zx=e8vm8nb175an#!mydiscussions/docs/E6GnGfQrawk)
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)

Google Sheet Number Commas.PNG

2 KB   View   Download

---

yogi_Compute Sum Of Expenses By Specified Month Year And SheetName

                                          Google Spreadsheet   Post  #1427
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 23, 2013
question by rkhubb (https://productforums.google.com/forum/#!mydiscussions/docs/TqiI2OvZyHA)
Using a cell to reference a sheet name in a Sumif formula
Hello, 

...And thanks in advance for your help...

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"))

Sheet: https://docs.google.com/spreadsheet/ccc?key=0AlJo4UnT5CF-dEhoLUVkTU5YRC1LNGx6MkhfZjlCY0E&usp=sharing

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

                                          Google Spreadsheet   Post  #1426
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 22, 2013
question by ajta@0316 (https://productforums.google.com/forum/#!topicsearchin/docs/after$3A2013$2F10$2F31$20AND$20-is$3Aduplicate$20AND$20-is$3Aresponded/docs/CpyQOVQ5h0k)
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..  

query language link > https://developers.google.com/chart/interactive/docs/querylanguage is very helpful, 

but i think i need some human intervention now.. 

thank you!

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