Hi All,

Wondering if i could get your hand with a problem, ive been racking my brain trying to work out how to solve this.

I have one table of activity with a location and start time / date and another table of location and cycle locations, number and end time (Cycle n+1 start time = cycle n = end time)

**Table 1**

Location Activity Start End Cycl Number

9690 1 25/07/2017 10:00 25/07/2017 13:30 ?

9465 1 25/07/2017 10:00 25/07/2017 12:00

9690 2 25/07/2017 13:30 25/07/2017 18:00

9690 3 25/07/2017 18:00 26/07/2017 1:00

9465 2 25/07/2017 12:00 25/07/2017 19:00

**Table 2**

Location Cycle Number End Time

9690 10 25/07/2017 13:00

9690 11 25/07/2017 19:00

9465 8 25/07/2017 11:00

9465 9 25/07/2017 18:00

I need to be able to some how link the activity to the cycle field such that an activity that occurs within a cycle time gets that number. so for example 9690, Activity 2 would have a cycl number 11.

Ive tried using INDEX / MATCH array formula but so far have not had any success.

Any help would be greatly appreciated!

Thanks in Advance.Example.PNG

]]>Wondering if i could get your hand with a problem, ive been racking my brain trying to work out how to solve this.

I have one table of activity with a location and start time / date and another table of location and cycle locations, number and end time (Cycle n+1 start time = cycle n = end time)

Location Activity Start End Cycl Number

9690 1 25/07/2017 10:00 25/07/2017 13:30 ?

9465 1 25/07/2017 10:00 25/07/2017 12:00

9690 2 25/07/2017 13:30 25/07/2017 18:00

9690 3 25/07/2017 18:00 26/07/2017 1:00

9465 2 25/07/2017 12:00 25/07/2017 19:00

Location Cycle Number End Time

9690 10 25/07/2017 13:00

9690 11 25/07/2017 19:00

9465 8 25/07/2017 11:00

9465 9 25/07/2017 18:00

I need to be able to some how link the activity to the cycle field such that an activity that occurs within a cycle time gets that number. so for example 9690, Activity 2 would have a cycl number 11.

Ive tried using INDEX / MATCH array formula but so far have not had any success.

Any help would be greatly appreciated!

Thanks in Advance.Example.PNG

Our company sends report twice daily if turn around time is in the Red or the Green. The attached sheet shows the status at the AM time, and the PM Time. I need a formula to count how many days in a row the department was in Green status, but only using the PM date line.

Hello,

I want to find the same values in two sheets. I have two sets of numbers and I want to find a particular value in the second sheet. Then i need to assign the value from a different column. I use VLOOKUP function (VLOOKUP(H2;SHEET1!$A:$H;4;0) and I have an NA error.

I have used NUMBERVALUE function to convert these strings into numbers but it didn't help.

Any idea how can i get rid of this error?

Data looks like this:

SHEET2

Name Partner NUMBER

ZZZ P1 12345

YYY P2 14346

SHEET1

Name NUMBER Date ID

ZZZ 12346 2017-04-01 56

CCC 22347 2017-04-01 87

]]>I want to find the same values in two sheets. I have two sets of numbers and I want to find a particular value in the second sheet. Then i need to assign the value from a different column. I use VLOOKUP function (VLOOKUP(H2;SHEET1!$A:$H;4;0) and I have an NA error.

I have used NUMBERVALUE function to convert these strings into numbers but it didn't help.

Any idea how can i get rid of this error?

Data looks like this:

SHEET2

Name Partner NUMBER

ZZZ P1 12345

YYY P2 14346

SHEET1

Name NUMBER Date ID

ZZZ 12346 2017-04-01 56

CCC 22347 2017-04-01 87

Hi, I am new here. Thank you for taking the time to read and propose a solution.

I have large set of data (thousands of rows) that contains a column with dates and a column with sales data. I need to set up multiple tables that show sales data from date ranges, each table centered around a specified date. So the date at the top of each table determines the sales data that goes into one cell in the table, and all of the other cells in that same table should pull sales data from the sales just before that date to the sales just after that date. For example, if the date in the table says "January 20, 2017," that table should populate with the sales data from the three sales before January 20th to the three sales after January 20th.

I know how to use VLOOKUP to pull the sales data for January 20th, but what I don't know how to do is pull the data from (for example) three sales before that (three rows above it) down to three sales days after that date. On some dates there were no sales, so the dates are not always contiguous; which means I cannot simply do a VLOOKUP to pull in data from January 19th, since there may not have been any sales on that day.

I also know how to use OFFSET to pull data 3 rows up from a specified cell. But I cannot find any way to combine the functions of VLOOKUP and OFFSET to look up the data for January 20th and then look 3 rows above it.

Here is an example of what the data might look like and the table that I want to create from selected data within the data set.

Workbook5.jpg

So I should get a table with the Units automatically filled in with 52, 59, 62, 44, 47, 53, and 51.

]]>I have large set of data (thousands of rows) that contains a column with dates and a column with sales data. I need to set up multiple tables that show sales data from date ranges, each table centered around a specified date. So the date at the top of each table determines the sales data that goes into one cell in the table, and all of the other cells in that same table should pull sales data from the sales just before that date to the sales just after that date. For example, if the date in the table says "January 20, 2017," that table should populate with the sales data from the three sales before January 20th to the three sales after January 20th.

