Programming language design concepts / David A. Watt; with contributions by William Findlay. Reader who knows only a language like C, C++, or JAVA should learn a contrasting language such as ADA (or vice versa). GHEZZI and JAZAYERI (1997), SEBESTA (2001), and SETHI. Programming language.

About Chandoo.org At, my aim is to make you awesome in Excel. I do this by sharing whatever little I learn everyday. Our community has an open mind. We are eager to learn and share. We all constantly teach each other new tricks and ideas using comments and forum posts. I am glad to know that you are here and want to be a part of us. I invite you to.

Every week I send 1 email with an excel tip or charting tutorial or useful material on VBA, Power BI, analytics to help you become awesome in MS Excel. Also check out to know how we can help you better.

I have launched Chandoo.org in year 2004. After 3 years of personal rants and chatter, I shifted the focus of site to Microsoft Excel, primarily to share new ideas and discuss about the problems I am working on. Soon, the site gathered momentum and today it is a massive part of my life.

We get 1,150,000 monthly views with readers from all over the world. There are ~125,000 members reading this blog through feed readers & email. The blog has been featured on Major newspapers, few books, LifeHacker, MSN, BNet, Technet and Delicious.

It made me a better person and communicator. I am able to connect with a wide part of the world and exchange ideas freely.

All thanks to this little site. You can support this site by, • Joining one of my training programs –,. • Getting a copy of my books – &. My products are well received by our members. You can try one of them and become even more awesome.

About Me: Chandoo I am your CEO here, Chief Excel Officer. My full name is Purna Duggirala. Chandoo is my nick name and I used it as my domain name too. I have an MBA from and Computer Science degree from. I am passionate about Data Analytics and Visualization and this blog is my platform for sharing what I learn about Microsoft Excel and new ways of presenting data. I have been (and renewed in 2010, 2011, 2012, 2013, 2014, 2015 & 2016)..

I am married to Jo, my college sweetheart. During September 2009, we became parents to 2 beautiful and naughty twins. A boy and girl. Life has been hectic and incredibly fun ever since.

We are based out of India, but we travel around the world and meet new people and cultures all the time. As of April 2010, I have quit my day job to become full time entrepreneur.

You can read about my journey as a start-up owner. Update – August 2016: We moved to New Zealand and currently live in windy Wellington. Our kids are in year 2 and they love their school. Jo & I spend most of the day enjoying beautiful weather, cycling and cooking delicious food. Thanks for visiting my site. Feel free to drop feedback on my email: chandoo.d [at] gmail [dot] com.

How do we run Chandoo.org I quit my job in April 2010 and Chandoo.org has been my only business ever since. Thus began my entrepreneurial journey. If you are curious to know how I run my business,. Stuff We use & Recommend Many people ask me what software, tools & services I use to manage a reasonably busy site like Chandoo.org.. You may also want to read about how you can use HTML and other codes to enhance your posts here:. Join our community If you like Chandoo.org story and want to be part of our community, please join us thru one of below links.

– Enjoy Chandoo.org Podcast Recently, I have launched Chandoo.org podcast and like everything else we do, its aim is to make you awesome. To listen our podcast sessions or subscribe. Like this article? Please or to your reading list to receive excel tips, charting ideas every weekday. Hi Chandu, I have gone through your website and is pretty amazing. I own a financial planning business in Australia and is planning to launch a property investment company and needs a worksheet which could given projections on home ownership, assumed growth, loan amortisations over 30 years and it should also have some provisions for tax calculations if possible.

Can you Please email back and if you can provide a contact number I am happy to talk to you in person to update you my needs and can discuss the cost involved. Chandoo.thanks for the info. I am trying something 'new' for me in excel and am running into a wall.

I downloaded your check boxes, and copied/pasted them in.great if I only need those 30. But I need 100. The new ones I add in format back to the old check box 'format' location from the first cut and paste. SURELY I am missing something.

This cannot be as hard as it seems. Why can I not 'drag' the formatting with the checkbox. Is there a VBA script that would solve this whole frustrating malfunction. Dear Sri Chandoo Sir, I am appreciating you and I wish u very good success in future developments also. I am working as an Accounts Officer in BSNL. I know excel upto the certain extent i.e, data entry, editing and some of the formulas etc for my regular work.

