## Sunday, August 31, 2014

### yogi_Extract Information For Specified Street Numbers From A Table Of Odd And Even Street Name Ranges

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-31-2014
post by Emanuel Borg:
Multiple index/match/lookup ? - Simple idea, but I can't find a solution
Hi, I've reached my Spreadsheets knowledge limit. Does anyone have an idea what kind of formula I could use to extract the correct Post code (column D) based on the Street name (column A) and whether the street number is odd/even and between the numbers in columns B and C. See exemple below:

Thanks a lot for any tips!
/Manne
---
hank you for all your replies!

James, you are absolutely right! This is my goal:
I think I understand what Emanuel is wanting. Looking at the sample data take the number 27 in cell B10 where it is an odd number and is between the odd range of 11 to 115, so the code to pull is 123 46.

The code suggestions that I received looks great, I they seem to work, however the range to search is huge, and I have to have an automated formula which uses the existing data to pull correct information.

In Sheet2, I would love to VLOOKUP(A2,Sheet1!A1:E7,4,0) if and only if B2 >= the vlookup's same row's B column, AND B2 <= the vlookup's same row's C column... if this is even possible.

Does this make any sense to you? It's difficult to formulate my problem... *cough* Please have look at the example.

Manne
-------------------------------------------------------------------------------------------------------------------------------

### yogi_Extract Information For Specified Street Numbers From A Table Of Street Name Ranges

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-31-2014
post by Emanuel Borg:
Multiple index/match/lookup ? - Simple idea, but I can't find a solution
Hi, I've reached my Spreadsheets knowledge limit. Does anyone have an idea what kind of formula I could use to extract the correct Post code (column D) based on the Street name (column A) and whether the street number is odd/even and between the numbers in columns B and C. See exemple below:

Thanks a lot for any tips!
/Manne

 A B C D E 1 Street name Street no. from Street no. to Post code Area 2 Abc road 1 9 123 45 City 3 Abc road 2 8 123 45 City 4 Abc road 10 52 123 46 City 5 Abc road 11 115 123 46 City 6 Abc road 54 120 123 47 City 7 Abc road 117 227 123 47 City 8 9 Street name Street no. Need formula that finds the following post codes from list above 10 Abc road 27 123 46 City 11 Abc road 53 123 46 City 12 Abc road 119 123 47 City

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

## Saturday, August 30, 2014

### yogi_Formula In Cell C3 For Sum of Hours Worked For Range Of WorkHour Entries Per Spec

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-30-2014
post by Jeremy Falletta:
Can I use an ArrayFormula to perform two functions on one range in a single formula?
Hello,

I'm still learning Sheets, and I'm wondering about how to do something. I have a range on which I want to A) perform math and B) sum the results of A - I'm wondering if this is possible with something like ArrayFormula.

Specifically, I have two columns, both formatted for time: the first is a start time and the second is an end time. What I want to do is calculate the duration between each pair, and then sum those. Currently, my workaround is to have a separate, hidden column that performs the first calculation, and then sum that column. Effective enough, but not very elegant.

Can anyone help?

Cheers,
Jeremy
---
Yogi,

Thank you for the reply, and I'm sorry it took me so long to follow up - been doing lots of experimenting in the mean time!

Anyway, check out this small example I put together, if you have a chance: https://docs.google.com/spreadsheets/d/1gZPQ1QCWWIWMrXAh56a6ES5MkDtrnv8Fwz5ufz6I3Lw/edit?usp=sharing

What I'm actually working on is an employee schedule. Right now, I have two columns (repeated for each day of the week, but that's not important at the moment), one for shift start times and one for shift end times. What I need to do is calculate the total amount of hours I've scheduled on a given day.

Currently, my workaround is to have a separate column (actually hidden in the actual spreadsheet, to prevent clutter) that calculates the duration of the shift, controlling for a lunch break if they are working 8 hours. You'll see that in the formula.

Then I simply sum that column to get a total. But what I want to do is eliminate that worker column, and do the entire calculation in one cell. I'm positive that's possible, but I'm not quite at the point where I can see how to manage it.

Can you assist? Any help is very much appreciated!!

