This is a guest post by Daniel Ferry of Excelhero.com.
Have you ever wanted to fetch live stock quotes from excel? In this post we will learn about how to get stock quotes for specified symbols using macros.
One method that has worked well for my clients can be implemented with just a few lines of VBA code. I call it the ActiveRange.
An ActiveRange is an area on a worksheet that you define by simply entering the range address in a configuration sheet. Once enabled, that range becomes live in the sense that if you add or change a stock symbol in the first column of the range, the range will automatically (and almost instantly) update. You can specify any of 84 information attributes to include as columns in the ActiveRange. This includes things such as Last Trade Price, EBITDA, Ask, Bid, P/E Ratio, etc. Whenever you add or change one of these attributes in the first row of the ActiveRange, the range will automatically update as well.
Sound interesting, useful?
In this post, you can learn how to use excel macros to fetch live stock quotes from Yahoo! Finance website. It is also going to be a crash course in VBA for the express purpose of learning how the ActiveRange method works so that you can use it yourself.
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Background – Understanding The Stock Quotes Problem:
The stock information for the ActiveRange will come from Yahoo Finance. A number of years ago, Yahoo created a useful interface to their stock data that allows anyone at anytime to enter a URL into a web browser and receive a CSV file containing current data on the stocks specified in the URL. That’s neat and simple.
But it gets a little more complicated when you get down to specifying which attributes you want to retrieve [information here]. Remember there are 84 discreet attributes available. Under the Yahoo system, each attribute has a short string Tag Code. All we need to do is to concatenate the string codes for each attribute we want and add the resulting string to the URL. We then need to figure out what to do with the CSV file that comes back.
Our VBA will take care of that and manage the ActiveRange. Excel includes the QueryTable as one of its core objects, and it is fully addressable from VBA. We will utilize it to retrieve the data we want and to write those data to the ActiveRange.
Before we start the coding we need to include two support sheets for the ActiveRange. The first is called “YF_Attribs”, and as the name implies is a list of the 84 attributes available on Yahoo Finance along with their Yahoo Finance Tag Codes. The second sheet is called, “arConfig_xxxx” where xxxx is the name of our sheet where the ActiveRange will reside. It contains some configurable information about the ActiveRange which our VBA will use.
All of the VBA code for this project will reside inside of the worksheet module for the sheet where we want our ActiveRange to be. For this tutorial, I called the sheet, “DEMO”.
Writing the Macros to Fetch Stock Quotes:
Press ALT-F11 on your keyboard, which will open the VBE. Double click on the DEMO sheet in the left pane. We will enter out code on the right. To begin with, enter these lines:
Private rnAR_Dest As Range
Private rnAR_Table As Range
Private stAR_ConfigSheetName As String
Always start a module with Option Explicit. It forces you to define your variable types, and will save you untold grief at debugging time. In VBA each variable can be one of a number of variable types, such as a Long or a String or a Double or a Range, etc. For right now, don’t worry too much about this – just follow along.
Sidebar on Variable Naming Conventions
Variable names must begin with a letter. Everyone and their brother seems to have a different method for naming variables. I like to prefix mine with context. The first couple of letters are in lower case and represent the type of the variable. This allows me to look at the variable anywhere it’s used and immediately know its type. In this project I’ve also prefaced the variables with “AR_” so that I know the variable is related to the ActiveRange implementation. In larger projects this would be useful. After the underscore, I include a description of what the variable is used for. That’s my method.
In the above code we have defined three variables and their types. Since these are defined at the top of a worksheet module, they will be available to each procedure that we define in this module. This is known as scope. In VBA, variables can have scope restricted to a procedure, to a module (as we have done above), or they can be global in scope and hence available to the entire program, regardless of module. Again we are putting all of the code for this project in the code module of the DEMO worksheet. Every worksheet has a code module. Code modules can also be added to a workbook that are not associated with any worksheet. UserForms can be added and they have code modules as well. Finally, a special type of code module, called a class module, can also be added. Any global variables would be available to procedures in all of these. However, it is good practice to always limit the scope of your variables to the level where you need them.
In that vein, notice that the three variables above are defined with the word Private. This specifically restricts their scope to this module.
Every worksheet module has the built-in capability of firing off a bit of code in response to a change in any of the sheet’s cell values. This is called the Worksheet_Change event. If we select Worksheet from the combo box at the top and Change in the other combo box, the VBE will kindly define for us a new procedure in this module. It will look like this:
End Sub
Notice that by default this procedure is defined as Private. This is good and as a result the procedure will not show up as a macro. Notice the word Target near the end of the first line. This represents the range that has been changed. Place code between these two lines so that the entire procedure now looks like this:
The Heart of our Excel Stock Quotes Code – Worksheet_Change()
ActivateRange
If Worksheets(stAR_ConfigSheetName).[ar_enabled] Then
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
If Target.Column <> rnAR_Dest.Column And Target.Row <> rnAR_Dest.Row Then
PostProcessActiveRange
Exit Sub
End If
ActiveRangeResponse
End If
End Sub
That may look like a handful but it’s really rather simple. Let’s step through it. The first line is ActivateRange. This is the name of another sub-procedure that will be defined in a moment. This line just directs the program to run that sub, which provides values to the three variables we defined at the top. Again, since those variables were defined at the top of the module, their values will be available to all procedures in the module. The ActivateRange procedure gives them values.
Next we see this odd looking fellow:
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
All this does is check to see if the Target (the cell that was changed on the worksheet) is part of our ActiveRange. If it is the procedure continues. If it’s not, the procedure is exited.
The next line checks to see if the cell that was changed is in the first column or first row of the ActiveRange. If it is, the post processing is skipped. If the change is any other part of the ActiveRange, another sub-procedure (defined below) is run to do some post processing of the retrieved data, and then exits this procedure.
If the cell that changed was in the first column or the first row, the program runs another sub-procedure, called ActiveRangeResponse, which is also defined below. ActiveRangeResponse builds the URL for YF, deletes any previous QueryTables related to the ActiveRange, and creates a new QueryTable as specified in our configuration sheet.
That’s it. The heart of the whole program resides here in the Worksheet_Change event procedure. It relies on a number of other subprocedures, but this is the whole program. When a change is made in the ActiveRange’s first column (stock symbols) or its first row (stock attributes), ActiveRangeResponse runs and our ActiveRange is updated.
Understanding other sub-procedures that help us get the stock quotes:
So let’s look at those supporting subprocedures. The first is ActivateRange:
stAR_ConfigSheetName = “arConfig_” & Me.Name
Set rnAR_Dest = Me.Range(Worksheets(stAR_ConfigSheetName).[ar_range].Value)
Set rnAR_Table = rnAR_Dest.Resize(1, 1).Offset(1, 1)
Worksheets(stAR_ConfigSheetName).[ar_YFAttributes] = GetCurrentYahooFinancialAttributeTags
End Sub
Again, all this does is give values to our three module level variables. In addition it builds the concatenated string of YF Tag Codes required for the URL. It does this by calling a function that I’ve defined at the very bottom of the module, called GetCurrentYahooFinancialAttributeTags.
The next subprocedure is ActiveRangeResponse:
Dim vArr As Variant
Dim stCnx As String
Const YAHOO_FINANCE_URL = “http://finance.yahoo.com/d/quotes.csv?s=[SYMBOLS]&f=[ATTRIBUTES]”
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count – 1, 1).Offset(1))
stCnx = Replace(YAHOO_FINANCE_URL, “[SYMBOLS]”, Replace(WorksheetFunction.Trim(Join(vArr)), ” “, “+”))
stCnx = Replace(stCnx, “[ATTRIBUTES]”, Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
AddQueryTable rnAR_Table.Resize(UBound(vArr)), “URL;” & stCnx
End Sub
Notice that here we have variables defined at the top of this procedure and consequently their scope is limited to this procedure only. This means that we could have the same variable names defined in other procedures but those variables would not be related to these and would have completely different values.
Next notice that we have defined a constant. This is good practice, as it forces us to specify what the constant value is by naming the constant. I could have just used the value where I later use the constant, but then the question arises as to what is this value and where did it come from. Here I have named the value, YAHOO_FINANCE_URL, removing all doubt as to its purpose.
The next line is this:
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count - 1, 1).Offset(1))
and it deserves some explanation. Let me back up by saying that whenever we write or read multiple cells from a worksheet we should always try to do it in one go, rather than one cell at a time. The more cells involved the more important this is. Otherwise we pay a massive penalty in processing time. One of the best optimization techniques available is to replace code that loops through cell reads/writes and replace it with code that reads/writes all the cells at once. It can literally be hundreds to thousands of times faster.
Here we are interested in getting the list of all of the stock symbols in the first column of the ActiveRange. So how do we get them in one shot? We use something called a variant array. Notice that we defined vArr at the top of this procedure. A variant array is a special kind of variable that holds a list of values and it DOES NOT CARE what variable types those values are. This is important when retrieving data from a sheet because the data could be numbers, text, Boolean (True or False), etc. Variants are powerful, but they are much slower than other variable types, such as a Long for numeric data for example. However, in the case of retrieving or writing large chunks of data from/to a sheet the slight penalty of the variant is dwarfed by the massive increase in the speed of data transfer.
It’s very simple to retrieve range data (regardless of the size) into a variant array. All you do is:
v = range
where v is defined as a variant and range is any VBA reference to a worksheet range. And magically all of the values in that range are now in v. Note that v is not connected to the range. A change in any of v’s values does not propogate back to the range, and likewise a change to the range does not make it’s way to v all by itself. v will ALWAYS be a two-demensional array. The first dimension is the index of the rows, the second dimension is the index of the columns. So v(1,1) will refer to the value that came from the top left cell in the range. v(6,9) will hold the value that came from the cell in the range at row 6 and column 9.
For most circumstances this two-dimensional format is fine. But we are only retrieving one column of stock symbols. The procedure will still give us a two-dimensional array, with the column dimension being only 1 element wide. This is a shame because VBA has a wonderful function called Join that allows you in one step (no loop) to concatenate every element of an array into a string. You can even specify a custom string to delimit (go in-between) each element in the output string. The problem is that Join only works on single dimensioned arrays 🙁
But there’s always a way, right? We can use the Application.Transpose method on the 2-D array and presto we get a 1-D array. The rest of the line just specifies what range (the stock symbols) to grab.
The next two lines are:
stCnx = Replace(YAHOO_FINANCE_URL, "[SYMBOLS]", Replace(WorksheetFunction.Trim(Join(vArr)), " ", "+"))
stCnx = Replace(stCnx, "[ATTRIBUTES]", Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
Again a handful, but all we are doing here is replacing the monikers, [SYMBOLS] and [ATTRIBUTES] in the YAHOO_FINANCE_URL constant with the list of stock symbols (delimited by a plus sign) and the string of attributes.
In the final line of the procedure:
AddQueryTable rnAR_Table.Resize(UBound(vArr)), "URL;" & stCnx
we are running another subprocedure called, AddQueryTable and we are telling it where to place the new QueryTable and providing the connection string for the QueryTable, which in this case is the YF URL that we just built.
Nothing unusual happens in the AddQueryTable sub. It just deletes any existing AR related QueryTables and adds the new one according to the options in the configuration sheet.
The PostProcessActiveRange sub is interesting:
If rnAR_Dest.Columns.Count > 2 Then
Application.DisplayAlerts = False
rnAR_Table.Resize(rnAR_Dest.Rows.Count).TextToColumns Destination:=rnAR_Table, DataType:=xlDelimited, Comma:=True
Application.DisplayAlerts = True
Worksheets(stAR_ConfigSheetName).[ar_LocalTimeLastUpdate] = Now
End If
End Sub
Processing Yahoo Finance Output using Query Table & Text-Import Utility:
As mentioned before the data from YF comes back as a CSV file. The QueryTable dumps this into one column. If you were only retrieving one attribute for each stock this would be fine as is. However, two or more attributes is going to result in unwanted commas and multiple attribute values squished into the first column of the QueryTable output. Unfortunately this is poor design by Microsoft, especially when you consider that the QueryTable does not behave like this when it is retrieving SQL data or opening a Text file from disk. You can actually specify this operation to be a text file and it will properly spread the output over all of the columns. To do so, you specify the disk location as being the URL of the YF CSV file, but as Murphy would have it, it’s unbelievably slow and pops up a status dialog as it slowly retrieving the CSV. Using the URL instruction instead of the TEXT instruction at the beginning of the connection string is incredibly fast in comparison, but dumps all of the data into the first column.
So what to do? We’ll just employ Excel’s built-in TextToColumns capability and bam, our data is where we want it.
Our finalized stock quotes fetcher worksheet should look like this:
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Final Thoughts on Excel Stock Quotes
The ActiveRange technique is quite versatile. It can be implemented with other data sources such as SQL, or even lookups to other Excel files, or websites.
In this example it provides a nice way to easily track whatever stocks you may have interest in and up to 84 different attributes of those stocks. You can enable and disable the activeness of the ActiveRange on the fly. You can set the AR to AutoRefresh the data at periods that you set or to not refresh at all.
This is a basic implementation. For example, changing the AutoRefresh setting will have no effect until a new QueryTable is built. That won’t happen until you also add or change a stock symbol or add or change an attribute. An easy enhancement would be to add a little code to the arConfig_DEMO code module to respond to changes to the ar_AutoRefresh named range cell.
Another enhancement would be to eliminate the slight flicker of the update by moving the QueryTable destination to the arConfig_DEMO and then doing the TextToColumns with the destination set to the DEMO sheet. In an effort to simplify this tutorial I have left these easy enhancements as an exercise for you to implement.
Have a question or doubt? Please Ask
Do you have any questions or doubts on the above technique? Have you used ActiveRange or similar implementations earlier? What is your experience? Please share your thoughts / questions using comments.
I read Chandoo.org regularly and will be monitoring the post for questions. But you can also reach me at my blog:
Further References & Help on Excel Stock Quotes [Added by Chandoo]
- Fetching Stock Quotes using Research Pane
- Stock Portfolio Tracker using Google Docs
- QueryTable Object Model & Properties
- Using QueryTable to Generate Dynamic Reports
- Yahoo Finance API Documentation & Example
Excel Hero is dedicated to expanding your notion of what is possible in MS Excel and to inspiring you to become an Excel Hero at your workplace. It has many articles and sample workbooks on advanced Excel development and advanced Excel charting.
132 Responses to “Beyond If and Sum, 15 really useful excel formulas for everyone”
Great post. I had never heard of the CHOOSE function. That one is going in my toolbox immediately. I don't think there's anything more difficult than nesting IF statements in the Excel formula bar. At least with 2007 they made the bar drop down so you could usually see the whole thing if it was a big one.
Love your blog, been reading for a few months but this is first comment. Keep up the good work.
timetaking but worth reading, liked it very much........
thanks for the help
pls send me link
Another useful one for processing cell contents:
If you concatenate text and cells containing dates, the date is usually passed to the cell as a numerical value. Use the TEXT function to pass the date as a text string, using the date format of your choice...
="Date is: "&TEXT(A1, "dd/mm/yyyy")
1. TRIM also converts excess spaces within a string to a single space, which is very handy with imported data:
TRIM(" abc def ") becomes "abc def"
2. Some functions require the Analysis ToolPak, including RANDBETWEEN, CONVERT, and WEEKNUM.
[...] Beyond If and Sum, 15 really useful excel formulas for everyone (tags: excel totw) [...]
@Ben.. thanks, welcome to commenting, I think this is the best place as more good ideas come out of this than the posts often... 🙂
@Sam.. that is a good tip, may be I will include it in the next issue of 15 functions
@Jon, I didnt know that about trim, thanks for pointing.
Yeah, analysis tool pack is required for few of those functions. I have had it on forever, so didnt realize that. 🙂
chando dont reply so much it waste our time
we are in era
Re: Tip #5 (date &/or time)
You all may already know this, but an easy way
to enter the current date in a cell is Ctrl+; then enter, and to enter the current time in a cell type Ctrl+Shift+; then enter.
It is very useful.. thanks..
[...] on names and text formulas: Find word count using excel formulas, 15 excel formulas for everyone, Generate tag clouds using VBA. Categories : Excel Tips | ideas Tagged with: Excel Tips | [...]
[...] 15 Excel Formulas you may not know [...]
Tip #7 for Zip Codes:
=TEXT(A1,"00000")
@Richard... thanks for that. 🙂
@Jon .. This is sweet, thanks very much. Often I use the Rept() way of doing this.. I am sure TEXT() is easier to use when you know the format up front.
[...] SMALL() excel spreadsheet formula is used to sort a list of numbers and fetch nth smallest number in a given [...]
[...] public links >> sorting Beyond If and Sum, 15 really useful excel formulas for everyone First saved by viimmy | 1 days ago sorting out christmas lights First saved by phipsi180 | 23 [...]
[...] Beyond If and Sum: 15 very useful microsoft excel formulas for everyone | Pointy Haired Dilbert - Ch... - [...]
i'm making up a form for a friends business and on a second copy of that same page i using a formula to copy data in particular cells. the problem i have is that i'm getting a bunch of "0" ont he copy if there isn't data on the first form. how can i get rid of that "0"?
@Bill.. Welcome to PHD. Thanks for asking the question.
When you use references you can wrap it in an if clause.
For eg. instead of saying =Sheet1!A2
you can write: =if(Sheet1!A2="","",Sheet1!A2)
that way, when ever the reference is empty you will force excel to show empty space instead of ZERO.
how can we work in ofice on excel plzzz next month i will join my job help me
[...] 1. Become a Conditional Formatting Rockstar with these 5 tips 2. Excel can be Exciting - 15 fun things you can do in MS Excel 3. 25 Free excel downloadable templates and workbooks 4. 73 high quality excel chart templates, download and make awesome charts 5. 15 Excel formulas you must learn [...]
@Bill / Chandoo :
You may untick the zero values from menu==>tool==>option==>view
[...] suck, convert your Pinyin to unicode, good tips for web developers, charts for Javascript users, good functions for Excel users, categories for computer science (this is [...]
Chandoo:
I think NA is for "Not available". (Tip 15).
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
Nice Chandoo 😀
Chandoo 🙁 need your help 🙁
am having an excel sheet it is actually a request sheet that provide an ID.. this ID I have to formlize it each day i want to ask for a request... the problem I just hate this way I need to find a way to make this ID automatically appear with a new serial number each time I add new sheet!!!!
note the ID number has to be formlized by this way: ddmmyy/###
how could I do it Chandoo?? 🙁 need your help :'(
@Loula.. thanks and welcome to PHD. Let me see if we can help you.
automatically incrementing the number whenever you use the formula is possible through circular references. Even automatically getting the current date while keeping the old date values intact uses circular references in formulas. These are slightly complicated formulas and hence I don't recommend them for day to day uses.
A better solution could be to use macros, write once and run whenever you need a new ID to be generated and pasted in the current cell. Let me know if you are interested, I can either help you on this as a consulting engagement or provide you some general guidelines through comments.
I am sorry, but I dont know anything else that is better, may be one of our readers do...
Chandoo, I need your help too. I downloaded the gauge sample and do not know how to replicate it. How did you make the pointer? I am pretty sure I can figure out the arch, but the pointer I am lost. Awesome site!!
Yes Chandoo it seems to be better than the way that I do 🙁
thanks alot Chandoo and wait your guidlines 🙂
@Bobby: I suggest you to read this article : http://chandoo.org/wp/2008/09/09/excel-speedometer-chart-download/
and see the sample download provided there. If you still have any doubts, feel free to ask me.
@Loula: Are you looking for some general info on circular references? if so, try this: http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/
If you need a more specific solution, drop another comment, I am sure one of our readers or me can respond with a good answer.
Yes I got some example,, but till now I could not find a way to write my forumla 🙁
I could do the date format but how could I put the serial number that comes automatically whenever I add new sheet??!!
@Loula: hmm.. there are few ways you could do that. (1) obviously using VBA to autogenerate a sequence number and place it cell, say B3 whenever a new sheet is added.
(2) using CELL() function in each sheet, CELL("filename") would tell you the entire file path along with sheet name. Then extract the sheet name using a formula like, =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))). Now use this name to derive the sequence number. If you are using default naming structure, then your sheet names would be sheet 1, sheet 2, sheet 3, ... , sheet 99 etc.
Let me know if you need further help 🙂
I'm kinda new to Excel and I need some help with a formula.
I need to calculate Drive time and actual work time in units if 1 unit=5 minutes.
@Paul: Welcome to Excel and PHD... 🙂
You can do that by using a formula like: =A3/5, assuming A3 has drive time in minutes, wherever you enter this formula it will give you the units
use the same logic for calculating work time. You may want to check our excel formula help section: http://chandoo.org/wp/excel-formula-helper-index-cards/ to learn more about formulas in plain English.
Hi
i want to learn MS Excel please help me
Chandooooo :'(
I tried many ways to do it but really couldn't do it 🙁
please if you do not mind give me step by step
sorry for bothering you 🙁
@Loula.. Sure, give me sometime... (I am sorry, but I am in between few important things and cant really take out time... I do appreciate your patience.. If any of our commenters can help Loula, I will be very thankful to you..)
Hi loula,
I am replying to this because I had a similar problem and couldn't find the answer. So i looked on the help pages in excel. I feel a bit daft not getting it, but who knows anything until you know it? We all have a time of not knowing...
What I didn't get was that you had to put the formula in separate cells from the data you are changing. So if your data is in cell A2, you could put this formula in cell B2: =PROPER(A2) to change "I am not daft" to "I Am Not Daft"
I thought this very simple but important step was worth sharing.
In Cell A1 I Typed :
=PROPER("thank you chandoo very much")
and It displayed
Thank You Chandoo Very Much 🙂
@Al... thank you so much.. that is soo sweet.. 🙂
Ok wait you to finish 🙂 thank you
@Loula: I feel very unfortunate for not being able to help you on time. I have moved to Sweden from a Sunny place in India and found myself with very limited internet access and extremely busy work schedule. I am not sure when I will be able to get back to you... but if you find a solution, share it with us.
Eternal glory awaits you...
Hi - complex issue I think: I have an Xcel doc with multiple worksheets for weeks of the month. It is a timesheet. I want to summarize total time in the month (pulling from multiple worksheets) based on a project number - which is variable, but always in the same column on each worksheet. The project number is in column D, which is totaled into column M for each weekday entry. The biggest stumbling block I see is the project number will not always appear on the same row. So I want a consolidated sum from column M where column D equals a specific text/numerical string from multiple worksheets. Does this make sense? Is it possible? Thanks!
Hi Sharon,
you may download RDB addin to. it help you out.
Hy guys. I am new to excel too.
Lets say there is a column which has 1 or more names, separated by commas. My requirement is that in the next column, a count of number of names is displayed.i.e. one can count the number of commas. I am unable to implement this.Please help.If you have any other suggestion to count,then also please share it.
@Sharon: Welcome to PHD and thanks for asking a question. I have taken an extended easter break to catch up on few things at home and now back online 🙂
coming to your question, yes, you can use excel to solve your problem. You need to use 3d references and sumif(). a 3d reference refers to same range across several sheets. See this: http://chandoo.org/wp/2009/02/04/satisfaction-surveys-excel/ and http://chandoo.org/wp/2008/11/12/using-countif-sumif-excel-help/ for more help.
I am not giving step by step instructions as it is a peculiar problem. But I am sure you can put the pieces together and solve this. Let me know if you hit any road block.
@Varun: Welcome to excel.
You can count the number of commas (or any other symbol) in a cell using this formula: =len("a,b,c,d")-len(substitute("a,b,c,d",",",""))
so, if you want the number of names in the cell, just add 1 to the above formula.
Let me know if you have some problems implementing this.
@Chandoo.
Thanks for the help mate. With a little modification , it worked.
=IF(J423"",LEN(J423)-LEN(SUBSTITUTE(J423,",",""))+1,0)
However, as my motive is to count the no. of words, it'll give an extra count,if a comma is inserted at the end.
Thanks again.Will be bugging in the future as n when new issues will pop.
[...] 15 very Useful Excel Formulas for Everyone [...]
Chandoo,
Just found this excellent blog, and have already incorporated a couple ideas into my work.
One small note on the example used in #7 - I believe it should be =REPT("0",5-LEN(zipcode)) & zipcode and not
=zipcode & REPT("0",5-LEN(zipcode)) as you want to pad the start of the string and not the end.
thanks again.
Hi
I am looking for an easy formula to replace the If function. I need to find the highest number in a series then add the data in the column to the right. The IF function works for this, btu I can only add 7 points for it to check. I have around 20 data points. Can you help
Thanks
Nichola
@Nichola... Welcome to PHD.
You can use MAX formula to find the largest value in a range of cells. You can mix this with Vlookup to find the value in adjacent cell like this.
Assuming the values are in the range A1:B20, with column having values and column B having related data, write the formula,
=vlookup(max(A1:A20),A1:B20,2,false)
Learn more about vlookup here: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
Thanks Chandoo. The thing is, the values are in one row (B3:B25). I have 200 rows of data that I need to seperately look at (find the highest value and it's corresponding data). The highest value is not always going to be in the same column.
Thanks
Nichola
Hey... brilliant. I didn't know about the CHOOSE function at all - I usually end up going with hidden lists combined with vlookup functions, or sumifs instead. Nice. I'll find a way to use this at work.
First time commenter, btw.. thank you thank you, pat on my back. I've been going through all your older posts just to find gems like these that I might've missed. My search has not been in vain so far. Thanks for all the great work!
@Nichola... if you have values in a row instead of column, you can use HLOOKUP function in the same way
@Arti... That is sweet. Thank you 🙂
The Weeknum function is not correct if your country uses the ISO8601: 2000 weeknumbersystem, like we do in Europe.
Today is week 4 for us. Excel's weeknum returns week 5.
You can correct this, by using the following Formula:
=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)
The date is in cell b4.
Found this on:
http://www.rondebruin.nl/weeknumber.htm
[...] 15 really useful Excel formulas for everyone — kleine Helferlein abseits des von =SUMME … [...]
Hi Chandoo, you have been able to help me in the past - and now that something has been bugging me for nearly a week I know there was only one place to go - you!!
I have created a table based on months of a year (in columns) bringing back a value of trade which commences in that particular month - what I need to be able to do it to populate the continuing months (row entries)in the Table with that entry if it is found? I was trying to do a kind of IF(CELL=FALSE,"FALSE","The cell value") this just produces all False values or if I try to do it all together with creating another "table" to create the If statement in, then it is just circular......
HELP!!!!!
@Em.. thank you for your appreciation 🙂
I am unable to understand your question.. can you give some more details?
Hi, Yes sorry it is rather confusing - My table has months in columns then customer names in rows, My aim is to sum up the total value of expected trade for the year by Customer by month - so far I am able to return the monthly value of trade (via a look up)in the cell relevant to the month that the customer has said they will start to trade but this value actually should be also be entered in to all the subsequent month columns also? That is what I am struggling with - any ideas? Thanks as always Em.
@Em.. you mean if the monthly value for Jan is 20, Feb, Mar etc. should have this 20 added to whatever they already have?
In that case, you can use a formula like
=vlookup(monthly value...) for Jan
=vlookup(monthly value...) + previous month's value for Feb onwards
Can anyone tel me, how can i convert a list of call duration stated in time format(i.e 05:36:45) into number of units. 60 second or part is one unit. given example should return with 337 units ,
@Ashok
Try : =HOUR(M1)*60+MINUTE(M1)
Make sure you format the cell as Decimal after you enter it ie: Click the , button
Change M1 to suit
Hui... I tried your formula above and only got 336 and I am by no means even close to an expert.... not even close, and this may be too much extra, but I used your formula and changed it a bit to get the 337....
see if this works: =ROUNDUP(HOUR(M1)*60+MINUTE(M1)+(INT(SECOND(M1))/60),0)
dear friends,
can any one tell me how to find consoled number if there are so many cells with number suppose IN cell A1 11, A2 10, A3 20, A4 50, A5, 60 AND A6 11 and i have to find which two cell number sum together and make 22 than how to solve through formula please send me on email
@Manoj
have a read of http://www.dailydoseofexcel.com/archives/2005/10/27/which-numbers-sum-to-target/
Dear Friend ,
I used one column in fraction and other column in value (Eg:Rs.1500.50 is entried one cell in 1500 and another cell 50 ),So I Summed the Fraction its above 100. So I want two neumeric in fractioned cell and if Greater than and equal to 100 I want to add Rs.1 to another Cell.
Please help me , I Want a formula in Excel.......Pls.........
Dear Friend
Whats the formula used to count the Even or Odd no.
I have a 7 column, each colum contain evev or odd number, I want to count In colum no 8 or K(for Even) and 9 or L (For Odd) how many number are Even or Odd in one row A5:J5
Example 3 18 34 41 45 46 37 = even Count 3 and odd count 4
Thanks
@Malik
Try: =SUMPRODUCT(1*(A5:J5=ODD(A5:J5))) for Odds
and =SUMPRODUCT(1*(A5:J5=EVEN(A5:J5))) for Evens
.
even though there is only enough data to go to G2?
Sir,
I want to highlight Odd Number and Even Number by Conditional formatting
Please help on this
@Atish
Select the range
Goto Conditional Formatting
Apply a New CF
Use a Formula
=isodd(A1)
select the format
Apply
Repeat using =iseven(A1)
Thanks
Another function that I also find useful is the "VALUE" function which turns numbers stored as text into numbers. I find this useful when I'm exporting figures from our accounting system which automatically exports as "### " numbers (with a space(s) as the last character).
If "### " is in A1 then I just type in =VALUE(A1) and it returns "###" with the default number formatting. Then I use this data to manipulate and do whatever I need with it.
Hope this makes sense and is useful for other people, too.
chandoo i want the explanations of sumif,countifand vlookup ,hlookup with examplesi can do and put formulas but i dont understand the logic in easy way. i want u to pls send me logic in easy way. especially with spreadsheet example.
It was very useful , thanks.
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
So very glad that I found this website. I had been trying to do a pmt function, but every time I tried, it returned the result as a negative amount when I needed it positive. I am in college and it was part of one of my assignments. After I found your website I was finally able to complete my assignment. Thanks for your help!
[...] To get nth largest number in a range, use =large(range,n)… Get Full Tip [...]
Thanks for the great information! I have been searching for a solution to an excel problem, and yet I still cannot seem to figure this problem out:
I have 2 columns: One column displays a Data Validation pull-down table. The second column is where the user can enter a dollar amount. I am trying to figure out a way that the forces the entered dollar amount to become a negative number, based on the conditions from the pull-down menu. Is this possible? I have tried formulas, using conditional formatting, but nothing seems to work...
Here is an example of it
COL A COL B
WITHDRAW $200
I want the "$200" to become "-$200", when the user chooses "WIHDRAW" from column A...thanks in advance for any advice on how to solve this, without learning VBA
Excellent guide, thank you!
hello
can you help me
i want to extract record from other sheet between two dates and a criteria in excel 2007 with formula functions
thanks a lot for your advice
Noviar Rizal
can U pls tell me a short cut key for format painter ?
I need formula that can count the days.
Example: 02-02-2011 & Current date is 29-07-2012.
How many days has been done.
Please advise
Thanks
Ahmed
If
A1: 2/2/2011
A2: 29/7/2012
Simply use =A2-A1
If you want to exclude weekends then use
=NETWORKDAYS.INTL(A1,A2,1)
[...] 3. To get nth largest number in a range, use =large(range,n)… Get Full Tip 4. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
Thank you for this great post!
Hi everyone. Please help with this. I have been tried to find this entirely on Net. Bt i did not fine that.
Actually i have a spreadsheet attched with other sheet.. and i enter value in 1st sheet and its bring the result in 2nd sheet. bt something results are negative number. in cell let say in Cell a1 has some formula and some time result will be change in negative numbers. Once the results automatic change. i want pop-up message instantly. please help me wtih that. Mr. chandoo. please..
Thank you
A great cheat sheet - I use excel loads and always finding new things, thanks
[...] 28. To get nth largest number in a range, use =large(range,n)… Get Full Tip 29. To get nth smallest number in a range, use = small(range,n)… Get Full [...]
[...] via Beyond If and Sum: 15 very useful microsoft excel formulas for everyone | Chandoo.org – Learn .... [...]
I have Excel 2008 for Mac, and somehow I have lost my Excel "Help" file... How can I find whether a text string contains a specified smaller string?
Example: for the larger string "ABCDEFG," the required function would return TRUE for "DE" but FALSE for "DF."
hi!
May I ask if what is the formula to keep the countdown running each day?
I have a workbook with a summary page and a sheet for each day of the month. Each day's sheet has data with units of product sold and sales values. My Summary sheet collects totals for each unit of product sold into a stock control sheet. My Problem. Not everyday is a sales day, so I need to collect the last date that sales were made ie the first sales were made on the fourth of the month and the second sales were made on the 7th.
I need my stock summary report to show that the latest sale was made on the 7th.
Any ideas will be appreciated
Plz. help me about the following problem-
A,B,C,D are 4 employee get salary as 20000, 22000, 40000, 35000 tk.
Income tax rate 0% when salary below 20000; rate 5% for 20000 to 35000 and 10% on above 35000.
How can I calculate it in excel.
@Fayez
Assuming your salary is in B2 I'd use:
=IF(B2<20000,0%,IF(B2<35000,5%,10%))
how to extract name and number
Reliance A/c 100000117503457
use FIND to find the position of each blank space, then LEFT and RIGHT using those values.
[…] how to effectively survive spreadsheets, then sharpening your technical aptitude by learning 15 Really Useful Excel Formulas and an extensive list of excel keyboard shortcuts will help with speed and […]
Hi Chandoo. This is a great site. I had an issue. Im using sumifs to calculate some things but i basically am doing a sum of all the sumifs as one variable in the sumifs calculation always pulls from the same column. Is there a formula i can write which i would be easier?? Thanks for any help you can provide.
@Oz
Can you post the question in the Forums and include a sample file:
http://chandoo.org/forum/
? I have my sheets named day 1, day 2, day 3, I have formulas on day 3 that go back to day 2 to bring it to day 3, but all my formulas but when I copied day2 to make day3. I have to go into formulas and manually change the dates expl. ='DAY 2'!E5:F5 I have to go and change the 2 to a 3. Help
@Michael
The simplest is to keep an index number on every page say in a1 which identifies what day the sheet is
You can add them manually or using a formula like
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))-4)
Then in your cell you can use the Indirect Function to build the previous worksheet
say you are on Day 2 and need the formula to refer to Day 1
In cell A1 you will either have the number 2 or the above formula which will return the Number 2
Then elsewhere you can use eg:
=SUM(INDIRECT("'Day "&A1-1&"'!E5:F5"))
Hi Chandoo, thanks for your great work! I'm looking for an effective method to calculate the maximum numbers of outages in any day of the year. The data set is by year and contains unique work orders with a start date and an end date. I need to know the count of the day that had most outages. Appreciate any help!
@Andreas
Can you ask the question at the Forums and supply a sample file of say 50 or 100 records
http://chandoo.org/forum/
sorry again, I read too fast and didn't pick up on "start date and end date." Hope to post a new method shortly.
This works, but probably there's a much more elegant way:
Put year in Column A, month&day in Column B. Sort on B. Put a blank row before the first row. In C2(or whatever is the first row of your data), type "=IF(B2=B1,C1+1,1)" and copy that down. Select Column C; do a COPY and then PASTE (in same place), using "Values Only." Sort everything on C, descending. The day(s) with the most outages will be in the first row(s).
Dear Sir,
Greetings. I wish to be registered for free Excel Formula and guide if any, i.e., without subscription charges.
Thanks and Regards,
sir formula ISERROR(1234*0=43)is being shown as FALSE???
@Nisha
That is correct
The functional part of the formula 1234*0=43 returns a value of False as the left side of the = sign doesn't equal 43
But the value False isn't an error, it is just a Boolean value of False
So the Iserror() function now is the same as Iserror(False)
As False isn't an error, the function returns False saying that the contents aren't in error.
The function =Iserror(30/0) returns True because the internal part evaluates to an #DIV/0 error
and so the Iserror() function sees an error and returns True
HI,
I have a data total working days in each quarter & one of the date consist 2 quarters working days. (Ex- Q2 & Q3 includes 40 working days, in that I need to need to know Q2 working days & Q3 working days) is there any formula to separate the working days on each quarters. if there please guide me.
Being a designer It was always a quest for me to make excel reporting now its much easier thanks for the tips and tricks sir !
It is nice to learn thanks
This is really awesome. Thanks so much for sharing.
Great Help. Amazing tips. 🙂
Hi Chandoo..how to save the long IF formulas to avoid repetitive typing?
I have number of sheets as part of of my total project estimate which consists of many project and those I summarize to a page by putting the numbers on the summary sheet but for path directrory when i put it on one sheet ( i,e,=+cell('filename"), the detail pops up on all sheet as the first project at present i populate the project by thsis formula and then copy and paste value so that it becomes a text .
I do not know whayt i should do so that I can give the directory path to individual estimates clubbed as different excel sheet in the book
Can anyone figure this out:
This formula works perfectly:
=IF(A1+B1+C1<1,0,((2.6)+((A1+B1+C1)-(1))*0.25))
I want to change it and use A1:C1 (instead of A1+B1+C1) so I don't have to manually revise the formula every time I insert another column between A and C.
However, it no longer works if I simply substitute A1:C1, as follows:
=IF(A1:C1<1,0,((2.6)+((A1:C1)-(1))*0.25))
What am I doing wrong? I've tried SUMIF and SUMIFS, but still can't get it to work.
TEST RESULT:
If the value for A1, B1, and C1 are each 1, the result should be 3.10
If the values are 0 for each, the result should be 0
Use SUM(A1:C1) instead of A1:C1
It worked! Thanks so much.
hi all,
i have a spreadsheet where i capture data regarding my water, electricity and sanitation usage per month. everything works fine and my data is quite accurate but, now i want to add a feature where i can estimate an amount for each category e.g. if my water usage for the month was 23KL of water the cost will be R XXX,XX. This seems quite easy if you have the price per unit but, my problem comes in when i realized that my SP charges me a different price for different categories of water e.g.
for units 0-6KL of water i am charged 6KL @ R X,XX per KL
for units 7-12KL of water i am charged 6KL @ R XX,XX per KL
for units 13-18KL of water i am charged 6KL @ R XX,XX per KL
for units 19-24KL of water i am charged 5KL @ R XX,XX per KL (ALTHOUGH I HAVE NOT USED THE FULL 6KL OF WATER BUT ONLY 5)
THESE TOTALS ARE CALCULATED TOGETHER TO GIVE ME A TOTAL COST FOR THE WATER USAGE.
my question is: I there a way to breakup a total of 23KL usage into four different total: 1st 6, 2nd 6, 3rd six and the rest (5) so that I can add the price per unit for each and calculate the estimated cost for the total if it was calculated i the manner above.
Hello Brandt;
I prepared an Excel worksheet to solve your problem. Please download the Excel workbook file from the following URL:
http://s000.tinyupload.com/?file_id=49965535413976665648
I created a Lookup Table (as you'll see inside the Excel file) where the leftmost column begins with the value zero and continues with the lower ends of the ranges of usages (in your numerical example above, these would be 0, 7, 13, 19, 25, etc.) I assumed that there are 10 price categories with the last category applicable for a volume of water usage of 55 KL or above.
The second column in the table contains the applicable price per KL. The third column (titled 'Base Total Price' in my worksheet) shows the base price payable. Its formula is: =IF(ISNUMBER($C5), $B5*($A6-1)+$C5,0). For example, for a water usage of 23 KL, you lookup 23 in the table with approximate match, locate the row starting with 19 KL, and retrieve from that row the base total price of $138. Why $138? Because (6 KL * $5 per KL + 6 KL * $8 per KL + 6 KL * $10 per KL) = $138. I made up all the unit water usage prices in each range.
Finally: The amount billed is calculated with the formula =IF(Usage=0,0,VLOOKUP(Usage,LookupTable,3)+VLOOKUP(Usage,LookupTable,2)*(Usage-VLOOKUP(Usage,LookupTable,1)+1)).
The formula may seem a little bit too long, thus intimidating. However, it is nothing but simple algebra.
For your example water usage amount of 23 KL, it first locates the 4th row of the LookupTable starting with the amount of usage "19" KL; retrieves the base price $138 from that row; adds on top of that the difference between the actual amount of usage (which is 23 KL) and the upper end of the previous price range (namely: 23 - 19 + 1 = 5 KL) times the applicable unit price (namely $12). Hence, the amount billed is $138 + $12*5 = $198.
You can modify the lower ends of the water usage ranges with different unit prices. The only assumption in my worksheet is that the water usage got to be an integer number and not a fractional amount.
Hope this covers your need. Best regards & happy Excelling! - Deniz
Hi Deniz,
Thank you very for the help. It is appreciated very much. Although I haven't had time to actually look at the spreadsheet, it does make sense if I read you reply. I will test you spreadsheet and if it work integrate it with my tables. I will let you know if I struggle but I usually win with the help that I get on Chandoo.org
Regards
Thank you Deniz Hocam =)
Have you thought about incorporating some social bookmarking buttons to these blogs. At least for twitter.
Dear Sir,
We have one problems please help on this
We Have one rage like
100
150
200
250
100
300
350
100
and than we using this =SMALL(C1:C8,3) result is showing 100
Sir Please help on this
The SMALL function is working properly, since there are three instances of 100 in your range: If you sort the values in your range, the third value from the "small" end is definitely 100.
Thanks @workingonit
Hi
These days i want learn MS access so pls tell me all access web site in Hindi and English.
Dear All,
I have one problems pls help on this
Convert the ff hire Date in Date Format using Formula
Of hire Date Desired Output in Date Format Solution (Using Formula)
Feb 15 2016 15-Feb-16
Oct 17 2016 17-Oct-16
Jul 15 2016 15-Jul-16
Jun 16 2016 16-Jun-16
Apr 30 2017 30-Apr-17
Mar 19 2017 19-Mar-17
Jan 10 2017 10-Jan-17
Thanks a lot iam a excel trainer in active excel microsoft excel certifications in india please contact us
Hello chando,
I need your support,like I am from logistics in my company I need to go through excel data day to day in my life, I want to learn to make my work easy in maintaining data free work.
I don't know how to start from were to start.whether power BI or VBA or excel crawlers like which all this make me easy in my day to day work. I am ready to join has a student to learn.
Please suggest me and I will be waiting for your help.