Excel Basics #4: Formulas and Functions

[SporeMaster619]

excel is HARD. i do it in class every day -_-.

[blizzal2]

how about? 2 types of concrete have different types of limits its either xc1,xc2,xc3,xs1,xs2,xs3,xd1,xd­2,xd3. for all of these there are a seperate table each for 100 or 50 years. and each of these tables there is a list of different cover values 15,20,25....60. So basically my lecturer wants me to be able to make a working programme where you input for example xc2,cover 15,at 100 years and the limit would just pop up...I have all the limits i just need to know what formula to use?

[ExcelIsFun]

@blizzal2 , stop posting the same question on 10 different videos!!!

[ventureelect]

Stretching words in excel is not needed.

[Mr2011Anish]

Thanks

[mouse6486]

Hello, it must be great to have you as teacher wherever you teach! you a great teacher, thank you!

[ExcelIsFun]

@mouse6486 , you are welcome!!

[FiggaDaSPy]

really good stuff sir

[stffaluffagus]

Awesome videos! Keep up the good work!

[ExcelIsFun]

I am glad that you liked it!!

[321funkystar]

Another Happy student... Thanks!!!

[ExcelIsFun]

You are welcome!

[thickbro522]

2:23 now im gunna hit TAAAB lol great tutorial vids!

[ExcelIsFun]

I am glad that you like them!

[nanokamina]

by n-1i meant eg in my university for exam purpose they use this feature in a software so like we have 3 term paper so they add the marks of the best 2 papers attempted for instance in one i get 12/15 i second i get 15/15 and in third i get 10/15 so the software will add 12+15 and carry forward the marks for the final exam, assignments etc i hope this helps

[ExcelIsFun]

I have no idea.

Try this site for Excel questions:

mrexcel[dot]com/forum

[nanokamina]

i would like to know is there a formula where we can put the option of n-1 for example of the 3 items the best 2 are chosen ?

[ExcelIsFun]

I do not understand your question.

[Yohasakura2005]

For column 5, do we really need to type in the equal sign, as we can just type in the text to save time?

[ExcelIsFun]

Sure, but then everytime you change a formula input, you have to re-type the label. The advantage to a "text formula" is that it can change just like a "calculating formula" when the formula inputs change. The rule is, if the data can change, put it in a cell and refer to it with a cell reference in a formula.

[risingsunemployee]

i have learnt excel fundamental within 2 hours .thanks a lot...yours are the best at this subject,,,Great job man...

[ExcelIsFun]

I am glad that the videos help!

[Pupe008]

thanx for all the vids i am without work and for alot of places they need people who can use excel and this is a great help thanx a thousand times over...:)

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

You are welcome!

[eannabi]

Thank you so much for all the videos! They are extremely helpful and you are very mindful posting them. Wish you all the best :)

[ExcelIsFun]

You are welcome!

[eannabi]

Thank you so much for all the videos! They are extremely helpful and you are really mindful posting them. Wish you all the best :)

[ExcelIsFun]

You are welcome!

[MrKirumbuyo]

Oh my, where was I ? In the dark? I wish the whole world should see how remarkable your videos and loads of knowledge in them. You have simply just saved me! Whew.....! Am glad

[ExcelIsFun]

I am glad that they help!

[aman1245singh]

Hello sir, I had tried the ampersand formula and used this in the monthly rate worksheet

"The "&A9&" for a "&A8 &DOLLAR(B8)&" is "&DOLLAR(-B9). And the result was The Monthly Payment for a Loan Amount$24,799.00 is $465.72. How would we correct the spacing error between loan amount and $24,799

[ExcelIsFun]

It won't let me post, so I sent an e-mail.

[marinight2008]

Really! I'm from Brazil and I was desperate needing instructions how to use excel for my new job! You saved me!!! hehehe thanks!

[ExcelIsFun]

You are welcome! I am glad that the videos help!

[futballerxx14]

bugs bunny joke.. lol

[ExcelIsFun]

Yes, Bugs Bunny loves Excel!

[vinodng44]

its gr8 thanks a lot

[ExcelIsFun]

You are welcome!

[leejae]

"Contractually extracts." Ha ha ha!

These videos are so awesome.

[ExcelIsFun]

I am glad that you like them!

[lexingtonlp]

Excellent video!

[fahadsaeed50]

good video

[ExcelIsFun]

Thanks for the 5 star rating!!

[xzbeijing]

the website which is listed in the end of this viedo is not avialable. Could you please give me another website so I can downlod your worksheet. Thans a lot

[ExcelIsFun]

Serach for and watch this video title:

Search & Find Excel Videos, Playlists, Download Workbooks - excelisfun

[xzbeijing]

thanks! got it

[highonhayek]

(6:20)