Thanks,
Jeremy
---------------------------------------------------------------------------------------------------------------------------

### yogi_Formula To Pull Sequential Values From Column A Into Every Sixth Row Of Column C

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-30-2014
post by Anuj Ang:
Copy list of column into separate column on every nth row
I need some assistance in copying and pasting some cells.

I have a column with data on its rows.
I wish to have the entries of this column pasted into a separate column, BUT on every nth row, while keeping the data continuous.

Here is a screenshot of what I want:

Column N is my raw entries.
And I want them pasted into column H, but on every 10th row.

Is this possible?
-----------------------------------------------------------------------------------------------------------------------------------

### Compute Number Of Months Employee Is projected To Work In Current Year

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-28-2014
post by Gerry Potter:
Number of Months
I am trying to write a formula in one cell (E4) that determines how many months an employee is forecasted to work for the company in the current year.

C4 is the Hire Date of an employee
D4 is the End Date of an employee if the employee leaves the company in the current year or if they are forecasted to work through the end of the year "12/31/2014".

For instance if an employees' hire date is 5/15/2012 and they are forecasted to work through the end of the current year, the number of months returned in E4 would be 12.
If an employees' hire date is 2/1/2014 and they are forecasted to work thru the end of the current year, the number of months returned in E4 would be 11.
If an employees' hire date is 5/15/2012 but they leave the company on 7/1/14 then the number of months returned in  E4 would be 6.

Can you assist with a formula that I can have in E4 that will evaluate c4 and d5 to return the correct number of months in the current year?
---------------------------------------------------------------------------------------------------------------------------------

## Thursday, August 28, 2014

### yogi_Row By Row Count Of Total Number Of Choices and Row By Row Count Of Each Choice Made

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-28-2014
post by Andyjst:
Hello,

I am a google form that has a question that several options can be ticked, I am trying to filter that data so I can add up the number of times each box has been ticked individually. The countif function for text only works if I put in every options rather than separates.

e.g.

question with options A B C D, options A B and C are selected and displayed in the response form as A, B, C. I want to be able to count the amount of A's, B's C's etc. Countif only works when I do the following =COUNTIF(Sheet1!D2:D999, "A, B, C")

Thanks

**********************************************************************
This email is confidential and may contain copyright material of the John Lewis Partnership.
If you are not the intended recipient, please notify us immediately and delete all copies of this message.
(Please note that it is your responsibility to scan this message for viruses). Email to and from the
John Lewis Partnership is automatically monitored for operational and lawful business reasons.
**********************************************************************
John Lewis plc
Registered in England 233462
Registered office 171 Victoria Street London SW1E 5NN

Websites: http://www.johnlewis.com
http://www.waitrose.com
http://www.johnlewis.com/insurance
http://www.johnlewispartnership.co.uk

**********************************************************************

## Tuesday, August 26, 2014

### yogi_Pull Entities With Maximum Number Of Occurrances in Column D

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-24-2014
post by Zakir B:
Counting help needed!
Hiya!

I have a bunch of text entries in column D. I wanted to find what the most common entry in that column is, so I used the formula:
=unique(filter(D:D,countif(D:D,D:D)=(max(if(D:D<>"",countif(D:D,D:D))))))

This formula is in cell E1, and is working very well to show me the most common entry.

My question, however, is regarding the number of times the most common entry appears. How can I make a cell display the number of times the contents of cell E1 occur in column D?

I'm really stuck and I'd like to learn!
------------------------------------------------------------------------------------------------------

## Sunday, August 24, 2014

### yogi_Compute Number Of Instances In Specified Months (Date Ranges) And Associated Stats

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-24-2014
post by Daniel MIA:
Countif falls between and range AND numerous criteria
Hi, thank you for your time in helping me out... I'm completely baffled! Let me try and explain (sorry if it's a bit confusing)...

I have a database type sheet called 'data'. In row 4 is the date, in row 7 there will either be 'Basic' or 'Additional'.

In another sheet, I have broken down into months... column A has the first date of the month, column B has the last date of the month. In column C, I'm trying to count the number of entries in the database that fall on that month AND are classed as 'Basic'. (Then I'll use a similar formula to work out the number of 'Additional' in column D).

