SubscribeExcel Formulas – Explained in Plain English
Bookmark this on Delicious Stumble it! Send this to a friend Subscribe to Pointy Haired Dilbert Feed FREE E-mail News Letter
|
Learn more about: .
Buy Excel Formula Helper E-book and Learn 50 Excel Formulas at Leisure Pointy Haired Dilbert Excel Tips, Charting Ideas and Visualization Oomph |
32 Responses to “Excel Formulas – Explained in Plain English”
[...] use it in a snap. So to help new users of excel learn the most frequent formulas I have built an excel formula helper [...]
Once again… totally useful!!! Thanks Chandoo!!
Thanks, while I use most of these formulas already, I did find a couple that I haven’t used for a while.
Hi, I would like to link a 3 data ( a date, amount and text). How do I use the = (equal sign) without manually changing the format of other worksheet destination. Thanks in advance
@Marlon: I am not sure I understood your question. do you mean to add up 3 cells to one ? you can use concatenate() or & operator to do it
hehehe sorry Im poor in english. What I mean is I have 2 worksheet the 1st worksheet is where the raw data is located..the 2nd worksheet was the summary..I get the data by simply using = sign to link the data but my problem is I always change the format cells to date..then number..and so on.. Is there a formula to automate the link..let us say if the data is a date format..a date format din..hope u understand.. thanks chandoo
Chandoo!!! Excellent one, Thanks. It will help me in day to day work in excel. Please put some more formulas and add examples
@Malon: I think you are copying the linked cell of summary sheet.
If you link individual cell of datasheet to the summary sheet, you will get exact format in summary sheet which is of datasheet.
e.g. : If you have the following data in sheet named “datasheet” :
A10 = 15 , B10= 15.03% C10=31-05-08
Now, you link by using = sign in other sheet say “summarysheet” :
A1=”datasheet”!A10
don’t copy A1 to B1 but link individualy like( i presume that you are copying)
B1=”datasheet”!B10
C1=”datasheet”!C10
If you do individualy, you will get exact format which is in your datasheet.
If you copy the A1 of summarysheet to B1 & C1, you are not only copyingthe series of link but also copying the format of A1 cell.
Hope you clear about the crux of the matter !
@Ketan: thanks for helping out Marlon
@Marlon: ideally when you link cells like this, formats carry over. The exception being, if you have manually changed the format at target range. you can un do this by selecting the cells, pressing ctrl+1, and adjusting the format to general.
another way is to use format() function. Using this you can change the format of an input value to the one you like.
@All: thanks for the appreciations.
Thank you somuch Chandoo Bhai.Now i can able use some more in my sheets.
These are incredible. I knew a lot of these but never heard of trim and I can really use that one!
Hi Chandoo,
Iv’e been producing a register that takes the raw register data such date and how many people attended and I want it to sort into monthly data which formula sdo i use ? example
register
Big school , 12 jan 2008 , 12 trainees
little school 15 jan 2008 , 10 trainees
red school 10 Mar, 5 trainees
monthly data
Jan = 22 trainees
feb = 0 trainees
Mar = 5 trainees
Hope this makes some kind of sence as I have been pulling my hair out with it
thank nick
Excellent guide for newbies. Strip all the tech part, and it appeals so much to the new users!
@Nick h
Make a excel register with the four columns:
Date,Month,Name of school and No. of trainees
In column date, set the format mm-dd-yy
In column month, copy the value from the date cell and change format of cell(date) to mmm-yy
In column, Name of school, make validation with the required list of the school name and select from the list which ever is required
For making summary, make a pivot table with “month” is row and sum of “no. of trainees” in data.
By giving a long range(untick the blank from the month list in pivot table), you need to only refresh the the pivot table .
You will get the desired results
This page fails – opening one of the links above, eg.: MAX() (URL: http://img.chandoo.org/i/formulas/max.png) fails.
As noted earlier, the domain hosting provider has blocked the content and I got it freed for a while. img.chandoo.org is a sub-domain and it is having some issues. I will try to resolve it. Meanwhile you can try changing the URL to chandoo.org/img/filename from img.chandoo.org/filename and it works.
@Michael : this is fixed now. Thanks for letting me know
This is very helpful
Hi,
I am keeping this in my favorites.
But I notice your syntax for Pmt() and Ipmt() are the same, but the output is different. Is this on purpose?
@David: thanks for pointing that out.. I made a mistake. Will correct and upload the image again
@David: I have now corrected the files. Let me know if you see something else funny
Great!
The mortgage functions… Never seen them before ( denial), but I might just get out of that denial now!
For the Vlookup function, the false or true finally (… I am slow) made more sense.
This is an incredibly ingenious aid as well as a very entertaining blog that helps spar up the day!
Thanks!
@Daniele: thanks for the nice words.
[...] the PHD Excel Formula Helper Tool? Good, now it is available as an e-book, so that you can take a print out of all the formulas or [...]
Thanks Chandoo, very good support.
[...] Learn Excel Formulas in Plain English | Executive Dashboards in Excel – 4 Part Tutorial | 15 Excel Fun Tips [...]
how can i use “if” formula to change table of 5 into 6,7,8,9,10….
@Nickel: Can you please clarify? I am not sure I got your question… Why would you use IF() when you need a multiplication table ?
I have a question similar to Marlon’s. I have the following formula on 50 subsequent sheets, but the formatting is not carrying over from my master where I change formatting on notes regularly.
=+IF(ISERROR(VLOOKUP($B43,’Balance Key’!$A$2:$F$186,6,FALSE)),0,VLOOKUP($B43,’Balance Key’!$A$2:$F$186,6,FALSE))
Please tell me how I can carry over formatting. For instance, If I change the font color in the cell on the balance key sheet, how can I make that color translate to the cell where I have this formula?
thank you very much…..master.
Hi Chandoo, I need your help please. I have the following data in a single cell and was trying to separate them into the different columns. Like ADDRESS, CITY, STATE, etc columns. I also need to delete the address:, city:, state:, etc. Just like to get the 116 North St, not the ADDRESS:. I would really appreciate your help. Thanks.
Address: 116 North St
City: Preston Hollow
State: New York
Zip: 12469
Day Phone: 5184707400
Evening Phone: 5182396800
Email: wood@mhcable.com
@Sam
I assume your data is in A2:A100?
Put the following formula in the cells and copy down
B2: =MID(A2,9,FIND(“City”,A2)-10)
C2: =MID(A2,FIND(“City:”,A2)+6,FIND(“State:”,A2)-FIND(“City:”,A2)-7)
D2: =MID(A2,FIND(“State:”,A2)+7,FIND(“Zip:”,A2)-FIND(“State:”,A2)-8)
E2: =MID(A2,FIND(“Zip:”,A2)+4,FIND(“Day Phone:”,A2)-FIND(“Zip:”,A2)-5)
F2: =MID(A2,FIND(“Day Phone:”,A2)+11,FIND(“Evening Phone:”,A2)-FIND(“Day Phone:”,A2)-12)
G2: =MID(A2,FIND(“Evening Phone:”,A2)+15,FIND(“Email:”,A2)-FIND(“Evening Phone:”,A2)-16)
H2: =RIGHT(A2,LEN(A2)-FIND(“Email:”,A2)-6)
You will/may need to retype the quotation marks ” manually in each formula if you copy/paste them