Dear Sir

How can i turn the "Sources data" into black when the data in cell match the "range of cell"

See attached

it used =MATCH(A8,$A$8:$D$18,0) but invain , if only single column, it is ok

What i want to do is , when i select the data whichis the same as in A8:D:, then turn color so that we will not select anymore

appreciated any help

How can i turn the "Sources data" into black when the data in cell match the "range of cell"

See attached

it used =MATCH(A8,$A$8:$D$18,0) but invain , if only single column, it is ok

What i want to do is , when i select the data whichis the same as in A8:D:, then turn color so that we will not select anymore

appreciated any help

I need help with a formula.

I have a worksheet with budget and the months going across the columns.

I want to do a sumifs formula where if I select a specified department and month, the sumif will work select the appropriate column and sum the proper values.

see attached.

Thanks in advance!

p.s. it doesn't have to be sumif if you know of a better formula please let me know.

I have a worksheet with budget and the months going across the columns.

I want to do a sumifs formula where if I select a specified department and month, the sumif will work select the appropriate column and sum the proper values.

see attached.

Thanks in advance!

p.s. it doesn't have to be sumif if you know of a better formula please let me know.

Hi

I would appreciate some help to set up a type Lookup formula.

The document contains prices for pumps parts. Column A has the pump model, Column B has the unique part codes (each pump model has a list of parts that fit into it) . In Row 2 you have the different regions the prices come from, and below that in Row 3 you have the customers names. Each Column under the customer name has the price they pay for the specific parts

I need a formula that would allow us to search by part, or by pump model (showing all relevant part associated with that pump model) and show next to the result the lowest price in the data for that part, the highest as well as the average. We would also like to be able to select by which region it should be searched.

I am not sure if you can have a Vlookup combined with an average, highest and lowest formula.

Please help

Thanks

Kind Regards

Gerhard

]]>I would appreciate some help to set up a type Lookup formula.

The document contains prices for pumps parts. Column A has the pump model, Column B has the unique part codes (each pump model has a list of parts that fit into it) . In Row 2 you have the different regions the prices come from, and below that in Row 3 you have the customers names. Each Column under the customer name has the price they pay for the specific parts

I need a formula that would allow us to search by part, or by pump model (showing all relevant part associated with that pump model) and show next to the result the lowest price in the data for that part, the highest as well as the average. We would also like to be able to select by which region it should be searched.

I am not sure if you can have a Vlookup combined with an average, highest and lowest formula.

Please help

Thanks

Kind Regards

Gerhard

Hi All,

Just cannot figure out how to create a formula based on the following criteria

Criteria 1

Match $B2 with Data!$A$2:$A$175

Then Criteria 2

Match a range ---- (Data!$E$2:$E$175=">=11"),--(Data!$E$2:$E$175="<=20")

Then provide sum of (Data!$B$2:$D$175)

I am using

=SUMPRODUCT(----(Data!$A$2:$A$175=$B2),--(Data!$E$2:$E$175=">=11"),--(Data!$E$2:$E$175="<=20"),Data!$B$2:$D$175)

but getting #value, and its because, well, I don't know!!! .. not to good with Excel as it is!

Can someone help me please?

Cheers

]]>Just cannot figure out how to create a formula based on the following criteria

Criteria 1

Match $B2 with Data!$A$2:$A$175

Then Criteria 2

Match a range ---- (Data!$E$2:$E$175=">=11"),--(Data!$E$2:$E$175="<=20")

Then provide sum of (Data!$B$2:$D$175)

I am using

=SUMPRODUCT(----(Data!$A$2:$A$175=$B2),--(Data!$E$2:$E$175=">=11"),--(Data!$E$2:$E$175="<=20"),Data!$B$2:$D$175)

but getting #value, and its because, well, I don't know!!! .. not to good with Excel as it is!

Can someone help me please?

Cheers

Hi everyone! I have overthought this formula so much that I am completely stuck. This is the problem I'm trying to calculate. If you have any ideas, I would love to hear them.

Volunteers are recruited at .4 volunteers per month starting 1/1/2018.

They stay on with the company for 10 months and then roll off.

Recruitment ends 5/12/2019

The total project ends 3/10/2020 (and all volunteers should be gone at this time)

I need to determine how many volunteers will be with us monthly over the entire course of the project.

I will be graphing this data to present and I honestly have been working on it for hours and cannot manage to get the formula to be correct.

]]>Volunteers are recruited at .4 volunteers per month starting 1/1/2018.

