Search results

Hello Karthik, Try: =SUMPRODUCT((UsersList=B2)*(FromTime<=C2)*(ToTime>=D2)) It will provide a count of overlapping times. Anything with a count greater than 1 would be a duplicate. Cheers, Sajan.

Hello, To handle cases where the criteria for Issue and Sector are not met, use a formula like the one below: =LOOKUP(SUMPRODUCT((IssueList=Issue2Find)*(SectorList=Sector2Find)), 1/FREQUENCY(-99999, -SUMIFS(Sales, IssueList,Issue2Find,SectorList,Sector2Find, NameList, NameList)), NameList) It...

For the benefit of other readers, I am posting the response I sent to Supat based on his private query to me... ------------- Hello Supat, It is best to ask questions in the general forum since I don't always get time to check this forum in a timely manner. Here is something quick and dirty...

Hi, Here is one more approach: =LOOKUP(2,1/(B1:B4<3),A1:A4) Cheers, Sajan.

Hello, Here is one more approach to get the ID of the fruit with the highest Q1 revenue: =LOOKUP(1, 1/FREQUENCY(-(9^9), -MMULT($C$4:$H$63,TRANSPOSE(--(MONTH($C$3:$H$3)<=3)))*('Unit Price'!$C$4:$C$63)), $B$4:$B$63) enter with Ctrl + Shift + Enter Cheers, Sajan.

Hi Luke, Congratulations on becoming a dad! (I somehow missed your original post on the subject!) Even with all of the sleepless nights (and I can relate since my son is now 17 months old), it is an absolutely joyful experience! Enjoy! Regarding your age, based on your "profile picture", I...

Congratulations Somendra! It is very impressive that you surpassed 1000 posts in a few short months! Keep up the great work! -Sajan.

Hi Villalobos, If your question has been answered by Jake, please feel free to ignore these questions... If not, I would be interested in learning a little more before offering any suggestions. I am still not clear on the relationship between column C and column D. Are you saying that the...

Hello Villalobos, Can you clarify how column D is to factor into the calculations? In the sample workbook, the explanation on the right does not reference column D, whereas your sample formula does. -Sajan.

Hi Lori, Thanks for revisiting an old problem. I had actually forgotten about it... Since I am a strong believer in inductive chain learning, a good one to revisit! I have not had a lot of time to visit this forum recently... but I am looking forward to studying the above formula in the next...

Hi, The definition of what is considered Advanced Excel might vary from person to person. For example, I consider VBA programming pretty basic, while many formulas require more thought. I think another way to look at usage is the degree to which Excel is used in a situation, and what type of...

Hi, If you hold down the Ctrl key while clicking a cell, Excel will automatically insert the delimiters between arguments in the SUM function. For example, =SUM(A1, B1, C2, X3) can be setup by typing "=SUM(" and then Ctrl clicking the required cells or ranges. Once you have selected your cells...

I have Save Undo Redo

Hi, Can you elaborate on what you mean by analysis and design phases? Are you referring to the life cycle of a software development effort for an inventory application? Or something else? If you are referring to software development, there are a lot of sites that explain that. If you are...

One thing I forgot to mention... the above formula would count a match if values are out of sequence... i.e. {A";"B";"C"} and {"C";"A";"B"} will match. If you need to respect the position of a value in the array, the above formula will need to be tweaked.

Assuming the list you want to search is in A1:A4 and your lists are in B2:B7,C2:C7, ... H2:H7 The following formula returns an array indicating the number of matches in each list. =MMULT(TRANSPOSE(ROW(B2:H7)^0), COUNTIF(A1:A4, B2:H7)) If you are looking to find out if at least one of the...

There are probably other sites that describe Excel consulting, but here are a few to start with: http://dailydoseofexcel.com/excel-consulting-buyers-guide/ http://dailydoseofexcel.com/archives/2011/11/02/how-much-to-charge-for-freelance-vba-development/...

I am not aware of a way to get the percentile info in a Pivot table without using formulas. Perhaps someone else will help you further.

Hi Sumit, Can you define interpolation and extrapolation without using those words in the definitions? Then provide some examples of how you created such keys. (Don't omit any steps... what you might assume is clear to others is not clear to others at all.) -Sajan.

Hello, I have not checked all of the solutions above, but I interpreted the original post as follows: Calculate the average of the DataColC that matches a specific product and organization, excluding the values that are in the bottom and top 5%. The determination of bottom and top 5% should...

Hi Karan, There may be other ways to do this, but the following is one approach: Add a column in your source data that calculates the percentile summary. For example: =LOOKUP(PERCENTRANK.INC($B$2:$B$9,$B2), {0,25,50,75,100}/100) where B2:B9 is numeric data, and the formula entered is to find...

My two cents... I think we should help anyone who requests it. If the poster explicitly indicates that the post is for a school assignment, perhaps we can initially offer some suggestions instead of solving the problem outright. But ultimately, I would put the responsibility for learning on...

What are the expected results in the following example: 1,2,3,Two,Two 2,3,4,Two 1,2,2,Two,Two Since "two" is found in row 1 also, will the MIN row be 1 or 2? Will the MAX row be 1 or 3? I am basically trying to understand if the position of a substring in a row is relevant.

Hi Sanjeev, Unfortunately, I am not aware of any automated ways to write formulas. As such, I write them the old fashioned way... manually. However, I sometimes use some helper cells to write longer formulas. (After I determine how the whole formula is to come together, I test each segment...

Thanks for the feedback I Khan! Happy to help! Welcome back anytime!