Highline Excel Class 02: Excel Formulas & Excel Functions

[lhen054]

hey, i am working in an advertising company and i normally extract a file that includes (e.g. 11/09/2011 Monday 6:15 am) all this are in a cell. Yet daily all i need is just the time 6:15 am that this ads appeared and need to do it in multiple rows. can you please give me an advise on formulas.

[ExcelIsFun]

@lhen054 , try formula:

=MOD(Cell,1)

Then Number Format as time.

[lhen054]

@ExcelIsFun

Hi, I tried this formula but it is not working... i posted the same in the forum. mrexcel.com/forum/showthread.p­hp?p=2864531#post2864531

[lhen054]

@ExcelIsFun Hello Mr. Excel... Thank you but that one did'nt work for me. However, Excel Magic Trick #192: Extract Middle Name Text To Column does apply. Thank you so much for your video. More power!

[ExcelIsFun]

@lhen054 , I am glad that the videos help!

[Choko4343]

hey

[fearlover09]

2+3 = ? IT TOOK YOU 3 SEC TO THINK OF THAT ? :P ...ehehehe thanks for the vid it helped me a lot.....! gotta check of your other vids ..I wanna learn Exel asap.... before our exam xD

[Pavithra9181]

Hey

Randomizing (F9) doesn't work for me . How did you do that?

[Pavithra9181]

Hey

Tab and Shift tab is not working for me... all others viz enter, ctrl enter, shift enter work.

[azamudinishak]

thanks you so much*infinity

[ExcelIsFun]

You are welcome * infinity!!!

[6rbtube]

you are amazing

[ExcelIsFun]

I am glad that the videos help!

[kiyazaj]

@ExcelIsFun It definitely did!

thx a lot, saved me lotta time :)

[ExcelIsFun]

You are welcome!

[aman1245singh]

Thanks again for the wonderful video

[ExcelIsFun]

No problem!!!

[blowyourmindset]

Are you being paid for all of these video you've done? You have a lot of video that cover a lot of topic about excel. I really appreciate someone like you were born in this world!

[ExcelIsFun]

You are welcome!

No - not paid - just posting videos for people to learn from!

[woldemort78]

these are easy- im stuck up with how to bond if & and function >:/

[ExcelIsFun]

If these are easy that means you are an Excel Master! If you want to see a video about the IF & AND functions, search for and wacth these video titles:

Excel Magic Trick 167p1 IF function formula: 12 Examples!

Excel Magic Trick 167p2 IF function formula: 12 Examples! -- (This one shows IF & AND functions together)

Highline Excel Class 35: IF Function Vs. VLOOKUP Function

[saleall]

@ExcelIsFun hey. lets say i want to write a set of numbers downward, under the colon A-P, 1-16. and if i would about to write down every combination of these numbers without numbers repeating themself, it would take me weeks. so what is the easiest way to do it?

[ExcelIsFun]

I do not know. Try this site:

mrexcel[dot]com/forum

This is THE best Excel site for questions.

[grooovydeb]

Thanks for this great tutorial video. Love the positive energy you put into this which makes it fun!

[ExcelIsFun]

You are welcome!

[aachilders]

You gotta love people who are passionate about what they do!! Great tutorial. Thanks for the tips!

[ExcelIsFun]

You are welcome!

[yuliarus]

This tutorial is one of the best I ever saw on YouTube! Definitelly the funniest one :) Even boring on first sight Exel can be Fun! Thanks for great video and good mood :)

[ExcelIsFun]

You are welcome!!

[EasternMerchant]

LOL ya know... I'm not sure is Excel is fun, but your enthusiasm might just make it appear fun for at the duration of the tutorial video.

[ExcelIsFun]

Thanks for the compliment!

[thuynguyenable]

Dear ExcellFun,

Suj: Print Reports using Set Print Area.

Is there a way to gather all reports that you want to print out in one page instead of putting each report in a separate page?

[ExcelIsFun]

I do not know - maybe try post Q? to Mr Excel Message Board.

[yaw123able]

@thuynguyenable

to print all your reports on separate pages, I guess you need to select all the reports and issue a print command. make sure you indicate/ check selection in the print Dialogue Box. if you understand, you will be able to print the reports the way you want it

[ExcelIsFun]

For more info search for this playlist of various printing videos:

Excel Page Setup & Printing Report Tricks

[5gorgeous]

If the formula got racked in excel, for instance =SUM(), I've tried to repear it, but it doesn't get saved by the system?