I want to become a expert in excel programming (i. Sig Sauer P230 Serial Numbers. e, with available monthly reports from departmental softwares like Pay package, I want take some other reports by using excel). To do this task, what I have to do? How can I proceed? Please advice me. Hi Chandoo, I am also very much intersted in Excel just like you and have been working with it since 2000 and have explored it with functionality and applicability.

Somehow there's area which i am getting a little confused with and somehow cannot find a solution. I would like to embed the Last Modified date of a document in the same document as this will save a lot of work during versioning and auditing. If you get time please do let me know how this can be done and I will be grateful to you Regards, Brijesh D. @Krishna: Welcome to chandoo.org brother. Instead of trying for an excel related job, go for jobs like analyst, business analyst, financial analyst etc.

These jobs have better career prospects and help you learn and earn more. These jobs have ample excel (or other data crunching software use) so you will enjoy them too. @Champak: Thank you so much. I am happy you like my site. I offer some products and training programs. For details visit - and @Anindya: I am humbled.

Thank you so much and welcome to our community. Hi Chandoo, great to know about your achievements. Papa. After reading your blogs I am very much interested in learning more in Excel.

My job is Project management. Right now I am trying to add a custom field to an existing report in Excel which pulls the data from Timesheet (reporting db). I am unable to figure out where the custom field data is being stored in Reporting DB. I added a column to existing timesheet in project web access.

Afer updating the timesheet data is still not showing up anywhere Let me know if you can help me with this. Thanks, Sandy. Hai, This is mahanti from sompeta, srikakulam(dt). I came2 know about u and ur site through the news paper(Andhra Jyothi). I've complited MCA from B.U and having experience of nearly eight years in teaching field starting from school level 2 Eng.College.When i came 2 know about u, my interest become more on this matter.So this is the comment for u 2 understand about me and my interest on ur service. I want ur help in this case for the updation of blogs through the net by using documents. Thanking u, Bye Mahanti.

I have just found this site, and its great for me! I could not find anything about printing different sheets without doing it manually. I mean, i have this bad excelworkbook with 30 sheets. At the first sheet I fill in some data(not vba) and as a result of this, sometimes I need to print sheet 2,3,5,9 and 21. Or 2,3,15 and 17. Is it a way to use a dropdownmeny with different options that will print different sheets as a result of the choise you make?

I hope it is an easy way:) Thanks for helping me:). This blog is very useful to learners and to know the solutions for any query occurs. First of all i would lke to say Thanks & congrats,this blog is amazing. It is very useful to know things which are in excel.

And i want to learn the Macros. Am staying at Hyderabad.can u suggest me which institute is good and can u send me tutorial for Macros.i want to learn macros in MS Excel.please send me the notes. I am looking for MIS analyst job.presently am working as MIS executive. I have good knowledge on Excel but where i attended an interviews for the position MIS analyst they usually asking do u know the Macros? So i decided to learn the macros. Thanks & Bye, vishwanath Reddy.K.

Hi Chandoo, I am so happy to have found this site. It has helped me come up with some great Excel solutions for my job. Thank you so much for presenting the information. I just saw your family photo with your wife and the brand new twins (although I believe that photo is over a year old) Such a beautiful family!

You have so much to be proud of and grateful for, my friend. Many thanks for all of your posts and email notifications. All the best to you and yours. Gina in the mountains of Western NC, USA.

