Last 50 Posts on Chandoo.org

Meagan on 2018-11-19 16:58:55
Late to the party, but... Next Monday: =InputDate+CHOOSE(WEEKDAY(InputDate,2),7,6,5,4,3,2,1) First Monday Next Month: =EOMONTH(InputDate,0)+CHOOSE(WEEKDAY(EOMONTH(InputDate,0),2),7,6,5,4,3,2,1)

Ksenia on 2018-11-19 16:51:34
Hi

Tim King on 2018-11-19 10:39:40
Chandoo, Thank-you for your article, which has been a great help. It is frustrating that these solutions do not work on Excel for Mac, but I have Parallels and Excel for Windows running. Also to say that to get distance out, I used =filterXML(cell,"//row/element/distance/value") for metres, and then converted to miles. Best regards, Tim.

Saif on 2018-11-18 18:02:14
I saw your answer "Find the last date of an activity", but i want to add value like,1,2,3,4,5 instead of "x", so please tell me how i find the last date of numerical data activity please. awaiting your prompt reply. regards.

ButchPrice on 2018-11-17 08:58:53
One word. Slicers. They are awesome. Simple and clean when the correct slicer is used. I am learning volumes with the courses, and found myself being referred to in an important meeting as the "Spreadsheet Master" by a colleague. Nice. Thank you for all your efforts. Do you provide a course or study plan dealing with workbooks interacting with other workbooks? I have many workbooks created from a common template that I would like to search for and display, based on key common cells, and extract the 1st sheet of each to compile into one large file of sheets.

Karyn on 2018-11-16 23:43:24
Chandoo, I tried to apply this to my data but nothing happened. I see it in my Name Manager. I also downloaded your sample but do not see how it is formulating the grouping. It's not applied in any of the fields in the field manager. So, how is it working? How is it applied to the pivot table?

philip on 2018-11-16 10:04:28
Chandoo Would Access be a better alternative than VB?

philip on 2018-11-16 10:02:27
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?

Brandon Purdom on 2018-11-16 01:39:02
How to Convert Text to Dates [Data Cleanup] [...]“If good men do good things it can get better” SOMEONE PROTECT THIS MAN HES SO PURE #Supernatural[...]

Steve Curtis on 2018-11-15 19:02:11
perhaps outside scope of this article but I have found when I attempt to create a pivot table from an external data source (connection to a sql view) the "Add this data to data model" becomes greyed out. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?

Candida on 2018-11-15 17:57:50
First of all I would like to say terrific blog! I had a quick questio in whiich I'd like to ask if you don't mind. I was intereested to know how you center yourself and clear your head before writing. I've had a hard time clearing my mind in getting my ideas out there. I do enjoy writing however it just seems like the first 10 to 15 minutes are generally lost simply just tryying to figure out how to begin. Any recommendations oor tips? Many thanks!

Mark on 2018-11-15 12:05:21
I have something similar to below:- A B C 101 Bill Fish 101 Bill Chips 103 Joe Chicken 104 Don Pizza 103 Joe Prawn 101 Bill Peas 107 Sam Pie 103 Joe Curry 101 B1ll Gravy search Column (A) above and produce below from Colomn (C) in a separate sheet A B C D E 101 Fish Chips Peas Gravy 103 Chicken Prawn Curry 104 Pizza 107 Pie Please can you help me ?

bharathram.cn on 2018-11-15 07:34:31
These details were very useful. Excellent for beginners.

Ateeq on 2018-11-14 13:03:20
Please provide the Dashboard for Plan Vs Actual Graph for Block and its sub unit to display using INDEX function

flowers mesa on 2018-11-14 07:08:36
I read this article fully concerning the difference of latest and preceding technologies, it's awesome article.

Deepak Gour on 2018-11-13 16:33:53
Thank you for this collection. It was very helpful for me today.

Sandeep Kothari on 2018-11-13 08:47:48
Dear Chandoo I enjoyed the dates & everything else in the article. Truly delicious!