Can you help me? :) Marina

[ExcelIsFun]

I do not understand your question. I do not know what you mean by:

"racked"

Can you explain your question differently?

[5gorgeous]

Sorry, I meant 'wrecked' or damaged, what I mean the Autosum formula in the system has been damaged and doesn't work anymore, so I can't calculate the total. Do you know how to repair the formula?

[ExcelIsFun]

I have never heard of that, ever.

What error message do you see?

What version are you using?

[musingrain]

You are owesome. Thanks so much for your sharing!!!!

I've spent a whole day to finish the Basic Excel 2007 and lookup series, cannot stop watching. I've some basic knowledge but still learn lots from it. Thanks again.

I am begin to watch Highline Excel Class series , some of them have covered in basic excel, um....I would like to know if I can skip some of the sessions or it is better for me to start from #1?

[ExcelIsFun]

I would not skip it, I would use it as a review - in addition, it is much more fast paced than the Basic Series.

[valeriacha20]

Hi! I'm having a little trouble matching 2 columns. I have 1 column with 40 different account numbers listed; and I'm trying to match it to another column with account numbers. All i need is to see what accounts are included in both columns. Do you think you could help me with this? I would like to contact you.

[ExcelIsFun]

Yes, I have many videos on this topic. Search for and wacth this video title to teach yourself how to search the excelisfun channel:

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

Then search for "match two lists" or "Compare 2 lists"

You must give yourself to power to find any video that you want by learning the tricks of the excelisfun channel! :)

[graffitypsycho]

Anyway I can get in contact with you? I'm having a bit of a trouble with an excel formula. You might be able to help me. Thank you.

[ExcelIsFun]

Search for "excelisfun" at YouTube, then click on the excelisfun channel, then use the "Send Message" option in the lower left.

[trance2307]

You're awesome...thanks!

[ExcelIsFun]

You are welcome!

[Godsblack]

Have you already done a video about making an employee schedule? I want to make a schedule that considers when people can't work (like when they are in school or have another job, or can't work a certain location).

[ExcelIsFun]

Can you give me more detail about what you are trying to do? Is your schedule like a calendar with dates? Or some other setup?

[Godsblack]

its a weekly schedule, we have about 25 employees, 6 locations: E26,E12, Customs, Atrium, Tgates and Lenox; open 7-3 ; midshift 12-8; close 2-10.

Some employees cant work on certain days and shifts due to school or their other jobs. It gets too cumbersome trying to remember all that, so I want to find an easier way and possibly automate and randomize the schedule.

[ExcelIsFun]

I do not have a video like that. If you have a specific Excel question, I try to help. You know like "how to you add data validation". The answer to your question is quite complex and may require Excel VBA code. If you need a consultant, the best site around for this is the Mr Excel site: mrexcel[dot] com. This site has consulting and a Message Board with 100s of people answering your questions.

[ExcelIsFun]

You can serach for and watch this video title:

How To Post Questions At The Mr Excel Message Board

This is a video i made about this amazing site!

[Accounting010203]

gr8 vid!

[ExcelIsFun]

I am glad that you liked it!

[SkullFck69]

hi can YOU Pls tell me what does the dollar symbol ($) do?/.. tnx soo mch.

[ExcelIsFun]

The dollar signs in cell references convert a relative cell reference to an absolute reference (or locked). Watch these video titles to learn more about this important but difficult topic:

Excel Basics #8: Cell References Relative & Absolute

and

Excel Basics #9: Mixed Cell References in Formulas

and

Excel Basics #11: Spreadsheet Setup & Cell References

[MegaMagicBus]

amazing  than you

[ExcelIsFun]

I am glad that you liked the video!

[DonaldC434343]

Thanks!

I had no idea that a formula could do so many things!

[ExcelIsFun]

You are welcome!

[thirthyest]

very nice and well structured:)

[ExcelIsFun]

Thanks!

[NguyenPhanNhuQuynhVN]

Thank you so much Mike! I really love all of your lessions and videos. Again, thank you kind Mike!

[ExcelIsFun]

You are welcome! I am glad that you like the videos!

[rtbubba]

In reference to the last minute or so,  what if you needed to add alternating cells together; what would be the efficient formula, instead of =Cell+Cell+Cell etc.

[ExcelIsFun]

If values in range A2:A17.

For adding the values in the odd rows, try this:

SUMPRODUCT(A2:A17,--(MOD(ROW(A­2:A17),2)))

For adding the values in the even rows, try this:

SUMPRODUCT(C2:C17,--(MOD(ROW(C­2:C17),2)=0))

I'll make a video soon...

[candice6666]

Thanks a lot I had fun watching it=p

[ExcelIsFun]

EXCELlent!!!!

[gandukabacha]

thanks mike u r doing grt......

[ExcelIsFun]

You are welcome!

So many formulas & functions, so much fun!

[tmontemage22]

This is absolutely terrific... Every financial analyst position I apply for quizzes my Excel skills... This is an excellent tool to brush up

[ExcelIsFun]

Great, I am glad that it is useful!

[growingstupid]

Thanks you! This was very useful.

[ExcelIsFun]

You are welcome!

[acolmar]

The problem is my months are out of order (MAY, AUG, OCT), but sorting will only due alphabetical order. Is there a way to make the Months correlated to numerical data (JAN=1) by using the pivot table or going back to the raw data. When I extracted the data Months where displayed as plain Text not as a date.

[ExcelIsFun]

Add a new column for month. Then use the formula =MONTH(cell with date) then copy it down. Then you can sort on that column!

[acolmar]

But since the data was extracted as simple text such as "JAN", "FEB", "MAR" when I use =month(cell with "JAN") it returns ####. Is there any other way? I tried =if(D13="JAN",1,if(d13="FEB",2­,.... but Excel only nests up to 8 or only up to August.

[ExcelIsFun]

Yes, you can create a Custom List:

Alt + F + I (Excel 2007), then click on "Edit Custom List". After you create a Custom List, you must use the Sort dialog box and select Sort by Custom List.

[ExcelIsFun]

If you have Excel 2003, you will have to let me know and then on Monday I will look at my Excel 2003 on my work computer and tell you how to do it that way.

[acolmar]

You are an Excel Master! It worked! Thanks so much for taking the time!

[ExcelIsFun]

Just a guy having fun with Excel!!!

[acolmar]

I extracted data from an online source for 5 different stocks with the following information: Company name (text), year (numerical), Month (Text, i.e. JAN), Adjusted Closing Price (numerical). I then put the information into a Pivot Table on a new sheet. Row Labels: Year, Month. Colum Labels: Company. Values: Adjusted Closing Price.

[acolmar]

Dear Mike,

Love your work! Truly a lifesaver! Anyway I can e-mail a quick question?

Thanks!

[ExcelIsFun]

Post a quick question here in the comment section.

[thusi99]

Thanks a million for the wonderful video. I just learned few tricks from your videos...

[ExcelIsFun]

Dear thusi99,

EXCELlent!! I am glad that they were helpful!

--excelisfun

[jjonad]

thank you great videos

[ExcelIsFun]

Dear jjonad,

You are welcome!

--excelisfun

[rakmaknaja]

I'm a Thai mathematics teacher, live in Thailand.

And your VDO is brilliant!!

Thank you so much.

KruKeng Chaingmai Thailand

[ExcelIsFun]

Dear rakmaknaja,

You are welcome! I am glad that you like it!

--excelisfun

[intelliGENeration]

These videos are a great reference!

Should we be worrying about you taking them down in the future? :)

[ExcelIsFun]

Dear intelliGENeration,

I am glad that videos are helpful! I will keep them here at YouTube as long as YouTube exists! If YouTube ever goes away, I will put the videos somewhere else!

--excelisfun

[MeLikeGoFast]

Just happened to stumble upon your site.

Nice video.

[ExcelIsFun]

Dear MeLikeGoFast,

I am glad that you liked the video!

--excelisfun

[justme11may]

I have the following question,

-I have an excel sheet with 100 employee records

-every employee has a status (working, leave, suspended, terminated

-I need a command automatically copy all the field for all the employees that are working, with keeping the exported data updated if the status was changed...

the exported data must include all the fields concerning the employee, not only one cell (e.x: status,Bdg#,working, dept,...etc)

please send me the link if you do a totorial

keep it up

[ExcelIsFun]

Dear justme11may,

We are not allowed to put links in the comment section. But I do have a few videos about what you are trying to do. See the excelisfun playlist:

Various Excel Extract Records From Database Table / List

The first 4 videos show various methods.

You can search for the playlist title, or you can watch this video to see how to use the excelisfun list of Playlists:

Search For Excel Videos Download Excel Files excelisfun

--excelisfun