@Dhanapal R - I am sure Chandoo wont be having much time so I paid a small visit to your website. Firstly I felt it very Clumsy - Information Overloading Secondly It seems you are forcing to make it popular (read it 'trying to sell it!) and lastly in 10 seconds i also felt, you are just pushing a student to learn 'mugging' How I felt Chandoo is unique. Very neatly arranged, precise and compelete information (of what you need!!!!!!) Secondly, He is not selling the knowledge, He is sharing it (More knowledge share, more it will grow-- I am too good:)) and most import, he is teaching us the concept in chandoo's way which Rocks!!!!!! PS: Sorry chandoo using your blog for such a discussion but I know you must be busy in inventing something AWESOME in excel so i did your part. HI Chandoo Sir, I am also one user in your website Its a very excellent Side Thanx a lot.

You are one of my Ideal as a Excel Hero. Request you to share with me some Good Ideas how can I will start with Excel Macro & VBA Programming Language.

I want start with Excel Macro & VBA Programming course as well I have called you website number as well but you are providing online classes but I have not a good knowledge about the VBA codes. That's why I am little bit confused. Request you to share some good Ideas for the same. I want also be excel Champion.

Regards, Jagdish N. @Kamran You can become expert in Excel without attending classes or reading books There is stacks of free stuff on the internet Get involved in some web sites, read, read, read Then Practice, practice, practice Try answering forum questions, even if some one else has answered it, try it yourself Start small and work up steady steady Ask questions on the various forums Even an old Excel 97 or 2003 version of Excel will still do 90-95% of what the newer versions can do. Similarilly books, Look for old Excel books 97, XP or 2003 in school or other book sales, they still contain most of what you need to know There are a lot of free tutorials on different aspects of Excel on Youtube, watch and then practice. Hi It is very nice to see the blog and proud that a Telugu person has got so much rewards.

Visit my blog careerandresum.wordpress.com I helped few people when there was no Excel - I used Quattro and Lotus in those days and trained whole staff of a company out of interest. I am basically a game changer - when every was using wordstar etc., for balance sheet and etc I trained them about using Lotus and then Excel. I wanted to learn Pivot table - I got it from your site and many thanks. If can reach any bank people in India, there is a format they use - CMA data sheet - to make analysis of credit worthiness. If you can put it in pivot table and sell it - by introducing in then you will have many customers. Hi Chandoo.very nice website you have.

I have a difficulties in my project that need your advice. Im now running a dashboard in excel sheet. I have a workbook with multiple sheets, and sheet1 will be the main page to consolidate all data from other sheets( total 9 sheets). The data in other sheets has to be update on a weekly basis, and what i intend to do is that, i plan to have an update button in the main page, and once clicked onto the button, the system will then generate each of the sheet to compare with previous week value, if there is update value, it will then updated onto the 1st sheet, and action will repeats for the remaining sheets. Can you guide me on how to start writing the code? Greatly appreciate your help on this. Hi Chandoo, It's really awesome site.

The information on this site is quite valuable and most importantly FREE. Which many sites charges. It's a Hats off effort. It help me a lot to simplify my way of working in excel. I had a question I work with huge database which are downloaded in Tab delimited format.

Then I use 'convert to text' formula then select delimited with ' ' the data gets converted to excel format. Now the problem is whenever there is huge data (file size more than 250,000 KB) either the file get hanged or data is converted but the lines are not in place. Data moves from one column to another and the whole report gets disturbed. To fix this it takes a huge toll of time and patience which is nothing but frustrating. Would you able to help me in solving this type of problem? Regards, Abhik.

Hi Chandoo, Thanks for posting all the information on your website. They are very helpful and informative. I will like to ask you for a help concerning excel Pivot table. I was recently working on a Pivot table in excel and there seems to be one particular problem that is preventing from moving forward. I was hoping to get a view from an expertise like yours. My Pivot table has two different Running total field(Running Total for for Column A and Running Total for for Column B).

I will like to create a new calculated field based on the two Running Totals ( = Running Total (A)/Running Total (B)). I know it is a simple as dividing the two fields but the problem is how do i divide the two running Total field. Running Total is an internal function which i don't have access to. When I tried inserting a new Calculated Field, I only have access to A and B instead of Running Total (A) and Running Total (B). I tried entering Running Total (A) / Running Total (B) but it gives me an error.

Hello Chandoo, I love the website and appreciate all the great adice you provide. I have a small issue I'd like to see if you or any of your followers have expereinced or resolved. In a dashboard, I have a range of data (a1:g14) that is separated by column h and resumes (i1:o14.) The data reads left to right and down to 'g' then picks up at 'i,' left to right and then down again (basically there is a gutter in the middle of the dashboard.) All of the data is related and I would like to sort from lowest to highest (column g & o) but the data would have to be rearranged across the gutter, possibly moving back and forth on each side depending on the rank. Is it possible to sort the data but tell it where to locate once sorted? I hope this makes sense. I'm happy to provide a screen shot if it helps the illustration. Thanks for looking into this.

Your forumlas are very helpful. I need to know how many 'bookings' students I have at any one given time. I chose weekends because that's when they arrive/depart.

I have a lot of overlap and I need to know how many students I have here on a given date /weekend-despite overlap. I'm not sure that's clear as I am having a difficult time discerning the best way to calculate. I need to make sure I have enough accommodations for the students so I can stop sale if I am saturated one weekend.

Not all students who arrive in January leave in January. Some leave other months. Also, some who arrive in say May, leave in May. It all depends. Does that make more sense? Hello Chandoo and thank you so much for your site. It is full of information and you are a very generous soul for sharing it with us.

I work in an accounting practice in Australia. We needed to develop a spreadsheet that would prevent duplicate data entry, specifically preventing an invoice number from repeating. I found the answer at your site and it works! The socks have been knocked of the feet of me and my co-worker! I have bookmarked your site and will be back.

Great service, Sir. Thanks, John. Hi, I am facing a peculiar problem on my excel file, i am using this file in my office. It has loads of date say close to ~ 35mb. Now in one of the tabs i have a pivot set up which i refresh to set up the data in a given format to facilitate my work. Now for the last few months after I save and close this file, when i again open it up the file has an error.

It say 'unable to read' the file, it asks' do you want to recover the file'. After i say yes and recover the file, the pivot is vanished. This is really hampering my work. I would appreciate if any one of you can help me with this. Regards, Vaibhav. Hi All, I am trying to pull data for some structured swaps modeled on bloomberg under the swpm function in my login. I am using the following function =BDP('SL4213RA CORP','SW_OPTION_VEGA','SETTLE_DT=20120531','SW_CURVE_DT=20120531') Now i have two question here 1) I am getting the option vega where the discounting curve and forward curve is assumed as bid, where as, I want the price where all these scenario are assumed as mid.

