Search results

Hi, Attached is your workbook modified with formulas to generate the unique colors list, unique range headings, and data values. Cheers, Sajan.

Hi, The following article by Daniel Ferry provides a great introduction to the capabilities of the INDEX function, and specifically how to construct dynamic ranges: http://www.excelhero.com/blog/2011/03/the-imposing-index.html Cheers, Sajan.

Hi, I should also ask if each row of your example data is in a single cell, or if they are in individual cells. If individual cells, this problem becomes straightforward to solve. If each row is in a single cell, that poses some unique challenges. Cheers, Sajan.

Hello, Not sure if the placeholder is the actual string, or whether that can be various strings. If it is a constant string, try: =SUBSTITUTE(C2," - Placeholder","") Cheers, Sajan.

Hello George, I am not sure I understand your question since it can be interpreted multiple ways. Can you tell me what the results would be for the following data set? 1, TwoTwo, twelve 1,2,3,4,5,Two Two, 3, 4 1,2,3,4,5,6,Two,Two 2, Two, 2 Three, Two, Two -Sajan.

Hi Lori, Thanks for the FREQUENCY based solution. I had initially attempted it, but could not get it to handle all of my test scenarios, including the range starting with a holiday. Besides, I am hooked on LOOKUP now, ever since I found out about its versatility in a recent Formula Challenge...

Thanks Xiq! They seemed the straightforward choice! :-)

Hi I Khan, There was logical error in the formula I supplied, where times earlier than 9:30am were not being accounted for. Try: =NETWORKDAYS.INTL(A1,B1,11)*8.5-24*((MAX(MOD(A1,1), "9:30 am")-"9:30 am")+("6 pm"-MIN(MOD(B1,1), "6 pm"))) Cheers, Sajan.

I interpreted the question differently... If you wish to consider Sundays as your weekend, and as such ignore any hours worked on Sundays, then try: =NETWORKDAYS.INTL(A1,B1,11)*8.5-24*((MOD(A1,1)-"9:30 am")+("6 pm"-MOD(B1,1))) or...

I would suggest going with Chandoo's or Kanti's solutions... but in the interest of showing one more approach... (after all, that is what makes Excel interesting!) =HLOOKUP(1,IF({1;0},N(C2:W2>0),C$1:W$1),2,0) enter with Ctrl + Shift + Enter Cheers, Sajan.

Hello, Here is one more idea... Setup named ranges called Dates and Inventory as Hui suggested. To calculate the 4 week average for current year: =AVERAGE(LOOKUP((LARGE(Dates,ROW(OFFSET(A$1,,,4)))/7)-0*52, Dates/7, Inventory)) enter with Ctrl + Shift + Enter Replace the 4 with 8, 12, etc. to...

Here is how I am interpreting Carley's post: Sheet2 has a list of training topics in cells A1:E1 (e.g. A1 is topic1, B1 is topic2, C1 is topic 3, etc.) On Sheet2, for each topic column, there are a list of tasks for the topic. For example, in cells A2:A5 are tasks related to Topic1; in cells...

Hi Santanu, Let me start by restating the problem: Need to count the number of Holidays and Sundays as Leave Days, when the day prior and day after are Leave Days. For example, LHSL would get counted as four Leave Days, with the 1 Holiday and 1 Sunday days included in that count. LHHHSL...

Hi Santanu, Now that you have a way to count holidays/sundays that need to be counted as leave days, the rest of the counts should be straightforward. Let us know if you need help with that. -Sajan.

Hi Santanu, You have an interesting problem. For the benefit of other readers, I will present a summary of the problem here: You have a series of cells of dates where an employee took Leave (indicated by "L") However, if the Leave was taken before AND after a holiday ("H") or Sunday ("S")...

Complete happiness!! :-) That is exactly what we aim for in this forum!! :-) All kidding aside... thanks for the feedback. Happy to help! Welcome back any time! -Sajan.

I also noticed a "drafts" button! Very cool! Helps folks like me who get distracted with other stuff! :) (I manually inserted this smiley.) However, I noticed a change in how the smileys get inserted. It used to insert the smiley where my cursor was... but now it inserts it at the...

Hi nejjoan14, I thought you had wanted to show multiple job numbers (that matched a given job department and start/end date combination) in a single cell. I had clearly misunderstood your requirement, but am glad you received a solution! Cheers, Sajan.

Thanks Lori for posting those links. They were all new to me, and I am loving it!! (I have to admit that I haven't been reading many of the other Excel forums out there. I should make more of an effort to do so.) I suspect that there are quite a few folks here that are math-inclined, and...

Hello iRasim, Welcome to the forum! There is not much to go on in your question. Please post additional details. Cheers, Sajan

Thanks Narayan. I would encourage everyone to try other values for rate to see what you get and share your results. For example, a rate of 0 will SUM an array. (Remember that the rate is plugged into the formula TermN*1(/(1+rate)^n.) -Sajan.

Hello Venkata, I am assuming that you are referring to your VBA code above. Thanks for putting that together. I will look to one of the forum VBA experts to comment on it since I stay away from VBA as much as possible! -Sajan.

Hi Hui, The NPV formula is actually from Lori (that she posted to Google Groups a few years ago). I came across it recently and fell in love with its beauty too! I was thinking of a Formula Forensics article that compiles a few different techniques that can be used to extract numbers. I will...

Hi Audrey, As Narayan suggested, please upload a sample file. Specifically, I have not understood how you structured the input data for "on hold" days versus "actvity days", especially in cases where you have multiple time periods of hold and activity. Cheers, Sajan.

Understood. Thanks for the clarification. Indeed, I always try to provide solutions based on my interpretation of the requirements, unless the OP was explicit and clear about what s/he wanted. In this case, the OP said "the part number I need to pull out contains letters & punctuations" but...