Hi:

I am trying to solve a problem in Excel associated with a Well Inventory / Drilling Schedule. Basically there are two Type Curves (TC1, & TC2). There is an inventory of wells associated with each Type Curve. That inventory within each Type Curve can be categorized by Reserve Categories (PDNP, PUD, PRB). Next there is an assumed priority of drilling this inventory within each Type Curve such as: First: PDNP reserve categories, Next: PUD reserve categories and Finally: PRB reserve categories. The priority of reserve drilling is the same across all type curves (i.e. 1. PDNP wells, 2. PUD wells, 3. PRB wells.)

Then there is an assumed Drill Schedule associated with drilling these inventories across time by Type Curve. I need to construct a table (shown in the attached Excel file) which breaks out the inventory of wells by month, reserve, category and type curve. I have already created the matrix of when the wells are drilled. However I now need to lookup what Reserve Category and Type Curve is associated with each drilled well based on the current inventory available in that Type Curve and based on the priority of drilling each Reserve category.

All inputs are colored Blue. The cells I need to calculate are filled in Yellow color and needs to be dynamic as the well inventory and drill schedule may change. Please ask any questions for clarification. From my perspective, this seems like a rather complex excel formula, and looking forward to the potential solutions for this. Ideally I would like the equation to be calculated within the Yellow cells completely, but if helper cells are needed that definitely makes sense.

Thanks for your help on this.

I am trying to solve a problem in Excel associated with a Well Inventory / Drilling Schedule. Basically there are two Type Curves (TC1, & TC2). There is an inventory of wells associated with each Type Curve. That inventory within each Type Curve can be categorized by Reserve Categories (PDNP, PUD, PRB). Next there is an assumed priority of drilling this inventory within each Type Curve such as: First: PDNP reserve categories, Next: PUD reserve categories and Finally: PRB reserve categories. The priority of reserve drilling is the same across all type curves (i.e. 1. PDNP wells, 2. PUD wells, 3. PRB wells.)

Then there is an assumed Drill Schedule associated with drilling these inventories across time by Type Curve. I need to construct a table (shown in the attached Excel file) which breaks out the inventory of wells by month, reserve, category and type curve. I have already created the matrix of when the wells are drilled. However I now need to lookup what Reserve Category and Type Curve is associated with each drilled well based on the current inventory available in that Type Curve and based on the priority of drilling each Reserve category.

All inputs are colored Blue. The cells I need to calculate are filled in Yellow color and needs to be dynamic as the well inventory and drill schedule may change. Please ask any questions for clarification. From my perspective, this seems like a rather complex excel formula, and looking forward to the potential solutions for this. Ideally I would like the equation to be calculated within the Yellow cells completely, but if helper cells are needed that definitely makes sense.

Thanks for your help on this.

Hello,

I have basic skills with Excel...I am using the Employee Absence Schedule template. I need to modify the formulas so that the following is adjusted:

Add a "days picked up" category that will add days to the total in column AH. Also, I would like the "sick days" not to effect the numbers in the total AH column but to be totaled in an additional column. Any help appreciated!

Thank you,

Amara

]]>I have basic skills with Excel...I am using the Employee Absence Schedule template. I need to modify the formulas so that the following is adjusted:

Add a "days picked up" category that will add days to the total in column AH. Also, I would like the "sick days" not to effect the numbers in the total AH column but to be totaled in an additional column. Any help appreciated!

Thank you,

Amara

Hi guys,

I hope someone can help.

I am trying to add formula calculations to a spreadsheet based on different commission rates- I have a dropdown list for 'operators' and each operator has a different commission percentage.

So if cell c3 says 'A' then I want it to calculate one formula, if it says 'B' then I want it to calculate a different one, etc etc. Formulas are just percentages of different cells.

Ive tried doing IF formulas but I'm getting in a muddle and can't get it to work, it keeps returning errors.

Can anyone help me???

]]>I hope someone can help.

I am trying to add formula calculations to a spreadsheet based on different commission rates- I have a dropdown list for 'operators' and each operator has a different commission percentage.

So if cell c3 says 'A' then I want it to calculate one formula, if it says 'B' then I want it to calculate a different one, etc etc. Formulas are just percentages of different cells.

Ive tried doing IF formulas but I'm getting in a muddle and can't get it to work, it keeps returning errors.

Can anyone help me???

Hi Everyone, this is my first post.