2) I also want the market price for these instruments, for historical dates. Your help will appreciated, do let me know in case of any doubts. Thanks and regards, Vaibhav.

Hi Vaibhav, Thanks for replying. No, we recommend a web-app which enables you to (for instance) validate data and address, finding invalid format, de-duplicate, merge, and sharing list/data without breaking a sweat.

Executing the mentioned tasks without any formula, only buttons. Mainly we focus on cleaning features. So here it goes, the users can import/copy paste their excel/pivot data into our web-app and play with it. It would be wonderful If we could share any thoughts with the excel addicts. So we can know your problems and what dreams you have to increase your coffee time while dealing wit excel, we try to build it for you. Don't hesitate to throw me questions or dreams and to try out private alpha version: Cheers. I am reading your blogs since last 5 month.

I am working in purchase department. Actullay i felt i dont know how to create purchase department dashboard report using excel.

Would you like to help me to create the same or provide any idea using following feilds. I have data with PO ISSUED DATE, PO No, VENDOR NAME,VENDOR ID,QTY. UNIT COST,TOTAL COST,PO RECEePt DATE, PO RECeIPT QTY, etc. It will be greate if you post some dash bpard report example in yzour blog. Please please help me...

Thanks & Regards, MOHSIN NADKAR. After a long time I have visited your website, Chandoo. Its fascinating and I love the questions which people asks in this blogs. I try to learn Excel by my own and love to experiment any stuffs which I find it interesting.

So just going through the blogs here, I got one interesting scenario which was a question published by Amit on 22nd Oct. I don't know if you have got the solution to this Amit, but it gave me something interesting to learn.

Thanks a lot. I am mentioning the solution step by step: 1. Go to VBE (Alt + F11) 2.

Go to 'Insert' -->'User Form' 3. Now create a Textbox (By default, the name of the textbox will be 'Textbox1' - and since this is an example, I am not changing it 😉 ) 4. Now Double click on the Textbox which you have created.

It will open a code window with 'Private Sub TextBox1_Change()' 5. Now put the code If InStr(TextBox1.Value, ' ') >= 1 Then MsgBox 'Space is not allowed.

Please reenter data' End If The code should look like: Private Sub TextBox1_Change() If InStr(TextBox1.Value, ' ') >= 1 Then MsgBox 'Space is not allowed. Please reenter data' End If End Sub I think so this should do the trick 😉. Hi, Chandoo I love your website. Although I have little knowledge in Excel but slowly I'm getting involved in Excel owing to its excellent features & versatility.

Your website comes handy with your depth of knowledge & experience. Many many thanks for teaching me. I loved your family pic. Beautiful family. Congratulations on having rare twins with rare names - Nakshatra n Nishanth. I'm a real estate agent in Bhopal (MP) in India & running a shoe store as well.