I know how to use VLOOKUP to pull the sales data for January 20th, but what I don't know how to do is pull the data from (for example) three sales before that (three rows above it) down to three sales days after that date. On some dates there were no sales, so the dates are not always contiguous; which means I cannot simply do a VLOOKUP to pull in data from January 19th, since there may not have been any sales on that day.

I also know how to use OFFSET to pull data 3 rows up from a specified cell. But I cannot find any way to combine the functions of VLOOKUP and OFFSET to look up the data for January 20th and then look 3 rows above it.

Here is an example of what the data might look like and the table that I want to create from selected data within the data set.

Workbook5.jpg

So I should get a table with the Units automatically filled in with 52, 59, 62, 44, 47, 53, and 51.

Hello everyone,

I was hoping someone could help me out by finding a formula that will determine what I need to set my selling price to on Amazon.com. My desired margin is to make at least 25.00%, my cost of the item is $5.60 and I have a 16.85% fee on the item selling price.

Any help would be greatly appreciated!

Thanks,

Courtney

]]>I was hoping someone could help me out by finding a formula that will determine what I need to set my selling price to on Amazon.com. My desired margin is to make at least 25.00%, my cost of the item is $5.60 and I have a 16.85% fee on the item selling price.

Any help would be greatly appreciated!

Thanks,

Courtney

How do i paste information from one sheet to another even tho it may appear a few times but doing so only once? see example book

I have a worksheet that I that I want a total of column in another worksheet =SUMIF('LAD 1099 Smartlist'!A:A,'W9-1099'!A9,'LAD 1099 Smartlist'!J:J) which I was able to get the only thing I am having trouble with is I want it to only take the sum of the column if it is in that month of the column name so I want it to depend on another column in that worksheet (Smartlist) column H which the date is formatted like 1/25/2017. Column H is in the Smartlist worksheet. So I believe I need to use SUMIFS but everything I have tried is not working.

I want to Concatenate a series of choices the user will make from six cells containing lists.

So I added the formula: =J5&"; "&J6&"; "&J7&"; "&J8&"; "&J9&"; "&J10

I added the semicolon and space so each choice would be separated.

Ex. Item A; Item B; Item D; and so on.

However the user may only need to one or two items. So the Concatenated cell would look like:

Item A; Item B; ; ; ;

How can I remove the extra semicolons and spaces at the end?

I would like to avoid a Macro if possible...

Thanks in advance.

]]>So I added the formula: =J5&"; "&J6&"; "&J7&"; "&J8&"; "&J9&"; "&J10

I added the semicolon and space so each choice would be separated.

Ex. Item A; Item B; Item D; and so on.

However the user may only need to one or two items. So the Concatenated cell would look like:

Item A; Item B; ; ; ;

How can I remove the extra semicolons and spaces at the end?

I would like to avoid a Macro if possible...

Thanks in advance.

Hi all, for formatting purposes I'm hoping to have the value for cell A1 and cell B1 to be stacked on top of each other in cell C1 - anybody have any luck with something like this?

Thanks!!

Scott

]]>Thanks!!

Scott

I feel like this should be easy, but I can't wrap my head around it.

A customer makes a payment of $14,752.40, which includes an interest payment of 1.5%. How would I write a formula that shows how much of this payment is principal and how much is interest?

]]>A customer makes a payment of $14,752.40, which includes an interest payment of 1.5%. How would I write a formula that shows how much of this payment is principal and how much is interest?

I have this formula:

=SUMPRODUCT(--ISNUMBER(MATCH($AR$5:$BC$5,C$271,0)*MATCH($JR$6:$JR$143,$A273,0)*MATCH($JQ$6:$JQ$143,__$A$269__,0)),$AR$6:$BC$143)

Cell $A$269 just has the number 1 in it.

But I want to change $A$269 in the formula to just be equal to the number instead, so that I don't have to reference a cell there.