They stay on with the company for 10 months and then roll off.

Recruitment ends 5/12/2019

The total project ends 3/10/2020 (and all volunteers should be gone at this time)

I need to determine how many volunteers will be with us monthly over the entire course of the project.

I will be graphing this data to present and I honestly have been working on it for hours and cannot manage to get the formula to be correct.

Hi I have an excel sumif formula in attached file called linked book1. The range is located in another external excel book called linked file 2. However the cell shows an error value whenever linked file 2 book is closed. Is there a way to update the formula not to show the error value.

Hello,

Is there a formula to convert multiple rows into a single row?

I am attaching a screenshot that exemplifies what I am trying to do.

Thank you!

Screen Shot 2017-05-24 at 9.46.00 PM.png

]]>Is there a formula to convert multiple rows into a single row?

I am attaching a screenshot that exemplifies what I am trying to do.

Thank you!

Screen Shot 2017-05-24 at 9.46.00 PM.png

So i am essentially trying to make a grid that automatically calculates how many days have passed since my last comment on the category. I've attached a sample. Ideally I would continue to add cells with more recent dates to the bottom and the number of days elapsed would automatically calculate. The problem I am having is that this will eventually go into a grid with many of these categories all one after the other, so there are blank comment lines in each category box. Is there a way I can nest if functions to search the date column and find the last cell with a value in it, then calculate the difference between that date and today's date?

Currently to calculate the difference I have: =(-G5)+C2 but that means I would have to change the formula every time I wanted to add a more recent comment.

Would something like this work or am I totally off base?

IF(G8=0, (G8-1 & (IF(G8-1=0, (G8-2 & (IF(G8-2=0, "Error", (=(-(G8-2))+C2))), (=(-(G8-1))+C2)), ((-G8)+2)))))

*I can make it so there will be no more than 2 blank date rows in a category.

Currently to calculate the difference I have: =(-G5)+C2 but that means I would have to change the formula every time I wanted to add a more recent comment.

Would something like this work or am I totally off base?

IF(G8=0, (G8-1 & (IF(G8-1=0, (G8-2 & (IF(G8-2=0, "Error", (=(-(G8-2))+C2))), (=(-(G8-1))+C2)), ((-G8)+2)))))

*I can make it so there will be no more than 2 blank date rows in a category.

Hi All,

Have attached a slimmed down version of a spreadsheet I use to log data. Looking for a formula that will auto capture flight numbers from the Trip sheet and populate the TLH and N45DJ sheets for those flights that having the matching criteria.

Criteria for the TLH sheet is cell TLH!A1 matching with TripsL:L.

Criteria for the N45DJ sheet is cell N45DJ! matching with Trips!B:B criteria.

I have been trying various formula's and the best I can get is an Index Match but, having a very difficult time using the formula with a criteria.

Thank you in advance.

Have attached a slimmed down version of a spreadsheet I use to log data. Looking for a formula that will auto capture flight numbers from the Trip sheet and populate the TLH and N45DJ sheets for those flights that having the matching criteria.

Criteria for the TLH sheet is cell TLH!A1 matching with TripsL:L.

Criteria for the N45DJ sheet is cell N45DJ! matching with Trips!B:B criteria.

I have been trying various formula's and the best I can get is an Index Match but, having a very difficult time using the formula with a criteria.

Thank you in advance.

Hey everyone,

I am trying to make data where I can graph vehicle sales along with recession periods. In the attached excel I have US GDP by which 2 consecutive quarters is by definition a recession. On the left in column F and G I have vehicle sales. The problem is vehicle sales is monthly, and GDP is quarterly. I want to be able to plot with the vehicle sales recession periods, and then put it into the graph. If someone can help me place a 1 or -1 next to the vehicle sale date. -1 when that period is a recession and 1 when it isn't.

Hope that was clear, and thanks!

I am trying to make data where I can graph vehicle sales along with recession periods. In the attached excel I have US GDP by which 2 consecutive quarters is by definition a recession. On the left in column F and G I have vehicle sales. The problem is vehicle sales is monthly, and GDP is quarterly. I want to be able to plot with the vehicle sales recession periods, and then put it into the graph. If someone can help me place a 1 or -1 next to the vehicle sale date. -1 when that period is a recession and 1 when it isn't.

Hope that was clear, and thanks!

Hello,

I'm attempting to make a formula that can total up a column, but half the criteria are coming from slicers. I'm using slicers so that my internal customers can select one or multiple values to change the outcome of a formula.

