Search results

Hi Kaushik and all... Here is a pure formula solution. I use a helper column, but I think we can avoid it with a named formula. See it here: http://img.chandoo.org/playground/event-date-listing-kaushik.xlsx

Hi Rom... Use this. Assuming the value is in A1, write =text(if(right(A1,1)="/",left(A1,len(A1)-1),mid(A1,find("/",A1)+1,999)),"@") This should extract either left or right portion while retaining leading zeros.

@Tel57... Welcome to Chandoo.org and thanks for posting your question. Pivot tables are meant for summarizing data only, not showing actual details at point of intersection. To get what you want, you can use INDEX, MACTH formulas. I made an example with data similar to yours. See it here...

Diwali wishes to all of you :) Check out http://chandoo.org/wp/2012/11/13/happy-diwali-animated-chart/ if not seen already

@Ricardo... With this setup, there is no automatic way to add decimal points using Excel alone. You can do this with VBA. But for such a small thing, writing VBA may not be the right way to go. My suggestion is like this: 1. When entering data, let users type just 10 digit numbers (with...

@Ricardo... you need to format A3 as text. Also use this formula. AND(ISNUMBER(A3+0),LEN(A3)=11,FIND(".",A3)=6) See this example file: http://img.chandoo.org/playground/complex-data-validation.xlsx

Ricardo... Welcome to Chandoo.org forums and thanks for your question. You can use Custom data validation to do this. Follow below steps. 1. select the cell(s) which need to have this validation. 2. go to data validation and select custom as rule type 3. type...

If you just have few places where you need this, you can draw a small rectangle with a down arrow in it (enter 6 and change font to webdings). Then place exactly where the combobox down arrow appears.

Interesting question Nassardewa. While I do not recommend such chart (pie leaking pie leaking pie chart), I think it is a good one-off exercise to see what is possible. Powhatan showed the first step in that direction. I went bit ahead and added graphic shapes that make it look almost like...

I found that it is easier to control a drunken monkey's typing than Pivot table formatting. My suggestion is simple. Use pivot for generating the values you want. Use a separate view sheet where you fetch pivot values with either getpivotdata or simple linked cells Then format the view sheet...

You can use Active X controls for this. I think there are a few that does this job. But you will have to use some macros to deal with check / uncheck events.

If you have Excel 2010, set up slicers and use them. This way you can update one and the other pivot updates too. More on this here: http://chandoo.org/wp/2010/12/08/dynamic-dashboard-video-tutorial/ In earlier versions, you can set up a simple macro that does it. More on this here...

Lockdalf & others starting with Excel 2007 or 2010, use these links: Introduction to tables: http://chandoo.org/wp/2009/09/10/data-tables/ Table formula copying: http://chandoo.org/wp/2011/05/23/preserve-table-references-while-copying-formulas/ Using tables with formulas...

Good discussion... I agree that vault should be treated separately from rest of the forums. Here is what I am thinking: Each submission to the vault to be reviewed by a ninja. Although the posts appear immediately, ninjas can review them and move them to another forum if needed. Also, ninjas...

Interesting question KPJ... While this is possible with formulas, we need use circular references. We need to extract a subset of data based on the freeze & scroll bar values. The logic is like this: When the freeze option is unchecked, - use the scroll bar position as start of data series...

I am not sure I understand this correctly. Median is a middle value in a list (when sorted). So what do you mean by median of the sum? If you meant average... then you can use below formula (assuming time values are in G17:G100, numbers you want to average in H17:H100)...

hats off to Sajan for the formula and Narayan for explanation. I saw this post and played sometime with a formula based answer, each time drawing a blank. So I bookmarked this thread and waited for others to enlighten me. :)

You do not need a macro for this. Assuming your data starts from 2nd row, and column J has the invoice unique identifier (=invoice number & invoice amount &Operating company & Vendor name) In K2 write =countif($J$2:J2,J2)>1 This will be TRUE for second, third etc. duplicates, but will...

Hi Jrek67, You can create this in Excel using scatter plots. There are 3 parts to it: 1. the violin plot using smothed lines showing the probability density 2. The actual data points as rug plot 3. the quartiles as lines (ala box plot) The process of calculating probability densities is...

Duck tapes...? Our CFO (wife) wont even make duck stew. May be I can convince her to invest in some scotch taps..

Hi Rickramz, Welcome to Chandoo.org forums and thanks for your question. There are many ways to do this. The simplest is to use formulas and make everything dynamic. With this method, all you have to do is focus on data entry and your body readings. Excel will update the charts & latest...

It is probably the storm hitting our servers. Seems to load ok now.

If that is the case, it is done either, 1) with conditional formatting 2) or formulas & special font (see here: http://chandoo.org/wp/2008/05/07/create-in-cell-pie-charts-in-excel-how-to/ ) For (1), just enter a number in a cell, select the cell, goto home > conditional formatting &#62...

Also, check out our recommendations here: http://chandoo.org/wp/excel-vba/books/

Congratulations Sir JB... Wishing you many more such milestones.