So far, I have figured out the following formula will count the total number of entries in the month, but I can't figure how to make a formula also check if it's 'basic' or 'additional' in row 7 of the 'data' sheet:

=COUNT(IFERROR(FILTER('data'!\$E\$4:\$FB\$4,'data'!\$E\$4:\$FB\$4>=A2,'data'!\$E\$4:\$FB\$4<=B2)))

If there are better functions to use, please let me know!

Thank you!!!
Dan

---
Hi, thank you for your time in helping me out... I'm completely baffled! Let me try and explain (sorry if it's a bit confusing)...

I have a database type sheet called 'data'. In row 4 is the date, in row 7 there will either be 'Basic' or 'Additional'.

In another sheet, I have broken down into months... column A has the first date of the month, column B has the last date of the month. In column C, I'm trying to count the number of entries in the database that fall on that month AND are classed as 'Basic'. (Then I'll use a similar formula to work out the number of 'Additional' in column D).

So far, I have figured out the following formula will count the total number of entries in the month, but I can't figure how to make a formula also check if it's 'basic' or 'additional' in row 7 of the 'data' sheet:

=COUNT(IFERROR(FILTER('data'!\$E\$4:\$FB\$4,'data'!\$E\$4:\$FB\$4>=A2,'data'!\$E\$4:\$FB\$4<=B2)))
If there are better functions to use, please let me know!

Than
k you!!!
Dan

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

### yogi_Pull Unique Numbers From Cells A5 to K33 Of Sheet Named Data And Their Corresponding Number Of Instances

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-24-2014
post by Question Marc:
Want to extract a list of unique values from a range of unorganized data.
Hello,

So I have this spreadsheet where I am compiling numbers, and I want to know how many time recurring numbers showed up (if they showed up more than once).

I've made an example document, see here.

In the example above, I want to take the numbers with a red background in the sheet "Data" and have it sorted out in the "Crunch sheet" sheet in such a way that I can tell "Number X has occurred 20 times throughout the data compiling".

I know the function "COUNTUNIQUE" can tell me how many unique instances of number there are, but that isn't really useful to me right now since I want to know what are the numbers that reappear. I've seen some options while googling for insight, but none really applied to an unorganized mess of number like mine.

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

## Friday, August 22, 2014

### yogi_Conditionally Format Those Cells In Column A That Are Not In The List In Column B

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-22-2014
post by George123455:
How do I use conditional formatting to check if a cell contains text from a list of cells?
I would like to be able to highlight specific cells that do not contain a list of values contained in another range of cells.  Essentially, this is data validation (and I am aware of the data validation function), but I want to make it extremely obvious when a value of one of the conditionally formatted cells does not contain a specified value from the range, since the data validation option is not terribly eye-catching.

I have tried the following, but it either doesn't work, or results in an error.

"Text does not contain" Sheet1!B2:B30,
"Text does not contain" Indirect(Sheet1B2:B30)
"Custom Formula" =range<>indirect(sheet1B2:B30)

I'm probably doing something wrong, but I can't figure it out.  Any ideas?
-----------------------------------------------------------------------------------------------------------------------------------

### yogi_Given A Start Date In Cell A2 Compute Dates In Successive Rows A3 And Down By Cumulatively Adding Needed Days In B3 And Down

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-22-2014
post by MikeWallaceDev :
How to refer to a previous cell in the same column
Hi,

I am trying to create a schedule in a sheet.  I'm trying to create a column with dates that will increment based on the date in the cell above + a values in a column.

What I want to achieve looks like this :

The first date (2014-09-18) will be entered manually and depicts the actual start of the project.  All of the other dates below that date should be created by adding the number of days in the "Days needed" column.

(assuming that what we see in this bitmap is column A, B and rows 1, 2, 3, ...)

I have tried this : (in cell A3) =ArrayFormula(A2:A+B3:B) but this gives me a circular reference. And I don't understand why...

so, from my understanding (regardless of the fact that it doesn't work) this formula should give me something like :
A3=A2+B3
A4=A3+B4
A5=A4+B5 and so on.

How would I go about getting this to work?

Thank you

-MIke
--------------------------------------------------------------------------------------------------------------------------------------