The table I'm pulling/summing my data from is called DL.

I have a slicer controlling a pivot for Site. This is in column A. Right now, we only have a handful of sites, but for future proofing, I've created the named rang A2:A40 = Sites.

I have a slicer controlling a pivot for Quarter. This is in column B. Same thing above, I've created a named range B2:B40 = Quarter

In the past, I've had help with multiple arrays that have the syntax below. The key was to use commas in one and semi colons in the next:

Now I'm trying to what I thought was the same thing, except my criteria aren't entered manually (AL,FL,CA, etc) they come from a named array (that happens to contain a pivot connected to a slicer). I tried the following:

I assume the issue is that I can't designate them as different arrays (or however you define what is happening) with commas and semi colons like I could before. It does return a result, but it appears to be a first pick type of thing.

Also if SUM(SUMIFS()) isn't the way to go, I'm open to suggestions. I thought about throwing this all into the pivot and just referencing the grand total, but the Site and Quarter are from different tables/datasets. There are Sites in the same table as the Quarter dataset, but not all of the Sites. That Sites slicer is also being used in other formulas in a similar manner, so I kind of need all the sites from a specific table.

]]>I'm attempting to make a formula that can total up a column, but half the criteria are coming from slicers. I'm using slicers so that my internal customers can select one or multiple values to change the outcome of a formula.

The table I'm pulling/summing my data from is called DL.

I have a slicer controlling a pivot for Site. This is in column A. Right now, we only have a handful of sites, but for future proofing, I've created the named rang A2:A40 = Sites.

I have a slicer controlling a pivot for Quarter. This is in column B. Same thing above, I've created a named range B2:B40 = Quarter

In the past, I've had help with multiple arrays that have the syntax below. The key was to use commas in one and semi colons in the next:

Code:

`={SUM(SUMIFS(DL[Revenue],DL[Site],{"AL","FL","CA"},DL[Quarter],{"2016 Q2";"2016 Q3";"2016 Q4";"2017 Q1"}))}`

Code:

`={SUM(SUMIFS(DL[Revenue],DL[Site],Sites,DL[Quarter],Quarter))}`

Also if SUM(SUMIFS()) isn't the way to go, I'm open to suggestions. I thought about throwing this all into the pivot and just referencing the grand total, but the Site and Quarter are from different tables/datasets. There are Sites in the same table as the Quarter dataset, but not all of the Sites. That Sites slicer is also being used in other formulas in a similar manner, so I kind of need all the sites from a specific table.

Attached is an excel test workbook, along with expected results. Basically I want some formula to pull from column A the account number, and another formula to add the name referenced to that specific account number. the third formula i'm looking for, is to count the number of times a 1 is present in column D for that account number. (it will only be a 1 or a 0 in column D)

Much Appreciated

Much Appreciated

Hi all,

I've nearly completed an epic learning project but stuck at the final Furlong.

Im working with Horse Racing Data Imported from the Web. I have rearranged and Custom Filtered the best i could and finally have the Filtered minimal amount of Data that i can possibly achieve to help with my final stage.

I need help with x3 Formulas to be Filled down in my tables.

The Problem;

I have Two Tables, One for UK Races and the other for IRE races. For each of these i already have the 'Course Name' & 'Time' of race. (imported from a Custom Power Query).

I then have 3 more Fields which i need Formulas to return the relevant data from the Filtered Web Page Data. These are, 'Distance, Class, Prize Money' , 'Runners' & 'Non-Runners'.

The problem i have is that although the Data is in Time order, I have to separate the Data into the Tables from UK & IRE Races. The good news is that the data i have Filtered is very consistent in its format.

I have attached a stripped back Worksheets which includes a UK & IRE (underneath UK) Table with the Fields 'Distance, Class, Prize Money' , 'Runners' & 'Non-Runners' left blank for the problem Formulas. Next to this i have included an example Table demonstrating for this example the correct Information that should be returned by the Formulas.

I have Color coded the relevant Cells that relate to each other if that helps.

As you can see from my attachment, the 'Distance, Class, Prize Money' Field is self explanatory but just needs the relevant information for the correct race to be placed into the Tables. The 'Runners' Field needs to again import the relevant Data which is eg, "16 Ran" but just return the number, so "16" into the relevant table position. Finally for the 'Non-Runners' Field i need the formula to not only import the correct data into the Table in the Correct position but return a value based on the number of commas in the Cell below "NR:" from the Web Data.