In Excell 2011 for Mac, it was easy to create a hyperlink in Excel to a specific bookmark in Word. You simply choose the file name in the hyperlink dialog box: example; goalsetting.docx, and then added (number sign) # and then the bookmark (such as; goal1); so the hyperlink formula looked just like this:**goalsetting.docx#goal1**. Super simple and worked great. This however does not work in Excel 2016. I have searched this extensively and there are lots of inquiries (and answers for previous Excel versions), but no one has been able to provide an answer that works for 2016. Any help would be greatly appreciated. Thanks!

]]>In Excell 2011 for Mac, it was easy to create a hyperlink in Excel to a specific bookmark in Word. You simply choose the file name in the hyperlink dialog box: example; goalsetting.docx, and then added (number sign) # and then the bookmark (such as; goal1); so the hyperlink formula looked just like this:

To whom be willing to help. In this problem I am trying to code using formulas (not VBA). Nodes (locations) on which vehicles must stop to refuel based on a 2000 mile journey. Vehicle types with associated maximum range are provided.

Table below assigns a row to each vehicle and range to nodes (cities) are depicted by columns.

Locations can be depicted by penalty value (5 minutes to refuel), or binary yes/no (for refuel location), or interger 1, 0s to id locations.

v/r

Ricrefueling vehicle locations based on range.xlsx

]]>Table below assigns a row to each vehicle and range to nodes (cities) are depicted by columns.

Locations can be depicted by penalty value (5 minutes to refuel), or binary yes/no (for refuel location), or interger 1, 0s to id locations.

v/r

Ricrefueling vehicle locations based on range.xlsx

Need to populate 2 column's in a worksheet with data from another worksheet within the same spreadsheet. Guessing i'd use the vlookup() function but I'm unable to get it to work.

In the attached file, I need Column B & C in the Contract Recap worksheet to populate with the relative information tied by the contract number in the contract list worksheet.

Need this for both the "Contract With" column & "Del Mth" Column.

Appreciate the help.

Thanks in advance

-Manny

In the attached file, I need Column B & C in the Contract Recap worksheet to populate with the relative information tied by the contract number in the contract list worksheet.

Need this for both the "Contract With" column & "Del Mth" Column.

Appreciate the help.

Thanks in advance

-Manny

Hi, I'm trying to build multiple scenarios in excel, using a combination of drop down menus (Created through data validation -> Lists ->Selecting a range of numbers 1-5) and a market model. The list links to a series of values (i.e. 25% market share links to scenario 1, 20% to scenario 2, etc...)

Some of my scenarios have the same value (i.e. 1 and 3 have 25% or 2, 4 have 20%). I'd like to be able to create an if statement that captures this. I tried = if(Reference cell = 4, 20%, 25%) and was able to get the formula to work. However when I tried if(Reference cell = 2 or 4, 20%, 25%) I got an error. Any tips on correcting my mistake?

Thanks!

]]>Some of my scenarios have the same value (i.e. 1 and 3 have 25% or 2, 4 have 20%). I'd like to be able to create an if statement that captures this. I tried = if(Reference cell = 4, 20%, 25%) and was able to get the formula to work. However when I tried if(Reference cell = 2 or 4, 20%, 25%) I got an error. Any tips on correcting my mistake?

Thanks!

Hello,

I created an inventory spread sheet to help keep track of a parts inventory.

I used the VLOOKUP function on column C and D in the "SOLD AND RECEIVED" tab to look up the special part code for each part "#" column A on the tab "INVENTORY LIST".

In column F "QTY" of tab "SOLD AND RECEIVED" will be the quantity of that part sold or received in.

I used the SUMIF function in column F "MOVEMENT" in the "INVENTORY LIST" tab to calculate any parts movement listed in column F and then a simple function to add the the movement with the original count (Column E) to give me the live count (Column G).

If I use SORT A to Z on column H "LOCATION" The movement / live counts are not correct.

Can someone please help me to keep the counts accurate when I sort by location?

Please let me know if you need more information.

Thank you,

Nikki

I created an inventory spread sheet to help keep track of a parts inventory.

I used the VLOOKUP function on column C and D in the "SOLD AND RECEIVED" tab to look up the special part code for each part "#" column A on the tab "INVENTORY LIST".

In column F "QTY" of tab "SOLD AND RECEIVED" will be the quantity of that part sold or received in.

I used the SUMIF function in column F "MOVEMENT" in the "INVENTORY LIST" tab to calculate any parts movement listed in column F and then a simple function to add the the movement with the original count (Column E) to give me the live count (Column G).

If I use SORT A to Z on column H "LOCATION" The movement / live counts are not correct.

Can someone please help me to keep the counts accurate when I sort by location?

Please let me know if you need more information.

Thank you,

Nikki

Hello,

I am obviously new here at the forums. I just want to ask if there is any way to determine if a time is between a given time range. I have searched for solutions but has never helped me yet.

e.g.

Start Time: 22:00:00

End Time: 7:00:00

Given Time: 3:43:00

Thanks in advance!

]]>I am obviously new here at the forums. I just want to ask if there is any way to determine if a time is between a given time range. I have searched for solutions but has never helped me yet.

e.g.

Start Time: 22:00:00

End Time: 7:00:00

Given Time: 3:43:00

Thanks in advance!

Hello friends,

I use

to find the value in the last cell of column B.

Instead of manually entering data in the column I want to use a formula to retrieve it,

,

so I copy the formula down 100 rows. The cell only displays a BLANK CELL until it finds data.

<the formula in the following row would look at $G$7 and so on. I'm having to manually update each row number because they do not increase as I drag them down because they are surrounded by quotes>

The Formula-A above no longer works because it sees the Formula-B in the cells and includes them in the search for the last, so it lists the BLANK from cell B100.

If it's possible, how can I make FORMULA-A only consider the cells with a numerical value and ignore the BLANKS (which are formulas)? The cells with numerical values are consecutive.

Thanks!

]]>I use

Code:

`FORMULA-A: OFFSET($B$1,COUNTA($B:$B)-1,0)`

Instead of manually entering data in the column I want to use a formula to retrieve it,

Code:

`FORMULA-B: IF(INDIRECT("[DATA.xlsm]20"&RIGHT($A13,2)&"!$G$6")="","",INDIRECT("DATA.xlsm]20"&RIGHT($A13,2)&"!$G$6")`

so I copy the formula down 100 rows. The cell only displays a BLANK CELL until it finds data.

<the formula in the following row would look at $G$7 and so on. I'm having to manually update each row number because they do not increase as I drag them down because they are surrounded by quotes>

The Formula-A above no longer works because it sees the Formula-B in the cells and includes them in the search for the last, so it lists the BLANK from cell B100.

If it's possible, how can I make FORMULA-A only consider the cells with a numerical value and ignore the BLANKS (which are formulas)? The cells with numerical values are consecutive.

Thanks!

Hi Friends,

I need help creating a formula for a work problem. I work for a clothing company as a analyst and need to set up a formula that tells me when inventory in a store for a certain article of clothing (An article is a model in a certain colour) is sitting below 3 units. ideally, the formula (or a seperate one) could also flag when there isn't 2 out of 3 core sizes remaining per article (the core sizes are S, M, and L)

So for example, from the attached sheet. I would want the formula to flag the "Phase SL bottom men's black" because it only has 2 units in store. I would also want to flag the "phase SL boxer men's black" because even though it has 8 units it doesn't have any of the core sizes.

If anyone is able to help me solve this, I will be forever grateful! I've been trying different things and haven't figured it out for months now.

I've attached a small example of the inventory but they usually get to be over 4000 lines or more. Here's a rough if you can't see the attachment:

Model_Color SizeCode Sum of Quantity On Hand

Phase AR Bottom Women's Black L 2

Phase AR Bottom Women's Black M 1

Phase AR Bottom Women's Black S 3

Phase AR Bottom Women's Black XL 1

Phase AR Bottom Women's Black XS 2

Satoro AR Bottom Women's Black L 3

Satoro AR Bottom Women's Black M 1

Satoro AR Bottom Women's Black S 1

Satoro AR Bottom Women's Black XL 1

Phase SL Bottom Men's Black L 1

Phase SL Bottom Men's Black M 1

Phase SL Boxer Men's Black XL 4

Phase SL Boxer Men's Black XS 3

Phase SL Boxer Men's Black XXL 1

I need help creating a formula for a work problem. I work for a clothing company as a analyst and need to set up a formula that tells me when inventory in a store for a certain article of clothing (An article is a model in a certain colour) is sitting below 3 units. ideally, the formula (or a seperate one) could also flag when there isn't 2 out of 3 core sizes remaining per article (the core sizes are S, M, and L)

So for example, from the attached sheet. I would want the formula to flag the "Phase SL bottom men's black" because it only has 2 units in store. I would also want to flag the "phase SL boxer men's black" because even though it has 8 units it doesn't have any of the core sizes.

If anyone is able to help me solve this, I will be forever grateful! I've been trying different things and haven't figured it out for months now.

I've attached a small example of the inventory but they usually get to be over 4000 lines or more. Here's a rough if you can't see the attachment:

Model_Color SizeCode Sum of Quantity On Hand

Phase AR Bottom Women's Black L 2

Phase AR Bottom Women's Black M 1

Phase AR Bottom Women's Black S 3

Phase AR Bottom Women's Black XL 1

Phase AR Bottom Women's Black XS 2

Satoro AR Bottom Women's Black L 3

Satoro AR Bottom Women's Black M 1

Satoro AR Bottom Women's Black S 1

Satoro AR Bottom Women's Black XL 1

Phase SL Bottom Men's Black L 1

Phase SL Bottom Men's Black M 1

Phase SL Boxer Men's Black XL 4

Phase SL Boxer Men's Black XS 3

Phase SL Boxer Men's Black XXL 1