Robert Lawson on 2018-11-09 16:50:20
So I retried the formula with dragging ranges (which automatically populates the range name) and I got this: =IFERROR(INDEX(Chandoo[[#All],[PointlessThing]],SMALL(IF(Chandoo[[#All],[Person]]=$F$3,ROW(Chandoo[[#All],[Person]])),ROW()-2),1),"") And it works! Originally I was hand typing it to make sure I got it all right and was entering this: =IFERROR(INDEX(Chandoo[PointlessThing],SMALL(IF(Chandoo[Person]=$F$3,ROW(Chandoo[Person])),ROW()-2),1),"") As you can see, I was missing [#All] preceding the column reference. That said, this also works when referencing another sheet in the workbook, as long as the relative positions stay the same. What I've run into now is this: Where I want the multiple occurrences to appear are 'Visit Tear Sheet!F12:F16' The drop-down data validation is Visit Tear Sheet!F8 The table location is 'Visit Log'B49:C148 I've kinda buried the table at the bottom of a spreadsheet because I don't want non-tech saavy users to easily find it and screw it up. I know I could let it rest on a separate sheet starting at A1 like our sample data set, but I'm trying to keep the number of sheets to a minimum to keep the weight of the file down.

Isra'a on 2018-11-08 22:27:36
Thank u :)

Robert on 2018-11-08 21:33:14
Hi Chandoo I've replicated your exact spreadsheet and it works perfectly, thanks! For my actual application, I'm using a Named Table where: $B$1:$B$20 = Chandoo[PointlessThing] $A$1:$A$20 = Chandoo[Person] Replacing the fixed cell references with the Table[Column] values the array formula produces an output that is one cell below what the actual value is. For example, if my lookup value is Das Hoff with the named table I get Amnesiac, Raging, Limb King, Krautrock, Erasing. When I just use the cell references I get Talented, Knightrider, Baywatcher, SpongeBob, Krautrock. As you can see, outputs when using the named table are actually one row below the intended output. I've varied the formula, from completely deleting the -2 in ...ROW()-2, to trying 0-3. I can never get the named table formula to output the same results as the cell reference formula. I've noticed the lateral distance doesn't matter, only the relative horizontal distance, so for that reason my named table formula starts in cell E3, referencing E2 as the lookup value, and my cell reference formula starts in cell G3, referencing G2 as the lookup value. The Person/PointlessThing columns begin at A1 and B1. The table is named "Chandoo." So my named table references are Chandoo[Person] and Chandoo[PointlessThings]. As a final note, I'm using data validation, referencing the Person column of the named table as my lookup values in cells E2 and G2.

Javi on 2018-11-08 18:35:25
What an interesting article. Without doubt Excel is an irreplaceable tool.

Anne on 2018-11-05 16:03:49
Hello Chandoo I'm using the Bing Maps API. I've a question about the results of the formulas of coördinates, km and minutes. The calculationsheet gives a traveldistance and travelduration of -1. Also the coördinates aren't right. For example, I've got 532191 instead of 53.2191. Can you explain how this is possible? Thank you!

AMAN_PAREEK on 2018-11-05 12:45:32
Short-Simple-Sweet!

P K Shah on 2018-11-05 07:13:11
Excellent. I could open, save and close file without any problem. Also added different directory to save text file. Thank you,

Den on 2018-11-05 00:06:27
Chandoo, thanks for the great webinar and file. Do you have an example of a survival analysis in the power pivot that would show the likelihood of working up to a certain length of time in a company?

Virupaksha on 2018-11-04 18:17:04
Hi Team, I have inserted a slicer to a pivot table with 4 fields...I need to add another field for the same slicer...help me with this..

okesanya ismail on 2018-11-03 19:22:11
Your work is excellent but pls willing to know more details about the features of microsoft excel

Meggaable on 2018-11-03 07:12:39
Hello Chandoo, I am very pleased and glad about your great work on the net. In fact I must tell confess...YOUR "GENIUS". Please Chandoo, I need Excel Report Card Template that can handle both High School and Primary. I already have one with me that is also Ok but it is limited to number of Subject, and has some feature to be tune to my need I don't know if you can help with it so it will not need building a fresh one from scratch. I have already mailed you through your Gmail and have also attached the excel template. I will really appreciate this. Thanks and God bless.

Ashutosh Bansal on 2018-11-02 10:54:57
When you excel, people start to spreadsheet. This guy has a powerpoint.

DragonPhantom on 2018-10-31 15:55:39
When using an [Index] column and a custom "Number.Random()" column I was not able to sort the data, all it would do is recalculate a new random number. So when I was playing around, I found that if add a custom column and add "List.Random(1)" it will give a "List" column and upon expansion it gave me a perfect set of random numbers that don't change and are sort-able! :D