For example,

NR:

Aisteoir (passport irregularities), Ardglen Flyer (temperature)

i would need to return the Value of 2, or,

NR:

Lilly Be (unsuitable ground)

i would need to return the value of 1.

The pattern is as follows,

If there are 'Non-Runners' and 0 Commas then return the value of 1,

If there are 'Non-Runners' and 1 Commas then return the value of 2,

If there are 'Non-Runners' and 2 Commas then return the value of 3,

etc....

I know this sounds REALLY complicated but if you do kindly take a look at the attachment you can see its just x3 Formulas possibly INDEX/MATCH that can then be Filled down the Tables.

Finally, the Table Data and Web Data change so the x3 Formulas are required rather than just referencing the corresponding Cells.

I really appreciate any help or advice and please just let me know if you need any more information if you fancy having a crack at helping.

Many Thanks guys!

(ps, Scroll down for the IRE Tables)

I've nearly completed an epic learning project but stuck at the final Furlong.

Im working with Horse Racing Data Imported from the Web. I have rearranged and Custom Filtered the best i could and finally have the Filtered minimal amount of Data that i can possibly achieve to help with my final stage.

I need help with x3 Formulas to be Filled down in my tables.

The Problem;

I have Two Tables, One for UK Races and the other for IRE races. For each of these i already have the 'Course Name' & 'Time' of race. (imported from a Custom Power Query).

I then have 3 more Fields which i need Formulas to return the relevant data from the Filtered Web Page Data. These are, 'Distance, Class, Prize Money' , 'Runners' & 'Non-Runners'.

The problem i have is that although the Data is in Time order, I have to separate the Data into the Tables from UK & IRE Races. The good news is that the data i have Filtered is very consistent in its format.

I have attached a stripped back Worksheets which includes a UK & IRE (underneath UK) Table with the Fields 'Distance, Class, Prize Money' , 'Runners' & 'Non-Runners' left blank for the problem Formulas. Next to this i have included an example Table demonstrating for this example the correct Information that should be returned by the Formulas.

I have Color coded the relevant Cells that relate to each other if that helps.

As you can see from my attachment, the 'Distance, Class, Prize Money' Field is self explanatory but just needs the relevant information for the correct race to be placed into the Tables. The 'Runners' Field needs to again import the relevant Data which is eg, "16 Ran" but just return the number, so "16" into the relevant table position. Finally for the 'Non-Runners' Field i need the formula to not only import the correct data into the Table in the Correct position but return a value based on the number of commas in the Cell below "NR:" from the Web Data.

For example,

NR:

Aisteoir (passport irregularities), Ardglen Flyer (temperature)

i would need to return the Value of 2, or,

NR:

Lilly Be (unsuitable ground)

i would need to return the value of 1.

The pattern is as follows,

If there are 'Non-Runners' and 0 Commas then return the value of 1,

If there are 'Non-Runners' and 1 Commas then return the value of 2,

If there are 'Non-Runners' and 2 Commas then return the value of 3,

etc....

I know this sounds REALLY complicated but if you do kindly take a look at the attachment you can see its just x3 Formulas possibly INDEX/MATCH that can then be Filled down the Tables.

Finally, the Table Data and Web Data change so the x3 Formulas are required rather than just referencing the corresponding Cells.

I really appreciate any help or advice and please just let me know if you need any more information if you fancy having a crack at helping.

Many Thanks guys!

(ps, Scroll down for the IRE Tables)

I have this on A1

BLUETTE

DECTETS

DETECTS

LETTUCE

]]>BLUETTE

DECTETS

DETECTS

LETTUCE

Hi,

I have created a sumproduct formula in the attached but wondered if there was some way to amend it so it could amend a chosen category.

In the example in I10 I would like to pull back all the data for AUS (column B) but if column C is 'Cats' I would like to exclude it - I am not sure if this is possible.

I would appreciate any guidance on the formula my current formula is =SUMPRODUCT(--($B$17:$B$276=$H8),I$17:I$276)

Thanks

I have created a sumproduct formula in the attached but wondered if there was some way to amend it so it could amend a chosen category.

In the example in I10 I would like to pull back all the data for AUS (column B) but if column C is 'Cats' I would like to exclude it - I am not sure if this is possible.

I would appreciate any guidance on the formula my current formula is =SUMPRODUCT(--($B$17:$B$276=$H8),I$17:I$276)

Thanks