yogi_From A Table Of Names And Expenses Compute Who Pays To Who and How Much

                                          Google Spreadsheet   Post  #1045
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 25, 2013
user lavvisk said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/4ru6XHx0v78)
Need help with my "who pays to who?-Sheet"

Hi,

I'm making a sheet to make it easier to count who has to pay to who.

I need help to make the sum formula depending on the "shares" and add it to the total "status".

My testsheet: (it is the bright yellow field I need help with.)

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

I'll be thankful for any suggestions!

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

following is a solution to the problem

yogi_Compute Sum Of Amounts For A Specified Week Number Based On Whether Week Begins With Sunday Or Monday

                                          Google Spreadsheet   Post  #1044
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 25, 2013
user Eduard Grama said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ysegyRWPYJY)
Hi,

I am currently using Excel 2013 Professional but I want to be able to use the same formula in a Google Spreadsheet. Basically, the formula I have in Excel is this:

=SUMPRODUCT(--(INT((Sheet1!$U$2:Sheet1!$U$1500-DATE(YEAR(Sheet1!$U$2:Sheet1!$U$1500),1,1)-WEEKDAY(Sheet1!$U$2:Sheet1!$U$1500))/7)+2=H1),Sheet1!$AH$2:Sheet1!$AH$1500)

Column U represents dates.

Cell H1 is the number of the week (1-53) and column AH is the amount invoiced. 

The formula returns me the sum of all invoices for the week number specified in cell H1. All other invoices that don't have a date within the week number specified in cell H1 are neglected. 

Thank you.

Eduard

---

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

Thank you so much for your help Yogi.

Eduard

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

in the following I have presented a solution to a bit more generalized problem

yogi_WorkAround For Formatting A Column of 10 Digit Numbers Into Formatted Phone Numbers

                                          Google Spreadsheet   Post  #1043
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 24, 2013
user justindudek, question by Janet Toomey said:(http://productforums.google.com/forum/?zx=abeuu1nfhpz#!mydiscussions/docs/VYLlDfdiMqM)
How do I convert a 10 digit number into a phone number like this: (xxx) xxx-xxxx

Attached is an excerpt from the original spreadsheet that I imported into a Google doc.  Note that I used the Format Cell function/Special/Telephone number.  When I imported it into the Google doc, the formatting disappears and the telephone numbers become a list of numbers.  I don't seem to be able to copy and cut and paste any rows of information from Google docs, so I don't know how to show you that.

I've now also discovered that I can't seem to edit email addresses in this document.  When I try to click onto a cell with an email address that is underlined (not sure why some are and some are not), it forces open an email addresses to that email.

This is a high school reunion database sheet.  I put it on Google docs so the entire committee could update information on classmates as they came across it.  As such, it is confidential, so please destroy the document after you have seen what you need to see.  Thank you.

Janet 
--------------------------------------------------------------------------------

following is a solution show ing the formatted phone numbers in an adjacent column

yogi_WorkAround For Highlighting Double Entries In A Column

                                          Google Spreadsheet   Post  #1042
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 23, 2013

                                                 UPDATED (added Sheet2) on Mar-10-2013

user Daniel Berling said:(http://productforums.google.com/forum/#!mydiscussions/docs/qKKDQzukQLU)
highlight double entries
Hi folks,

I have several spreadsheets which are used by many people. Basically we are putting a numbers in one row and in the next one we report a problem. Sometimes it happens that numbers are equal. In this case I want to highlight the cell with another color as a warning showing that there is already an entry with this number. In excel it works quite well but is it also working in google spreadsheets?

Any hint would be helpful.

Greetings

Daniel

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

following is a solution to the problem

yogi_Match A Column In Another Sheet And Pull The Corresponding Value In Another Column Of The Other Sheet

                                          Google Spreadsheet   Post  #1041

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 22, 2013
user adb9000 said:(http://productforums.google.com/forum/?zx=3s6yw9idkqjm#!category-topic/docs/spreadsheets/FNDwy6DW4HA)
Help with Query 
I am pulling my hair out trying to figure out this one.

https://docs.google.com/a/integer.com/spreadsheet/ccc?key=0Ar4xX86wong8dFl0MG94dmdpYndfNVhKOVp2U0xPY1E#gid=1

If you look at the doc, you will see that in Sheet1 I have an Internal IP column and an External IP column.

What I am trying to accomplish is pulling the Internal IP from Sheet1 into the External IP of Sheet2 by comparing External IP on Sheet2 to External IP.

Can someone please take a look and see what I am doing wrong?

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

following is a solution to the problem

yogi_Compute Sum Of Hours For Specified Date Ranges

                                          Google Spreadsheet   Post  #1040

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 21, 2013
user Scuzzball said:(http://productforums.google.com/forum/?zx=3s6yw9idkqjm#!category-topic/docs/spreadsheets/W8sXsn9why0)
Summing numbers between dates
So I have read a few questions similar to this, but I wasn't able to understood how the answer worked, or was to make the answers work for me.

Sample data: 
https://docs.google.com/spreadsheet/ccc?key=0AqU4LU4RNy0AdGRHWUdfcFcydkN0blV1NURXaXlKYlE#gid=0
(World writable)

I know SQL well enough, so I was trying to use the query function, but I did it wrong, and am not really sure what.

I don't just want the answer, I actually want to understand the function.

Many, many thanks for any help.
Mark.
---------------------------------------------------------------------------------------------------------------

in the following solution I have also included one array solution for the entire set of date ranges

yogi_Perform Multi-Conditional Count In Another Sheet

                                          Google Spreadsheet   Post  #1039

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 20, 2013
user Monica Rampani said:(http://productforums.google.com/forum/?zx=icnslbsw6sms#!category-topic/docs/spreadsheets/KTDN9AnBfKo)
Array Formula based on Date Range and Multiple Criteria
Any help would be much appreciated.  I have the following spreadsheet:

A                              B              C              D             E

Timestamp         Username FA Code  Date  Installed

1/30/2013 18:55:33 james 10131150 1/30/2013 Yes

1/31/2013 8:49:15 james 10005063 1/31/2013 No

1/31/2013 10:09:06 derek 10131135 1/31/2013 Yes

1/31/2013 10:13:04 james 10150588 1/13/2013 No

1/31/2013 10:57:13 james 10149630 1/31/2013 No

This information is on the first sheet called "Job Log Submissions".  On another tab I have a sheet called "Stats".  What I want to do is have a formula that will tell me how many entries each Username Installed within a date range from column A. 

For example, I want to know how many sites did James install (a "yes" answer) between 1/30/2013 and 2/2/2013 based on column A (not column D.)  The answer with the above example should be 1.  

I have been playing around with Array Formulas but can't seem to get it right.  This is what I tried:

=ArrayFormula(SUM((‘Job Log Submissions'!A:A>=date(2013;1;30))*(‘Job Log Submissions'!A:A<=date(2013;2;2))*(‘Job Log Submissions'!B:B="james")*(‘Job Log Submissions'!E:E="Yes")))

But it is giving me an Error, maybe because I need to incorporate the time as well? Would anyone know how to achieve this? Thanks in advance

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

following is a solution to the problem