## Saturday, May 31, 2014

### yogi_From Multiple Entries By Same Name In Column A Pull Data For First Instance Of Each Name

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
Hi,

Is it possible to return the first and last value also rater then a min max grouped by Column.

I just want to return the first value in the array not the minimal one.

Thanks
---
Here is an example: doc

It should return the first value depending on the date given by Name
------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Compute Row By Row Number Of Days of Activity By Specified Client During a Speified Month

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
post by romgl -- question by Polish user: (https://productforums.google.com/forum/#!mydiscussions/docs/O9TCwKC5fPs)

Hi Yogi,

I am trying to find a formula, that can help me count the number of days a client was active in a chosen month, as shown in the test spreadsheet with the link below :

Thus, for example, in the link above, on the Client activity sheet, if I choose February, I should see the following results :

Client A   2
Client B   2
Client C   0

But if I select April, I should see the following :

Client A   1
Client B   0
Client C   0

Thanks again for your efforts in helping me with this.
------------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Single Formula In Cell A1 Of Row 1 For Column By Column Sum Of Multiple Columns

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
SUMMING MULTIPLE INDIVIDUAL COLUMNS
How do I sum multiple columns in an individual fashion without doing it 100 times?

Ex:I have 100 columns of data and I want the sum of each column.  Thanks.
----------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Compute Class Size Value Based On Table Of Number Of Students In Class And Class Size

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
need formula: if cell value = range 1 then input a, if range 2 then input b, etc.
Hi,

I am recording the number of students attending a classes in a spreadsheet. That number ranges anywhere from 2 to more than 100.  I want to have another column called class size in which the number of students is labeled a size 1-5 based on the number of students who attended. The ranges would be 1-9, 10-19, 20-29, 30-39, 40 + . I'm looking for a formula that would take the number of students and output the class size. for example cell m24 has a value of 33 and I want the "class size" value '4' output into cell n24.
---------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Formula For Cell E13 to house 'B' If D13 less than 5 -- 'S' if D13 greater than 5 -- 'G' if D13 equals +5 or -5

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
Conditional formatting adding color and letters to a cell based on another cells
I want to have cells change color and add a letter to that cell based on the value of another cell. Im using cell D13 for numeric ranging from 200 to -200, in cell E13 Im running custom formulas that change the cell E13 to red, blue or grey based on the values of D13. "=D13<5" =blue in Range E13, and "=D13>5" =red in range E13. I want to also have it put a B in the blue ones an S in the red one, grey in the -/+ 5 ones. Not sure how to add the B/S/G values to it.
---
Here is the a sample, the first tab, Daily closes. D13 and E13.

Thanks
--------------------------------------------------------------------------------------------------------------------------------------------------------

### yogi_From A Table Of Products By Name Create A Table Of Names By Product

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
I am trying to create several lists of names that are based on abilities from a table. The table is in rows like this:
A                 B                  C                   D                   E
1  Lisa        COOKS      CLEANS      DRIVES          SEWS
2 Linda       COOKS      CLEANS          na                   na

For example:

Lisa    Cooks, Cleans, Drives, and Sews.
David Cooks, cleans
Linda Drives
Sue Cooks and Sews

Cooks List:
Lisa
David
Sue

Cleans list
Lisa
David
Sue

Drives list
Lisa
Linda

Tried using Hlookup but the value returned is text and always get an error because Hlookup expects the result to be  a number. Does anyone have a simple solution or function I can use to do this??

Thanks

---
Hi,

This is a example of what I was trying to do, but without the errors in the list.

Thanks,

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

## Friday, May 30, 2014

### yogi_Find The Title Of Column(s) (in row 1) For The Maximum Value(s) In Row 2

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-30-2014
Help With Formula
Hello. I am trying to firgure out how to find the max value between 3 columns and return the title of that column.

Example:
a  b  c
1  2  3
In that example I would want a cell to show me c not the number 3.
---
I can do it with a long series of IF statements but if anyone has a suggestion on something shorter and easier to mange, that would be awesome.
``` =IF ( AND ( K6 > L6 , K6 > M6 ) , "A" , IF ( AND ( L6 > K6 , L6 > M6 ) , "B" , IF ( AND ( M6 > K6 , M6 > L6 ) , "C" , IF ( AND ( K6 = L6 , K6 =M6 ) , "A & B & C" , IF ( K6 = M6 , "A & C" , IF ( L6 = M6 , "B & C" , IF ( K6 = L6 , "A & B" , "ERROR" ))))))) --- ```
I also need it to skip any zero values.
-----------------------------------------------------------------------------------------------------------------------------------------------

## Thursday, May 29, 2014

### yogi_Compute Row By Row Number Of Days of Activity By Specified Client During a Given Month

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-29-2014
post by romgl -- question by polish user: (https://productforums.google.com/forum/#!mydiscussions/docs/O9TCwKC5fPs)
How to list and count unique cells in multiple columns?
Hi,

I have been trying to use the COUNTUNIQUE formula along with the FILTER formula, for a set of data, where I am trying to count the unique dates in a month on which activity has taken place with certain clients. While using the COUNTUNIQUE formula with FILTER, I notice that the output shows 1, even when there was no activity with a client in a given month.

---
Hi Yogi,

I did wrap the expression in IFERROR. Please find below the link :

In the file you will notice, there are 3 clients, Client A, Client B and Client C, with their own dashboard with details regarding the service offered to them. Client A and Client B use the service on 2 days in the month of February. Client C does not use the service at all yet.

In the Client Activity sheet, when I try to calculate the no. of days the 3 clients were active in the month of February, using the formula IFERROR along with COUNTUNIQUE and FILTER, I get a result ( which can be viewed on the shared spreadsheet) different from the expected result, which was to be :

Client A    2
Client B    2
Client C    0

Apart from the above, in the same shared spreadsheet, I am facing another problem, regarding copying data automatically with the help of a formula, from the Transactions Log into the respective Client's dashboard.But I was wondering if I am able to post the problem here, since it is not directly relevant to the subject of the post. Presently I have been referencing cells manually.

I would be very grateful if you are able to assist with these problems.

Thanks for this initiative.
-----------------------------------------------------------------------------------------------------------------------------------------

## Wednesday, May 28, 2014

### yogi_Pull From Sheet1 Entries In Columns A To C And Put Them All In One Row

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-28-2014
Columns of data paste in all one row.
Going from new GD sheet to an old GD sheet, pasting (for instance) three columns and five rows comes out as one long row. Hoping that the data posts correctly, so for example:

 y Daniel Frey y Kevin Mukai y Harry Chawla

Pastes into:

 y Daniel Frey y Kevin Mukai y Harry Chawla

Fun.

Any ideas?

Also, re-creating a couple hundred old GDs into new GDs is not an option.

--------------------------------------------------------------------------------------------------------------------------------
I had posted a formula in the forum ... following is an illustration for solution to a bit more generalized problem

## Tuesday, May 27, 2014

### yogi_Using Timestamp Column Compute Number Of Transactions For Each Day

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-27-2014
Ignore or delete time stamp in date fields
'd like to see a count of how many applications I receive each day, which I can do using a pivot table.
However, the value reported includes both the date and time, and I need to either delete the time stamp or ignore it.
Here's some sample data:
 5/1/2013 1:52:00 5/1/2013 14:41:00 5/1/2013 21:57:00 5/2/2013 21:58:00 5/5/2013 13:07:00 5/6/2013 16:41:00 5/9/2013 18:50:00

I tried doing a search and replace for any values after 2013, which turned the time stamp to " 0:00:00", but I'd like to know if there's an easier solution that eliminates or ignores those values. Choosing to display just the date does not solve this issue.

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

### yogi_Use A Computed Column Formula In Cell of A Frozen Row So The Range Can Be Sorted With Formula Working

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-27-2014
Help with array formula positioning
Hello, everyone!

I am needing some help re-positioning a function into another cell and still have it work. I will start with a screen shot and the syntax:

The function is currently in D3 and reads as follows: