Please Allow me to Explain my Situation:

I work in Car Selling Company.

We normally Have the followings:

Make = Toyota, BMW etc, Model= Corolla X5 etc, & Model Codes (Model Codes are also known as First part of the Chassis or Win Numbers)

I have Situation that under One type of Model Code (NZE121, there are Multiple Models E.g Corolla, Alex, Runs etc) and they all Belong to Toyota. Example Below:

I am unable to attached the file will try, even ready to pay some bucks if can be fixed

]]>I work in Car Selling Company.

We normally Have the followings:

Make = Toyota, BMW etc, Model= Corolla X5 etc, & Model Codes (Model Codes are also known as First part of the Chassis or Win Numbers)

I have Situation that under One type of Model Code (NZE121, there are Multiple Models E.g Corolla, Alex, Runs etc) and they all Belong to Toyota. Example Below:

I am unable to attached the file will try, even ready to pay some bucks if can be fixed

Hope someone can help me wrap my brain around this.

We have a large database of work orders where we need to generate a report that will be used by management to quickly identify areas of concern. The data is exported from our ticketing system and analyzed using Excel. In order to make things easier for management to use the data I wanted to come up with a sheet that would take any exported data and present the results they were looking for.

They want to see a list of WO# that are found on the DATA tab, where techs exist on Report!A:A, and finally reduce the results by only showing the WO# with certain status values identified in the named range "Status_Open".

I was able to get a list of WO# to be identified using the array criteria below, but still need to ensure that we list only list work orders where the Tech's showing on the Report tab.

-TIA!

We have a large database of work orders where we need to generate a report that will be used by management to quickly identify areas of concern. The data is exported from our ticketing system and analyzed using Excel. In order to make things easier for management to use the data I wanted to come up with a sheet that would take any exported data and present the results they were looking for.

They want to see a list of WO# that are found on the DATA tab, where techs exist on Report!A:A, and finally reduce the results by only showing the WO# with certain status values identified in the named range "Status_Open".

I was able to get a list of WO# to be identified using the array criteria below, but still need to ensure that we list only list work orders where the Tech's showing on the Report tab.

Code:

`{=IFERROR(INDEX(nmDATA,SMALL(IF((DATA!C:C=INDEX(nmStatus_Open,1))+(DATA!C:C=INDEX(nmStatus_Open,2))=1,ROW(DATA!A:A)-1),ROW(1:1)),1),"")}`

Hey guys,

This is my first thread on this forum and, besides the basic IF, AND, and OR functions, I'm not very good at using Excel.

Let's say that I have one column full of letters and another full of numbers (respectively): so if I have A in one cell, I have 1 directly next to it to the right. If I have B in one cell, I have 2 directly next to it to the right etc.

I would like a function that would allow me to search a specific range for a specific value and return the value to the right (or the left) of that cell. For instance: if I'm looking for C, it would return 3 (since it's the one directly to the left).

Any ideas of how to do that?

Thanks in advance. :)

]]>This is my first thread on this forum and, besides the basic IF, AND, and OR functions, I'm not very good at using Excel.

Let's say that I have one column full of letters and another full of numbers (respectively): so if I have A in one cell, I have 1 directly next to it to the right. If I have B in one cell, I have 2 directly next to it to the right etc.

I would like a function that would allow me to search a specific range for a specific value and return the value to the right (or the left) of that cell. For instance: if I'm looking for C, it would return 3 (since it's the one directly to the left).

Any ideas of how to do that?

Thanks in advance. :)

hi all i need multiple if in 1 formula

in column O i have a formula =IF(C4,"Live","")

but i also need the cell to return "completed" when the date in e1 is greater than the date in column G

thank you for the help in advance

in column O i have a formula =IF(C4,"Live","")

but i also need the cell to return "completed" when the date in e1 is greater than the date in column G

thank you for the help in advance

Help with IF Statement/Count IF

So I have 2 sheets

Sheet1 = Column A has a list of dates (14/05/2018)

= Column B has a list of names (John Smith)

= Column C has a Start Time (14/05/2018 10:00)

= Column D has a Finish Time (14/05/2018 22:00)

= Column E is the difference between D+C (12:00:00) - (h)hh:mm

