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

                      Google Spreadsheet   Post  #1745
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-31-2014
post by Emanuel Borg:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/HzGrIBPuNl8)
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!

Perhaps I explain myself better here. Here is the link to an example document: https://docs.google.com/spreadsheets/d/1z8Ea3KwzQ1viBdwUbwVPRtls1Y1YeTZx4S1PEYv7G88/edit?usp=sharing

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.

Thanks for your patience,

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

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

                      Google Spreadsheet   Post  #1744
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-31-2014
post by Emanuel Borg:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/HzGrIBPuNl8)
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

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

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

                      Google Spreadsheet   Post  #1743
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-30-2014
post by Jeremy Falletta:
(https://productforums.google.com/forum/#!mydiscussions/docs/Fy68bX64QFY)
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?

Thank you very much in advance for your time!

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

                      Google Spreadsheet   Post  #1742
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-30-2014
post by Anuj Ang:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/VSRl1SfBmkc)
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:

https://dl.dropboxusercontent.com/u/68017236/gsheetq.png

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

                      Google Spreadsheet   Post  #1741
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-28-2014
post by Gerry Potter:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/JgjHO1HVubw)
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?

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

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

                      Google Spreadsheet   Post  #1740
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-28-2014
post by Andyjst:
(https://productforums.google.com/forum/#!mydiscussions/docs/7dvJRO3wcFI)
Counting google form responses

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

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

yogi_From A Table Of String Of Locations And Reps In Spreadsheet2 Find Whether There Is A Rep For Locations In Column A

yogi_Rearrange Data in Columns A to C In DataBase Style So That Each Selected Option Is Presented In A Separate Row of Its Own

yogi_ Present Names In Cells A2 to D Row By Row Concatenated With Comma As Delimiter

yogi_Pull Entities With Maximum Number Of Occurrances in Column D

                      Google Spreadsheet   Post  #1736
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-24-2014
post by Zakir B:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Iq-5UV5DZbg)
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!

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

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

                      Google Spreadsheet   Post  #1735
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-24-2014
post by Daniel MIA:
(https://productforums.google.com/forum/#!mydiscussions/docs/zS3M-nshoK8)
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!

Thank you!!!
Dan
------------------------------------------------------------------------------------------------------------------------------------

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

                      Google Spreadsheet   Post  #1734
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-24-2014
post by Question Marc:
(https://productforums.google.com/forum/#!mydiscussions/docs/-J5cU5vwvdg)
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. 

Thanks for reading.

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

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

                      Google Spreadsheet   Post  #1733
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-22-2014
post by George123455:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/56W5vp1blCQ)
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

                       Google Spreadsheet   Post  #1732
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-22-2014
post by MikeWallaceDev :
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/2OJO9xOzLTY)
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

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

yogi_For Dates In Column F Compute Corresponding Date For Specified Previous WeekDay

                       Google Spreadsheet   Post  #1731
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-17-2014
post by Michael Gannon:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/gInrDI8Uw8U)
What formula would i use to change future dates to back date to its most previous friday?

Hey, everyone!! Would someone happen to know a formula that i could use that would change my future dates to fall back to a previous Friday...

H5 is referencing the date in F5, currently I've succumb to having to do it manually i.e cell H5  is =F5-3    and F5 is 9/1/2014

Any help would be awesome!! Thanks

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