I do my billing in Excel. I'm desperately trying to find a solution wherein my billing will be linked to my Inventory to update it simultaneously. Please tell me if you have a solution. I shall be grateful. Warm regards.

Swatantra kumar sethi. Hello Chandoo! I have just stumbled on this site and must admit that I am not the only one re-inventing the wheel but improving the aura of invention using excel 🙂 🙂 🙂 🙂 Excel is a large part of my day-to-day work - I am very passionate about Excel and am soooo very glad that it is not like a novel that you read once and the end is final.

There are always new ideas and professionals, such as yourself who write the sequels! Otherwise, what would we be without Google 😉 I loove getting a tricky mind-boggle - and solving it - my puzzle! Hope I have enought time to visit and learn from you! Very best regards, Astrid. Dear sir, I need a small help in excel. I want to know if I enter an end date of an event then automatically the excel should fill in all the dates for other tasks backwards excluding weekends. Example if the event end date is June 10th then it should enter the dates like below: Collect ppt on 9th june ( here collecting the ppt is a task that has to happen a day before the event) arrange gate pass - 8th june finalize the venue of event - 10th May task 3 - 21 may task 4 - june 1st etc etc The only thing I will give is the end date of the event which is 10 June I hope you will help me sir.

Thanks & regards Ramesh. Hi, Chandoo, I am a big fan of your website. I have been following your news-letter for two years and also installed your app 'Learn Excel' in my cellphone. However, when I changed my cellphone and reinstalled the app, all the old articles and padcasts are gone, only the recent ones are still in the app. I still have them in my tablet, but could you tell me how I can transfer to my smart phone? I've tried to copy the whole folder from my tablet to my phone, but the app could not recognize them.

Your help will be very much appreciated! Hi Chandoo, Thank you very much for sharing your knowledge. Speaking from the bottom of my heart, I learnt a lot from your website. You and your team are doing a really Awesome job.

Recently I got promoted to Managerial cadre and I would like to implement few of my suggestions with the help of Excel and VBA macros. My question for you is, Do you any plan to conduct live classes on Chennai. If so, I kindly request you to drop a email along with the cost of the course. I prefer to attend only physical classes instead of online courses. Nobody has repsonded to my email which was sent on Monday 15th Sept 2014.

In Chandoo's absense, the other members who work with Chandoo have not even bothered to reply! Hi Chandoo, I hope you and your team are well! Really need your help today! I recently purchased the project-portfolio-dashboard-VBA-v1-2010, and just noticed that I need to record 20 project plans.

When opening the spreadsheet, under the ‘Plans’ tab I can only add upto 10 project plans. Please can you re-send me the project-portfolio-dashboard-VBA-v1-2010 which allows me to add more than 10 project plans.