Sheet2 = Column A has a list of dates (14/05/2018)

= Column B has a list of names (John Smith)

= Column C has a working shift length (08:00:00) - (h)h:mm

I want to in Sheet1 Column E calculate the difference between D+C but condense the result to not be any longer than Column C in Sheet2.

As the data will have long lists of names and different dates. I need to use the Date and Name in both Sheets to match.

please help

]]>So I have 2 sheets

Sheet1 = Column A has a list of dates (14/05/2018)

= Column B has a list of names (John Smith)

= Column C has a Start Time (14/05/2018 10:00)

= Column D has a Finish Time (14/05/2018 22:00)

= Column E is the difference between D+C (12:00:00) - (h)hh:mm

Sheet2 = Column A has a list of dates (14/05/2018)

= Column B has a list of names (John Smith)

= Column C has a working shift length (08:00:00) - (h)h:mm

I want to in Sheet1 Column E calculate the difference between D+C but condense the result to not be any longer than Column C in Sheet2.

As the data will have long lists of names and different dates. I need to use the Date and Name in both Sheets to match.

please help

My attachment is the data table and the expected results. I am successful at using index match with multiple criteria, but there is often a series of results that match the criteria selction. I want to take all those results (line items) and concatenate them into one string. Help is greatly needed and appreciated

See attachment.....Need formula for cell G2 to do the following: Look in Column A for any "Area 1" aka cell E2, plus in Column B for any "Anthem Blue Cross - Prudent Buyer - Small Group" aka cell F2, then return the results in column C, and separate each of the matching lines with a semi colon ";".

See attachment.....Need formula for cell G2 to do the following: Look in Column A for any "Area 1" aka cell E2, plus in Column B for any "Anthem Blue Cross - Prudent Buyer - Small Group" aka cell F2, then return the results in column C, and separate each of the matching lines with a semi colon ";".

Hello,

I am trying to create a tracker to determine when training will expire by date (training is good for one year). I can pull a mass roster from an internet based site, but I need a way to parse the raw data into a easy to view roster of who has completed training, and when they are coming due for future training.

What I am trying to accomplish is to have my tracker sheet look at the raw data and populate a cell with a color code based on the employee name, training type, and date of training. (black-overdue, red-within 30 days, yellow-31 to 60 days, white-61 to 90 days, 90+ days green)

Please see the attached tracker for an idea of what I'm trying to do. The raw data is very simplified, but follows the same general formatting. Additionally, our security settings will not allow macros, so while I appreciate any VBA solutions, I will not be able to use them. I appreciate any help!

I am trying to create a tracker to determine when training will expire by date (training is good for one year). I can pull a mass roster from an internet based site, but I need a way to parse the raw data into a easy to view roster of who has completed training, and when they are coming due for future training.

What I am trying to accomplish is to have my tracker sheet look at the raw data and populate a cell with a color code based on the employee name, training type, and date of training. (black-overdue, red-within 30 days, yellow-31 to 60 days, white-61 to 90 days, 90+ days green)

Please see the attached tracker for an idea of what I'm trying to do. The raw data is very simplified, but follows the same general formatting. Additionally, our security settings will not allow macros, so while I appreciate any VBA solutions, I will not be able to use them. I appreciate any help!

Hi all,

I'm trying to sort a time sheet to calculate the true time I have spent on my assignment, in accordance with the rules the company has set.

The company sees the hours as 60/10, so in 6 minute intervals where the calculation is negative of course. I.e 15:00 is 15:00 until it is equal to or more than 15:06. There is also a lunch break of 42 minutes per day.

In my example I have managed to resolve all but 6 minute factor and also how to resolve double input, I.e splitting the work day in 2 shifts with the time in between minus the 42 minutes.

Any help on how to resolve this would be greatly appreciated.

Regards

Fred

I'm trying to sort a time sheet to calculate the true time I have spent on my assignment, in accordance with the rules the company has set.

The company sees the hours as 60/10, so in 6 minute intervals where the calculation is negative of course. I.e 15:00 is 15:00 until it is equal to or more than 15:06. There is also a lunch break of 42 minutes per day.

In my example I have managed to resolve all but 6 minute factor and also how to resolve double input, I.e splitting the work day in 2 shifts with the time in between minus the 42 minutes.

Any help on how to resolve this would be greatly appreciated.

Regards

Fred

This might be too complex to do in an Excel formula, but I'd like to try.

Imagine I have this table:

I want to write a formula that calculates column C in that table. Column C basically: grabs the value of Column A in its own row, looks for all the times that same value has been in Column A so far, and then lists the contents of Column B for every Column with the same value in Column A.

So, for row 3, it finds all the instances where Column A is "A", looks up the values of Column B (1, 2), and lists them.

Is that possible? Right now I've got it simply counting the appearances so far, but listing them has eluded me.

]]>Imagine I have this table:

A | 1 | |

A | 2 | 1 |

A | 3 | 1,2 |

B | 1 | |

A | 4 | 1,2,3 |

B | 2 | 1 |

B | 3 | 1,2 |

C | 1 | |

A | 5 | 1,2,3,4 |

C | 2 | 1 |

I want to write a formula that calculates column C in that table. Column C basically: grabs the value of Column A in its own row, looks for all the times that same value has been in Column A so far, and then lists the contents of Column B for every Column with the same value in Column A.

So, for row 3, it finds all the instances where Column A is "A", looks up the values of Column B (1, 2), and lists them.

Is that possible? Right now I've got it simply counting the appearances so far, but listing them has eluded me.

Hi

I have a column with a series of product codes as below and am using the formula below to extract the region of the product. However, some product codes starts with BR and others with DP and I can't figure out how to tweak my formula to read the first number after the strings and return the region.

Thank you for any help.

Column D3

121408

211601

321502

321398

321502

BR111517

111601

111610

121603

311603

321602

221601

321602

311603

521502

111601

311603

521502

221601

321602

DP122222

=IF(LEFT(TRIM(D3),1)="1","French",IF(LEFT(TRIM(D3),1)="2","Italian",IF(LEFT(TRIM(D3),1)="3","Spanish",IF(LEFT(TRIM(D3),1)="4","Australian",IF(LEFT(TRIM(D3),1)="5","New Zealand",IF(LEFT(TRIM(D3),1)="6","Chilean",IF(LEFT(TRIM(D3),1)="7","South African",IF(LEFT(TRIM(D3),1)="8","Argentinean"))))))))

]]>I have a column with a series of product codes as below and am using the formula below to extract the region of the product. However, some product codes starts with BR and others with DP and I can't figure out how to tweak my formula to read the first number after the strings and return the region.

Thank you for any help.

Column D3

121408

211601

321502

321398

321502

BR111517

111601

111610

121603

311603

321602

221601

321602

311603

521502

111601

311603

521502

221601

321602

DP122222

=IF(LEFT(TRIM(D3),1)="1","French",IF(LEFT(TRIM(D3),1)="2","Italian",IF(LEFT(TRIM(D3),1)="3","Spanish",IF(LEFT(TRIM(D3),1)="4","Australian",IF(LEFT(TRIM(D3),1)="5","New Zealand",IF(LEFT(TRIM(D3),1)="6","Chilean",IF(LEFT(TRIM(D3),1)="7","South African",IF(LEFT(TRIM(D3),1)="8","Argentinean"))))))))

Hi everyone

I have two rows/columns, B14:B24 and C14:C24.

The cells in column B show either TRUE or FALSE.

The cells in column C shows text when the adjacent cell in column B = True.

In cell E26 (merged to be the same amount of rows as the info in B and C) I want to combine the text which populated in column C but don't want it to include any blank cells from C.

I'd like each row of text to be on a different line within E26 also.

Thanks for your help

]]>I have two rows/columns, B14:B24 and C14:C24.

The cells in column B show either TRUE or FALSE.

The cells in column C shows text when the adjacent cell in column B = True.

In cell E26 (merged to be the same amount of rows as the info in B and C) I want to combine the text which populated in column C but don't want it to include any blank cells from C.

I'd like each row of text to be on a different line within E26 also.

Thanks for your help

I am frustrated with several formulas today:

1) I am trying to get a median number of J based on specific criteria from 2 columns (E and I in the attached file)

2) I am also trying to get the median number of J based on when E=private but I does not equal hospice and cannot figure that one out in any way, shape or form

3) I am also working on the average of the same items