I tried:

=SUMPRODUCT(--ISNUMBER(MATCH($AR$5:$BC$5,C$271,0)*MATCH($JR$6:$JR$143,$A273,0)*MATCH($JQ$6:$JQ$143,__1__,0)),$AR$6:$BC$143)

and

=SUMPRODUCT(--ISNUMBER(MATCH($AR$5:$BC$5,C$271,0)*MATCH($JR$6:$JR$143,$A273,0)*MATCH($JQ$6:$JQ$143,__"1"__,0)),$AR$6:$BC$143)

But neither option works...what's going on here?

Thanks!

]]>=SUMPRODUCT(--ISNUMBER(MATCH($AR$5:$BC$5,C$271,0)*MATCH($JR$6:$JR$143,$A273,0)*MATCH($JQ$6:$JQ$143,

Cell $A$269 just has the number 1 in it.

But I want to change $A$269 in the formula to just be equal to the number instead, so that I don't have to reference a cell there.

I tried:

=SUMPRODUCT(--ISNUMBER(MATCH($AR$5:$BC$5,C$271,0)*MATCH($JR$6:$JR$143,$A273,0)*MATCH($JQ$6:$JQ$143,

and

=SUMPRODUCT(--ISNUMBER(MATCH($AR$5:$BC$5,C$271,0)*MATCH($JR$6:$JR$143,$A273,0)*MATCH($JQ$6:$JQ$143,

But neither option works...what's going on here?

Thanks!

I am trying to create a formula that will read a column where a date is entered. I only want it to count the item if there is a date in the field. I don't know how to indicated not null or <>"" in a CountIfs statement. Is this even doable?

Any help is appreciated.

]]>Any help is appreciated.

Working on a spread sheet for a golf tournament and I want to track hole in ones, eagles, birdies, pars, bogies, and double bogies. Just started messing around with the formula and was going to use "0" as par, "+1" for bogie etc. and then use the count function.

How do I write it to identify if a cells value is 1 less/greater than a cells value (instead of just <F3 or >F3)? Or can someone suggest a better route all together?

=IF(F5=F3,"0",IF(F5<F3,"-1",IF(F5>F3,"+1","")))

]]>How do I write it to identify if a cells value is 1 less/greater than a cells value (instead of just <F3 or >F3)? Or can someone suggest a better route all together?

=IF(F5=F3,"0",IF(F5<F3,"-1",IF(F5>F3,"+1","")))

Hi there,

I have managed to sort some data according to non-alphabetical criteria. Specifically, I have sorted a range of data for children's attainment levels where 4E (year 4 emerging) comes before 4D (year 4 developing), which comes before 4S (year 4 secure), etc. So I have used 'Custom List' to insert the levels, like this: 3E, 3D, 3S, 4E, 4D, 4S.

Is there a way to use the same principle in conditional formatting? For example, if column B has data for each child for July and column C has data for September, I'd like Excel to highlight in column C the levels that have gone down (e.g. from 3S to 3D), the ones that have stayed the same, and the ones that have improved (e.g. from 3S to 4E).

Thank you in advance.

All the best,

David

]]>I have managed to sort some data according to non-alphabetical criteria. Specifically, I have sorted a range of data for children's attainment levels where 4E (year 4 emerging) comes before 4D (year 4 developing), which comes before 4S (year 4 secure), etc. So I have used 'Custom List' to insert the levels, like this: 3E, 3D, 3S, 4E, 4D, 4S.

Is there a way to use the same principle in conditional formatting? For example, if column B has data for each child for July and column C has data for September, I'd like Excel to highlight in column C the levels that have gone down (e.g. from 3S to 3D), the ones that have stayed the same, and the ones that have improved (e.g. from 3S to 4E).

Thank you in advance.

All the best,

David

bare with me, this a complex one!

I have 3 columns - 'Location', 'Date In' & 'Date Out'

Is there any way to highlight two rows, if the two dates between the 'date in' & 'date out' overlap for the same location in another row?

example attached: the dates given for paris both overlap... Newyork is there twice too, but there is a good few months between the durations...

tester.JPG

any help would be MUCH APPRECIATED, i'm going out on a limb and saying this is a tricky one...

]]>I have 3 columns - 'Location', 'Date In' & 'Date Out'

Is there any way to highlight two rows, if the two dates between the 'date in' & 'date out' overlap for the same location in another row?

example attached: the dates given for paris both overlap... Newyork is there twice too, but there is a good few months between the durations...

tester.JPG

any help would be MUCH APPRECIATED, i'm going out on a limb and saying this is a tricky one...