Thank you Rakesh Mistry (rakeshmistry@hotmail.com. Hi Chandoo, I hope you and your team are well! I am really struggling!!!!

I have 2 major issues and I question. Please see attached my workbook. 1.I have logged risks and issues for one project called “Communication” under the Risk & Issues tab. The problem is that Dashboard is not showing # of issues or # of risks for that project. Please can you amend or check ‘all’ the calculations. (I have tried everything and I am really struggling!!!!) 2.Every time I open time workbook the format of the Gnatt Chart is out of place. (you will see) Please can you amend this.

3.On the Dashboard you have a box called People. Please can you explain the what the bar charts tells us. Regards Rakesh Mistry.

Hi Narayan, I hope you are well! I am really struggling!!!! I have 2 major issues and 1 question below. Please see attached my workbook. 1.I have logged risks and issues for one project called “Communication” under the Risk & Issues tab. On the Dashboard, on the left hand side where you select you project it is not showing No. Of issues or No.

Of risks for that project. Please can you amend or check ‘all’ the calculations. (I have tried everything and I am really struggling!!!!) 2.Every time I open time workbook the format of the Gnatt Chart is out of place. (you will see) Please can you amend this. 3.On the Dashboard you have a box called People.

Please can you explain the what the bar charts tells us? Regards Rakesh Mistry. Dear Chandoo, Firstly I want to express my admiration to you. I am (Economist) on Oil and Gas, and I want kindly to advice me about the best way to be involved in building an (economic modeling) as a Profitability Analysis to study economics and evaluation the Petroleum Projects, I mean the value chain of this industry (Upstream, Refineries, Pipeline project, Marketing.etc). Also, I'd like to ask if you have a specialized course for that topics (Economic Modeling for Petroleum Projects). Or a an Integrated Model for study these projects (Economic & Financial Model as a Profitability Analysis and Risk Analysis), and what is the cost of these products assuming send via email.

Thanks again and accept my respects. Best Regards. Chandoo, I had to stop in the middle of my work to THANK YOU for your simply awesome and effective Excel tips. I am specifically referring to the Time Saving Strategies you submitted for Business Analysts. This breakdown is extremely efficient and brings a high level of relevance to using Excel effectively. I will be teaching some of these strategies to the Analysts at my organization on Friday and b/c of your tips, I am overly excited to present to them.

Thanks a million, I could go on and on, but I will return later and let you know how the session goes! 🙂 Best to you! Marsha McConnell. Let me start by saying I enjoy your podcasts and emails very much.

I have found them to be very helpful as I learn more and more about Excel. I am in need of some assistance. I have an excel table to which I add several new rows each week. It is accessed by multiple users, and I need to use sheet protection to protect the formulas and certain data within.

The table however, will not expand with sheet protection on. I was hoping to solve this with a macro that could add 10 rows and then activate the 1st cell in the first empty row. My Dear Chandooji: I have problem: Kindly help me, I have an Excel Sheet in Excel 2013 having one column with 10 Numbers in all cells of C Column( Total 2116 ). To want to convert it in US Date Format i.e. Please help me so that in future if I add 10 digits in C Column, it will automatically be converting in US Date format i.e. If I enter 4, it should be converted in 554-696-2234. By Cell Format Method & Formula Method Or more methods.

Thanking you in Advance. HARESH SHRIMALI AHMEDABAD 0. Hi Chandoo, Amaze to see all the above information. I hope even I get a solution to my problem. I am trying to use this excel as a token machine. I have linked up this token sheet with other sheet which shared by four users.

If any off the users changes the token number, it gets change in our main display but the problem it doesnt say the token number. WHAT I EXACTLY WANT IS AS THE VALUE GETS CHANGED IN ANYONE OF THIS SPECIFIED CELLS (B4, B5, B6 & B7 THE EXCEL MUST ANNOUNCE THE NUMBER. For example If in cell B4 the number 7 comes, so the excel should tell ” TOKEN NUMBER 7?, after that in cell B7 is updated 21, so its should again tell as ” TOKEN NUMBER 21? And so on it should keep doing the same. I hope I get my solution at the earliest. Hi Chando bhai, i have gone through the portfolio managment dashboard and i simply love it.

It is worthy to be adopt. I have downloaded the locked version which is totaly as per my requirments but there are 2 or 3 things which i need to change on the main dashboard for individual acitivities.

For example i dont want budget details to be published against each activity because if i consider my project milestones that does not apply. Thats why i need to change it to START DATE Vs end date and the percentage of work done. Rest all good to go. Now the problem is i need to have the unlocked version and for that you are charging 30 USD which is too costly for me.

I am an indivisual working in a smal org and can not affoard to buy it. If you can do something i will be thankful to you. Regards Waseem.

Dear Sir, I am a great fan of you. I am highly inspired from you and the way you live your life and achieved the success. I was also a student of engineering, but unfortunately i did not able to complete my degree, i am now married and i had also started blowing 4 month back, i regularly write content which i know best, i had also uploaded lot of useful tech video on youtube, but still not able to earn my living full time. Just i want to ask sir, is this my decision for depending on blogging is right or i should search for some job or shall i complete graduation is arts and look for govt job, please suggest sir. Hi, My name is Kailash, working an organization' i want to share my idea so tht you can convert into excel sheet for example in a shop there are 200 items, every item expense daily or per requirement, My duty to maintain Monthly record of Item that how many item (quantity) expensed in the month and how much stored. And total Balance Quantity automatically show me in each month (for example if there is 5000 Pen in a shop and 2600 pen selled in april 2015 so it should show Balance 2400 Automatically, and if selled 2000 Pen in May 2015 then 400 Pen should be show in Total Balance.

So how can i develop it as a excel formula.plz help. I have a boss who is adding sales totals for each day and he wants me to calculate the following: Median Total Sales for each day of the week Average Sales for each day of the week Record High Sales for each day of the week Record low Sales for each day of the week. He has (abbreviated list) Column A: Day of the week by Number (1 = Sunday, 2 = Monday, etc.) Column B: Actual Date such as Column C: Total Sales Column D: Total Sales product 1 Column E. So I created a new sheet where I reference each cell of that sheet and sort by day number.

I then used the Match command to find out the last relative row that has that day number. I add in the number of rows of his headers (which I'm leaving out of the sort) to give me the actual row number. I repeated for each day of the week. I now know: Beginning and ending row for each day of the week and this updates as his data is updated after I run the sort on the second sheet (something that I would like to have automatically happen but I don't know how yet). My boss doesn't want me to post the actual spreadsheet so I created a sample one. Note the store is only open Monday through Friday and this is only a SAMPLE and not the full row of cells.

He doesn't want to use Pivot Tables and once I get this working he wants me to work on other things and wants to take care of this himself so I need this as simple as possible for him. I tried doing =Median(C8:C36) and it works but when he updates tomorrow the rows will shift down so I can't have my boss enter these manually every day.

How can I make it so that the values in =Median point to the cells below where it shows C8 and C36 (which will be '37' tomorrow and so on). When I try to do that I get an error.

Apparently it is thinking I actually mean I want a MEDIAN of the cells where my values are below and now the actual data that these cells actually are referring to. I'm not that good at Excel and I've been trying to figure this out for days and finally stumbled on your website.

Can you help? Sunday1From To Monday2C836C$36 Tuesday3C$3770C$70 Wednesday4C$71102C$102 Thursday5C$103133C$133 Friday6C$134162C$162 Saturday7. Update: What I'm ***trying*** to do is this: =MEDIAN('Sorted by Day of the Week'!C$8:CONCATENATE('C$',D32)) So that I can get the MEDIAN of cells C8 through C36 (in my abbreviated data).

D32 has a value of 36 which is the last row with a 2 for Monday. If tomorrow is another Monday then that value would change to 37 and I want it to automatically update in my MEDIAN calculation. Hopefully that makes sense. In my formula above I get an error saying, 'We found a problem wiht this formula. Try clicking Insert Function on the Formulas tab to fix it, or click Help for more info on common formula problems. 'Not trying to type a formula?'

Well I am so the rest is invalid. I'm sure people are rolling their eyes because this is obvious to them but like I said, I'm not good at Excel and I'm trying to figure this out for my boss. Thanks in advance for your help. Hello, I am interesting in purchasing the templates. Before committing, I processed an email to the email address copied below from the chandoo.org website, which was not recognized by gmail.

I also removed the '.d', and again, the email address is not recognized. Respectfully, I am trying to verify whether this purchase would be legitimate and the chandoo.org remains operational. Have more questions? Call me at +1 206 792 9480 or +91 814 262 1090 or E-mail me. I will be able to help you right away. I'm sorry that this is a message you did not expect to receive.

I'm a freelance audio producer from China and I came across your podcast on iTunes today. Do you need someone to produce your show for you so that you could free up your time and focus on creating great content? What I do is take your raw recording, edit out parts that you don't want, mix in your intro, outro, music, etc., normalize volume level and add ID3 tag.

My rate is 5 US dollars per episode and your first one is free 🙂 At the moment I have one client - Paul Kirch at the BOSS Academy Radio. Thank you for your consideration and I hope you have a wonderful day! Hi, I am trying to make a simple dashboard that I can customize and apply to several areas in the service we provide. I would like the NEEDLE type graphic.

For example: The Need for Long Term Care Insurance Needle points to red zone Do Not Get LTC Insurance Needle points to yellow zone: Consider LTC Insurance Needle points to green zone: You should strongly consider LTC insurance Needle points to black zone: You likely can self-insure for LTC The dashboard would read a set of data hat describes how many assets they have in their portfolio, an assessment of their age. It cold be this simple. The needle would point to the following ranges: age 65 (black) Assets $5,000,000 (black) How do I make this? I can apply the same logic to questions about their current and forecasted level of college savings, retirement savings, life insurance, and estate planning. Hi Chandoo, I'm a total newbie to your site. Glad I found it trolling the internet.

I am in management but it's been years since I've been in school. I am making do with my prehistoric Excel skills and am most interested in signing up for any Excel school that will help me further my career.

My question to you is: will your Excel school help me with the Why and When to use certain Excel functions? Most books and sites teach you the how. I really need to learn to understand the why and when. I know you are biased to your own program, but if it can't help me with what I'm looking for, I'm hoping you or your followers can point me in the right direction. Thanks in advance. I have excel files in different folder and all files under each folder has same file name however data inside is of different clients.

As an example I have folders A, B, C Under each folder I have excel file name “Recon”. In “Recon” file under folder A, I have data of client A. In “Recon” file under folder B, I have data of client B. As per my requirement I have to open each excel file and paste it in particular tab in one Master file. So, I will open Master file which has tab names as “A”, “B”, “C”.

So, client A data from its recon file I will copy and paste it in tab “A” of Master file. Is there a way I can automate this requirement? I am tired of doing transpose manually.looking for any help to do it using VBA code. I have the around 200k+ lines.and similar data i need to do it going forward as well.

Any help to is greatly appreciated. Actual data 123456 123456 123456 123456 123456 123456 22334455 22334455 22334455 22334455 aabbcc aabbcc aabbcc aabbcc 3344556677 3344556677 3344556677 3344556677 3344556677 3344556677 3344556677 3344556677 OUTPUT 1 2 3 4 5 6 22 33 44 55 aa bb cc 33 44 55 66 77. I am trying to read numbers in a text & number mixed cell so I can then use the number to calculate for another cell. Example: Mixed cell entry has a cabinet part number with width in it: 'W912L' I want to read the '9' as a number which represents 9 inches and divide it by 12 to get a linear footage result in another column. For this one I only need to read 1 number in the 1st position, but for other part number the width is more often 2 digits.

Ex: 'W1212L'.where the first 12 is the width of the cabinet that I want to read and then divide by 12 to get a linear footage result in another column. Can you tell how to do this? Chandoo, Just joined your newsletter and have downloaded Project + Portfolio Template Pack. I am going through the material now.

Although some functions/displays are of interest, I still desire a fair bit of customization to meet my portfolio and project management needs. I’m a busy project/program manager, so I was wondering if you offer any customization services?

The idea is for me to mark-up what I need (perhaps via PowerPoint), and you would help to create the template I need? If this is possible, let me know how you typically like to structure these engagements. Thanks in advance, Mark. DEAR CHANDOO, I NEED A TEMPLATE LIKE TO MAINTAIN A MINIMUM STOCK IN A STORE. I AM GIVING AN EXAMPLE: SUPPOSE WE ARE SUPPLYING COCA-COLA IN A RETAIL STORE. WE WANT TO FIX A RULE THAT THESE VARITIES OF STOCK TO BE MIANTINAED BY STORE COKE= 100C/S=10% THUMS-UP= 150C/S= 15% LIMCA=400C/S= 40% SPRITE=150C/S= 15% MAAZA= 200C/S=20% TOTAL 1000C/S. * IN MAZZA MINIMUM HE HAS TO BE MIANTAIN 20% BUT NOT BELOW 200C/S.

OTHER PRODUCTS CAN BE UP AND DOWN IN THAT% RESPECTIVE. PLEASE GIVE YOUR NUMBER. I WANT TO DISCUSS IT.

THANK YOU IN ADVANCE. I NEED A TEMPLATE LIKE TO MAINTAIN A MINIMUM STOCK IN A STORE. I AM GIVING AN EXAMPLE: SUPPOSE WE ARE SUPPLYING COCA-COLA IN A RETAIL STORE. WE WANT TO FIX A RULE THAT THESE VARITIES OF STOCK TO BE MIANTINAED BY STORE COKE= 100C/S=10% THUMS-UP= 150C/S= 15% LIMCA=400C/S= 40% SPRITE=150C/S= 15% MAAZA= 200C/S=20% TOTAL 1000C/S.

* IN MAZZA MINIMUM HE HAS TO BE MIANTAIN 20% BUT NOT BELOW 200C/S. OTHER PRODUCTS CAN BE UP AND DOWN IN THAT% RESPECTIVE. PLEASE GIVE YOUR NUMBER.

I WANT TO DISCUSS IT. THANK YOU IN ADVANCE.