Any and all assistance is greatly appreciated

CHillFL aka Carol

1) I am trying to get a median number of J based on specific criteria from 2 columns (E and I in the attached file)

{=MEDIAN(IF('2013'!I:I="Hospice",IF('2013'!E:E="Private",'2013'!J:J),0))}

It is returning 0 when I should get 9.50 - I have tried several variations of formulas to do this including:

=IF(AND('2013'!I:I="Hospice",'2013'!E:E="Private"),MEDIAN('2013'!J:J),0) as well as several others that I have lost during the process

And I am getting either 0 or the NUM error

It is returning 0 when I should get 9.50 - I have tried several variations of formulas to do this including:

=IF(AND('2013'!I:I="Hospice",'2013'!E:E="Private"),MEDIAN('2013'!J:J),0) as well as several others that I have lost during the process

And I am getting either 0 or the NUM error

2) I am also trying to get the median number of J based on when E=private but I does not equal hospice and cannot figure that one out in any way, shape or form

3) I am also working on the average of the same items

I have =AVERAGEIF('2013'!E:E,"*Private*",'2013'!J:J) to get the average of Private (includes Hospice) (comes to 11)

and =AVERAGEIFS('2013'!J:J,'2013'!E:E,"*Private*",'2013'!I:I,"*Hospice*") to get the average of Private with Hospice (comes to 12.6)

but I would like the Private number to be the average of the Private without the Hospice, and I have done circles with that one too, I keep ending up with a negative number :confused:

and =AVERAGEIFS('2013'!J:J,'2013'!E:E,"*Private*",'2013'!I:I,"*Hospice*") to get the average of Private with Hospice (comes to 12.6)

but I would like the Private number to be the average of the Private without the Hospice, and I have done circles with that one too, I keep ending up with a negative number :confused:

Any and all assistance is greatly appreciated

CHillFL aka Carol

Hi All,

I am trying to implement multiple formula to extract data from multiple sheets to a master sheet and came across Num error.

and I was stuck in tranferring data from 2 sheets to one.

Please refer to the attached wb.

Any assistance is greatly appreciated.

Thanks in adv.

I am trying to implement multiple formula to extract data from multiple sheets to a master sheet and came across Num error.

and I was stuck in tranferring data from 2 sheets to one.

Please refer to the attached wb.

Any assistance is greatly appreciated.

Thanks in adv.

I'm trying to calculate XIRR for an oil company from the perspective of an equity investor. The initial investment say is $21 million. For the 1st five months net income and cash flow is negative. In month six oil revenues begin coming in but net income and cash flow is still negative due to drilling and overhead costs. Each month a new oil well is added producing oil revenues. By month 12 net income is positive but cash flow is still negative. By month 14 cash flow is positive. Would XIRR be calculated using gross oil revenue, net income, or cash flow? How would it be calculated? Attached is spreadsheet

I have never used Check Boxes before and i not sure how to achieve what i need, or even if you can

On the attached, what i would like is for only one of the two boxes to be selectable, so when someone chooses one the other one is greyed out or maybe obscured?

And whichever one is selected it will choose which tabs i have along the bottom to be visible (which i have removed on this sheet)

But lets say for example if someone chooses the top box then tabs named 1, 2 and 3 are to be visible, 4, 5 and 6 are to be hidden

and vice versa so if the second box is selected, 1, 2 and 3 to be hidden, and 4, 5 and 6 to be visible?

but on opening the excel doc i obviously need it to be reset so both boxes are visible and selectable and all tabs are visible

is this possible to do with check boxes please? any help much appreciated! thanks!

On the attached, what i would like is for only one of the two boxes to be selectable, so when someone chooses one the other one is greyed out or maybe obscured?

And whichever one is selected it will choose which tabs i have along the bottom to be visible (which i have removed on this sheet)

But lets say for example if someone chooses the top box then tabs named 1, 2 and 3 are to be visible, 4, 5 and 6 are to be hidden

and vice versa so if the second box is selected, 1, 2 and 3 to be hidden, and 4, 5 and 6 to be visible?

but on opening the excel doc i obviously need it to be reset so both boxes are visible and selectable and all tabs are visible

is this possible to do with check boxes please? any help much appreciated! thanks!