How do I condense values in a column so that there are no empty cells between values in the same column and **without** losing the order of the values (as one does when using the sort from largest to smallest value function).

See attached file.

Many thanks,

JackBlack2

See attached file.

Many thanks,

JackBlack2

I have two worksheets. One contains a client name in one column and project name in another column. I need to match both client name and project name on the second worksheet and if a match, then pull in data from a different cell on the match row. This is my current formula but it is not quite working. My list contains 2540 rows to search through for a match.

=IF(AND(EXACT($D2,'WStwo'!$B$2:$B$2540),EXACT($E2,'WStwo'!$C$2:$C$2540)),'WStwo'!$D$2:$D$2540)

Name in Column D and Column E on WSone must match the name exactly in both Column B and Column C on WStwo. Once if finds a match, then I need the data in Column D on WStwo. Once I can get this formula to work, then I will be doing the same to pull in data from other columns on WStwo into WSone as well.

I would think this requires a VLOOKUP formula since I need to search and entire row on another sheet. So a formula within a formula within a formula? Above my pay grade, but I know Excel can do it.

Thanks for any help I can get on this.

]]>=IF(AND(EXACT($D2,'WStwo'!$B$2:$B$2540),EXACT($E2,'WStwo'!$C$2:$C$2540)),'WStwo'!$D$2:$D$2540)

Name in Column D and Column E on WSone must match the name exactly in both Column B and Column C on WStwo. Once if finds a match, then I need the data in Column D on WStwo. Once I can get this formula to work, then I will be doing the same to pull in data from other columns on WStwo into WSone as well.

I would think this requires a VLOOKUP formula since I need to search and entire row on another sheet. So a formula within a formula within a formula? Above my pay grade, but I know Excel can do it.

Thanks for any help I can get on this.

Hi! I know how to use the find function in Excel and I also know how to loop through an array in VBA to see if a certain string is present. However, I'd like to learn how to use an array formula within a worksheet to see if a cell's string value is in a value of an array that may contain those unique strings but are also surrounded by other strings. For instance:

Range A1:A10 is my first array and can be visualized as ("Electricity", "Water", "Sewage", etc...)

Range B1:B10, which are in an arbitrary order that does not correlate with the corresponding cell in column A, may look like ("The bill for Water was...","Last month, my electricity bill was...", etc...).

How would I do something along the lines of {=find(A1,B1:B10,1)}? I am trying to see if each individual element of the first array, range A1:A10, appears in the second array, range B1:B10.

As mentioned, this is easy to do in VBA, however, I am just curious as to how this would be done.

Thanks!

]]>Range A1:A10 is my first array and can be visualized as ("Electricity", "Water", "Sewage", etc...)

Range B1:B10, which are in an arbitrary order that does not correlate with the corresponding cell in column A, may look like ("The bill for Water was...","Last month, my electricity bill was...", etc...).

How would I do something along the lines of {=find(A1,B1:B10,1)}? I am trying to see if each individual element of the first array, range A1:A10, appears in the second array, range B1:B10.

As mentioned, this is easy to do in VBA, however, I am just curious as to how this would be done.

Thanks!

The formula below is returning a 0 (zero) on date formatted column which results in 1/0/00. This is impacting other conditional formatting I have in place. How can I update it to where if it doesn't find anything to return, to simply return a blank vs 0...?

=IFERROR(INDEX(Milestones!$AA$5:$AB$1254,IF(CM$4<>"",AGGREGATE(15,6,(ROW($A$5:$A$1254)-ROW($A$4))/(Milestones!$H$5:$H$1254=$B7)/(Milestones!$Z$5:$Z$1254=CM$4)/(Milestones!$M$5:$M$1254<>"Agile Workflow"),1),AGGREGATE(15,6,(ROW($A$5:$A$1254)-ROW($A$4))/(Milestones!$H$5:$H$1254=$B7)/(Milestones!$Z$5:$Z$1254=CL$4)/(Milestones!$M$5:$M$1254<>"Agile Workflow"),1)),IF(CM$6="target",1,2)),"")

]]>=IFERROR(INDEX(Milestones!$AA$5:$AB$1254,IF(CM$4<>"",AGGREGATE(15,6,(ROW($A$5:$A$1254)-ROW($A$4))/(Milestones!$H$5:$H$1254=$B7)/(Milestones!$Z$5:$Z$1254=CM$4)/(Milestones!$M$5:$M$1254<>"Agile Workflow"),1),AGGREGATE(15,6,(ROW($A$5:$A$1254)-ROW($A$4))/(Milestones!$H$5:$H$1254=$B7)/(Milestones!$Z$5:$Z$1254=CL$4)/(Milestones!$M$5:$M$1254<>"Agile Workflow"),1)),IF(CM$6="target",1,2)),"")