wow... charging interest is the equivalent of theft. :(

Stay classy ExcelIsFun. Can't wait to hear all the other suckerpunches you have to deliver as I make my way through the rest of your video library.

[ExcelIsFun]

If you listen carefully, I correct myself and say "contractual extraction"!

Since most people don't understand the power debt has played in increasing economic growth throughout history, they are confined to feeling like interest is "contractual extraction". So the joke usually gets lots of laughs.

If you want classy, though, you can go buy your Excel help somewhere else!

[Cannibalzz]

These are great!

[ExcelIsFun]

I am glad that you like them!

[Ashamanknight]

The EFFECT function does not appear on my excel 2003, is this something in 2007 only?

[ExcelIsFun]

In 2003, you can add it in by going to Tools menu, Add-ins, then select the "Data Analysis toolpak".

[Ashamanknight]

Thanks abunch!! You really helped me out.

[ExcelIsFun]

You are welcome!

[animefreak38]

Hey there excelisfun nice tutorial! Anyway can i know if there any way that we can assign colors to different numbers like making an odd number RED and even number GREEN?

[ExcelIsFun]

Highlight range, go to Conditional Formatting, for odd use the formula:

=MOD(RelativeCellReferenceOfAc­tiveCell,2)

For even use:

=MOD(RelativeCellReferenceOfAc­tiveCell,2)=0

See Excel Magic Trick 370, which I will post later today.

[animefreak38]

thanks for your help! anyway is there suppose to be a space in the word active?

[ExcelIsFun]

No space. Watch the video. It will be posted in a few minutes.

[VandalCatt]

ExcellsFun

Why do you put a 1 in the parenthesis for the exponential function?

[ExcelIsFun]

There are two ways I can explain it: deductive proof or indutive proof. If you go to my college web site and click on the Busn 233 class (Finance) and go to Chapter 4 or 5 and click on the hand written notes for chapter 4 and 5, I show a beautiful inductive proof that shows exactly why you have to use the 1. If you send an e-mail to excelisfun, I can send you the link.

It's all finance math that has been around for 100s of years.

[sylwienzik]

I just started taking your lessons. You are a brilliant teacher. I didn't have a clue how to use excel and now I'm starting to understand it. I only had one problem so far. When I tried to insert DOLLAR function it came up with ZŁ (polish currency) and I figured it's because my windows is Polish version. But what if I acctualy want dollar or Pound sign? Is it possible to change it or at least get rid of the currency sign but still have shorter number?

Thanks and sorry for my terrible english.

[ExcelIsFun]

Dear sylwienzik,

Maybe a formula like this will work:

="$"&ROUND(A1,2)

if the number is in cell A1.

--excelisfun

[ExcelIsFun]

Dear swethavim,

I am glad that you liked it!

--excelisfun

[lubenitza2004]

Annual Int Rate... I' m entering 0,085 and than Enter and it changes to 85.Why is that? What do I have to do?

[ExcelIsFun]

lubenitza2004 ,

Try entering 0.085 - don't use a comma

--excelisfun

[derby46a]

Thank you for replying! I remembered, too, that I was looking for your vid re: CTRL + SHIFT + ENTER for arrays. I found it along with the one you told me about above.

Take care and thanks again!

[ExcelIsFun]

Dear derby46a,

EXCELlent!!!!! I am glad that you found it.

When you have one cell highlighted, Ctrl + Enter puts the thing in the cell and keeps cell highlighted.

When you have more than one cell highlighted, Ctrl + Enter puts the thing into all the cells at once (populates all the cells)!

Ctrl + Shift + Enter is used only to enter an array formula.

For array formula basics, see this video:

Excel Array Formula Series #1: Basics of Array Formulas

--excelisfun

[derby46a]

Hello ExcelIsFun!

Your videos are wonderful and help me a lot! Thank you for this!!

I'm trying to find your video where you explained why you use CTRL/ENTER to enter a formula in a cell vs. just ENTER. I thought for it was just for copying formulas down a column of cells. You use it in "Excel Magic Trick #31.5 IF, Data Validation, SUMIF" but I don't know why you did. Can you direct me to the answer re: why we use CTRL/ENTER to enter a formula? Thank you, again!

[ExcelIsFun]

Dear derby46a,

I am glad that the videos are helpful!

I have a video that explains all about CTRL + ENTER. Search for this video and then watch it:

Excel Magic Trick #208 Ctrl + Enter 6 Examples Of Efficiency

--excelisfun

[faznacassiere]

Dear ExcellsFun,

Thank you very much for uploading such useful vedios. its has been very useful to me as well as my colleagues. Thank a lot.

[ExcelIsFun]

Dear faznacassiere,

EXCELlent! I am glad that the videos are useful!

--excelisfun

[ExcelIsFun]

Dear Accountant2009ca,

Yes, fun is good. I remember when I was a kid, people told me that everything can't be fun. Well, that's not always true. If Excel can be fun, then we learn it more easily, then we can do our jobs better!

I am glad that you find the videos useful!

--excelisfun

[anilbit4]

thanx brother....i dont have words to appreciate your efforts....i would just say excel has become effortless since i have started enjoying ur videos....u r gr8 sir...

[ExcelIsFun]

Dear anilbit4 ,

I am glad that the videos are useful!

--excelisfun

[ExcelIsFun]

Dear buzdeal ,

I am glad that the videos are helpful!!!

--excelisfun

[ExcelIsFun]

Dear gaurangcvador,

EXCELlent!! I am glad that the videos are helpful!

--excelisfun

[TheSoftBulletin82]

Great work! I've been one of those guys who knows just enough to get by for creating spreadsheets and forms, it's always great to learn new and sometimes faster ways of doing the same thing.

[ExcelIsFun]

Dear TheSoftBulletin82,

I am glad that the videos are helpful.

If you watch the whole Basics Series and then a few of the other series in the Playlist section (like Lookup Series), you will not believe how much faster things will go (and more fun!).

--ExcelIsFun

[buji81]

You are a very generous man!..Thank You for your dedication in making this series!

[ExcelIsFun]

Dear buji81,

I am just a guy having fun with Excel! I am glad that you find the series useful!

--ExcelIsFun