Excel formulas can always be very handy, especially when you are stuck with data and need to get something done fast. But how well do you know the spreadsheet formulas?
Discover these 15 extremely powerful excel formulas and save a ton of time next time you open that spreadsheet.
1. Change the case of cell contents – to UPPER, lower, Proper
Boss wants a report of top 100 customers, thankfully you have the data, but the customer names are all in lower cases. Fret not, you can Proper Case cell contents with proper() formula.
Example: Use
proper("pointy haired dilbert")to get Pointy Haired Dilbert
Also try lower() and upper() as well to change excel cell value to lower and UPPER case
2. Clean up textual data with trim, remove trailing spaces
Often when you copy data from other sources, you are bound to get lots of empty spaces next to each cell value. You can clean up cell contents with trim() spreadsheet function.
Example: Use
trim(" copied data ")to get copied data
3. Extract characters from left, right or center of a given text
Need the first 5 letters of that SSN or area code from that phone number? You can command excel to do that with left() function.
Example: Use
left("Hi Beautiful!",2)to get Hi
Also try right(text, no. of chars) and mid(text, start, no. of chars) to get rightmost or middle characters. You can use right(filename,3) to get the extension of a file name 😉
4. Find second, third, fourth element in a list without sorting
We all know that you can use min(), max() to find the smallest and largest numbers in a list. But what if you needed the second smallest number or 3rd largest number in the list? You are right, there is a spreadsheet function to exactly that.
Example: Use
SMALL({10,9,12,14,26,13,4,6,8},3)to get 8
Also try large(list, n) to get the nth largest number in a list.
5. Find out current date, time with a snap
You have a list of customer orders and you want to findout which ones are due for shipping after today. The funny thing is you do this everyday. So instead of entering the date every single day you can use today()
Example: Use
today()to get 08/13/2008 or whatever is today’s date
Also try now() to get current time in date time format. Remember, you can always format these date and times to see them the way you like (for eg. Aug-13, August 13, 2008 instead of 08/13/2008)
6. Convert those lengthy nested if functions to one simple formula with Choose()
Planning to create a gradebook or something using excel, you are bound to write some if() functions, but do you know that you can use choose() when you have more than 2 outcomes for a given condition? As you all know, if(condition, fetch this, or this) returns “fetch this” if the condition is TRUE or “or this” if the condition is FALSE. Learn more about spreadsheet if functions like countif, sumif etc.
Where as choose(m, value1, value2, value3, value4 ...) can return any of the value1,2.., based on the parameter m.
Example: Use
CHOOSE(3,"when","in","doubt","just","choose")to get doubt
Remember, you can always write another formula for each of the n parameters of choose() so that based on input condition (in this case 3), another formula is evaluated.
7. Repetitively print a character in a cell n number of times
You have the ZIP codes of all your customers in a list and planning to upload it to an address label generation tool. The sad part is for some reason, excel thinks zip codes are numbers, so it removed all the trailing zeros on the leftside of the zip code, thus making the 01001 as 1001. Worry not, you can use rept() the extra needed zeros. You can also custom format cell contents to display zip codes, phone numbers, ssn etc.
Example: Use
zipcode & REPT("0",5-LEN(zipcode))to convert zipcode 1001 to 01001
You can use REPT("|",n) to generate micro bar charts in your sheet. Learn more about incell charting.
8. Find out the data type of cell contents
This can be handy when you are working off the data that someone else has created. For example you may want to capitalize if the contents are text, make it 5 characters if its a number and leave it as it is otherwise for certain cell value. Type() does just that, it tells what type of data a cell is containing.
Example: Use
TYPE("Chandoo")to get 2
See the various type return values in the diagram shown right.
9. Round a number to nearest even, odd number
When you are working with data that has fractions / decimals, often you may need to find the nearest integer, even or odd number to the given decimal number. Thankfully excel has the right function for this.
Example: Use
ODD(63.4)to get 65
Also try even() to nearest even number and int() to round given fraction to integer just below it.
Example: Use
EVEN(62.4)to get 64
UseINT(62.99)to get 62
If you need to round off a given fraction to nearest integer you can use round(62.65,0) to get 63.
10. Generate random number between any 2 given numbers
When you need a random number between any two numbers, try randbetween(), it is very useful in cases where you may need random numbers to simulate some behavior in your spreadsheets.
Example: Use
RANDBETWEEN(10,100)may return 47 if you keep trying 😉
11. Convert pounds to KGs, meters to yards and tsps to table spoons
You need not ask Google if you need to convert 156 lbs to kilograms or find out how much 12 tea spoons of olive oil actually means. The hidden convert() function is really versatile and can convert many things to so many other things, except one currency to another, of course.
Example: Use
CONVERT(150,"lbm","kg")to convert 150 lbs to 68.03 kgs.
UseCONVERT(12,"tsp","oz")to findout that 12 tsps is actually 2 ounces.
12. Instantly calculate loan installments using spreadsheet formula
You have your eyes on that beautiful car or beach property, but before visiting the seller / banker to findout of the monthly payment details, you would like to see how much your monthly / biweekly loan payments would be. Thankfully excel has the right formula to divide an amount to equal payment installments over given time period, the pmt() function.
If your loan amount is $125,000,
APR (interest rate per year) is 6%,
loan tenure is 5 years and
payments are made every month, then,Use
PMT(6%/12,5*12,-125000)which tells us that monthly payment is $ 2,416 if you keep trying 😉
Also, if you want to find out how much of each payment is going for principle and how much for the interest component, try using ppmt() and ipmt() functions. As you can guess, even though EMIs or loan installments remain constant, the amount contributed to principle and interest vary each month.
13. What is this week’s number in the current year ?
Often you may need to find out if the current week is 25th week of this year. This is not so difficult to find as it may seem. Again, excel has the right function to do just that.
Example: Use
WEEKNUM(TODAY())will get 33
14. Find out what is the date after 30 working days from today ?
Finding out a future date after 30 days from today is easy, just change the month. But what if you need to know the date thirty working days from now. Don’t use your fingers to do that counting, save them for typing a comment here and use the workday() excel funtion instead. 🙂
Example: Use
WORKDAY(TODAY(),30)tells that Sep 24, 2008 is 30 working days away from today.
If you want to find out number of working days between 2 dates you can use networkdays() function, find out this and a 14 other fun things you can do with excel.
15. With so many functions, how to handle errors
Once you get to the powerful domain of excel functions to simplify your work, you are bound to have incorrect data, missing cells etc. that can make your formulas go kaput. If only there is a way to find out when a formula throws up error, you can handle it. Well, you know what, there is a way to find out if a cell has an error or a proper value. iserror() MS Excel function tells you when a cell has error.
Example: Use
ISERROR(43/0)returns TRUE since 43 divided by zero throws divide by zero error.
Also try ISNA() to findout if a cell has NA error (Not applicable).
Give these functions a try, simplify your work and enjoy 🙂
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.