Wednesday, May 17, 2017

VLOOKUP versus INDEX MATCH! Which is Better?


VLOOKUP versus INDEX MATCH! Which is Better?

vlookup and index match

Chris Chau of Defeatexcel.com asked twenty-seven (27) Excel MVPs and community experts their preference  on the ongoing controversy of VLOOKUP vs. INDEX MATCH. it is great reference material and some suggest choices they think are better than those two functions!



Click on the link to read the blog. https://defeatexcel.com/vlookup-vs-index-match


Check out the results. According to Chris, there was no clear winner so which do you use and prefer?   It really is liking asking who is better -Superman, or Batman!


If you want more information after reading Chris' blog, check out my course Become a Lookup Expert.  

It provides a comprehensive examination of the Lookup and Reference category in Excel. It covers the LOOKUP and VLOOKUP functions.
With VLOOKUP, you will learn how to: use Wildcards; search through multiple sheets for a match; and what to do if a match is not in the first column of your table. The course also explores HLOOKUP. It explains the advantages and disadvantages of INDEX MATCH versus VLOOKUP. It reviews the OFFSET function, which allows you to write calculations that automatically update as new data is added. With the INDIRECT function, you will learn how to find data on other worksheets sheets.  Finally, HYPERLINK is briefly discussed.

Friday, May 12, 2017

Determining Median and Mode

Determining the Median and Mode

Sometimes determining the average is not sufficient.
For example, if
 you are analyzing departmental salaries you might want to know the median or mode of the salary levels.




=Median(data range)  internally sorts the data and  then displays the middle value. If there is an even number of values, then the two values in the middle are averaged. =Median(B2:B10)


=Mode(data range) will display the value that appears most often. =MODE(B2.B10)

This from an excerpt from my Excel CPE Course, Must Know Excel Tools and Tips for CPAs.  

Monday, February 13, 2017

RRI Time Value of Money Function


Time Value of Money Functions

Below is an excerpt from Excel Time Value of Money Functions for CPAs, a CPE course offered by CPASelfstudy.com


New Single Sum functions in Excel 2013 

There are two new time value of money functions in Excel 2013, the RRI and PDURATION functions.  Both of these functions will only work if you have Excel 2013 or greater.  You will not be able to replicate the examples using a lower version of Excel.

The RRI function returns the equivalent interest rate for the growth of an investment.  The inputs required are the number of periods, the present value and the future value.
In this blog entry, we are only going to discuss RRI and save PDURATION for another time.

RRI Example:

As an example, let’s say you invested $100,000 for 8 years compounded annually and the investment grows to a value of $150,000.  What is the equivalent rate of return?  Click here to read the rest of the entry.

Friday, February 10, 2017

Iphone 7 Delete All Solution

Finally - A solution -How to Delete ALL Emails on My Iphone 7.


I finally found a solution to deleting all the emails in my InBox at once. Why oh why did Apple get rid of the Delete All option?  It definitely needs to be added back.

I found the answer on this site  and it was very easy to do once you know the trick.

1. Go to your Inbox and click Edit.
2. Select a single email.
3. Press and hold down  the Move button at the bottom of the screen and then uncheck the email you selected.
4. A page will pop up and you can select if all the emails should go to Trash or Junk.
5. Make your selection and all your emails disappear!
You may have to hold the Move button for a good couple of seconds, depending upon on how mail you have. 
If you don't see the page, it may just automatically got to Trash.I have had that happen a couple of times.


Make sure that you really want to delete everything in your Inbox before you do this. 
Don't forget that this just deleted them from your inbox- you still need to clean out your trash folder at some point.



Thursday, January 12, 2017

Date and Day Functions



Date and Day Functions

I found an interesting site on Excel for SEOs.
Actually, most of the information on the site pertains to anyone using Excel.
One of their pages was on dates and days. I find a lot of people struggle with date functions
.
=NOW() and =TODAY() are the most common  Date functions.
 They are called volatile functions because they change as the date changes. =NOW() is the more versatile of the two as it tracks both date and time, however, you can format it so that it displays just the date, just the time or both. Whereas, =TODAY only displays and tracks the current date.
They can be very useful. For example, if you create and print a lot of versions of a document, such as a budget. include =NOW(), at the top or in the document header. The =NOW() will display the time - you can even  format down to the second- so you are confident that you have the latest version of your document as you go off to your presentation.

This site has some other date functions that you may be interested in  - particularly NETWORKDAYS and YEARFRAC.
I hope you find them useful.
http://www.acuitytraining.co.uk/white-paper/the-seos-guide-to-excel