Vlookup is one of the functions I use the most. It can locate data within a table and return a value. One of the most confusing things about the function is whether or not to include the True or False at the end.
When using the Excel Function wizard, it argument in the function is called Range_lookup.
Simply stated, use a TRUE or 1 when you want an approximate match. Use a FALSE or 0 when you need an exact match.
Use TRUE when the data in the lookup table does not contain all of the possible data points. For example, in the figure below, if you want to look up the tax rate you would use an approximate match since all incomes are not in the table. If the net income were 100,001, it would return a rate of 39%.
It finds the closest value that is not larger than the lookup value.
In order to use an approximate match, your data must be sorted as well. Otherwise, use a FALSE or exact match.
At the recent AICPA conference, much of the discussion focused on what skills the accountant of the future would possess. There are two major changes happening in public accounting — technological disruption and a move to more advisory services.
These two changes require more diverse skills than ever before. Many of the skills are beyond the traditional CPA.
One of the primary skills that will be needed in the future is the ability to continuously learn.
“There will be a need to learn, unlearn and relearn… it’s the thought of futuristic literacy moving forward–not to read and write but the ability to unlearn so you can relearn, and the general awareness of all these activities for everyone. If you’re trusted advisors, you need to know the implications of these shifts–of both the wide knowledge and capabilities for a narrow knowledge.”
With these changes, the certifications will need to evolve. Possibly different types of certifications will be needed beyond the CPA.
To see the remaining summary of the conference, go to
In my opinion, as students start to focus on graduation, certification needs to be a focus. No matter the certification — CPA, CMA, CFE, or CISA, those initials will matter and could be a difference maker.
Love sports? Good at Data Analytics? There may be a new job for you.
The Southeast Conference (SEC) recently hired Deloitte to review their football officiating program. The firm is to review play by play reports and using a statistical analysis, determine if there are any outliers or exceptions. They are also to compare to other conferences to ensure quality standards are being met.
Hopefully between analysis, the auditors can watch a game or two.
RSA 2048-bit encryption is the standard. The time to break the code for encryption is currently so long, this is the standard for most systems. All banking, email systems, etc use 2048-bit as the primary encryption method.
Now comes the new computers called quantum computers. The processing times on these systems are a fraction of traditional computers. Experts predict RSA 2048-bit encryption may be broken in 8 hours or less.
Scientists have found a new encryption method which would not be able to be broken, but it is far becoming the standard.
In a data area, several cells are selected by highlighting in a different color. The example show cells that were manually selected, rather than using conditional formatting. However, if cells are shaded or have a different font color due to conditional formatting, this will work as well.
These can easily be sorted to the top of the list. Place your cursor in one of the cells that
has the color to sort to the top. Right
From the pop-up menu, select sort > Selected cell color on top. This will sort based on the color.
To develop a new Artificial Intelligence model, it needs to be trained. This is done by processing a large amount of data several times until the model works.
With AI models becoming more complex, larger datasets are required to fully operationalize the model. This is taking significant amounts of computing power, and thus, electricity.
In a study at the University of Massachusetts, researchers found “that the process can emit more than 626,000 pounds of carbon dioxide equivalent—nearly five times the lifetime emissions of the average American car (and that includes manufacture of the car itself). “
Similar issues have been raised with Blockchain mining, in particular Bitcoin.
Think you are new to analytics? You may have a start. Most of us in business, and especially accounting are quite adept in Excel. This makes the tool a perfect place to start. You cannot use the largest data sets, but with new Excel tools such as PowerPivot, that is constantly increasing.
Analyzing data in Excel helps you learn the process of determining which questions can be answered from the data and using an analytics technique to summarize. If you can do a Pivot Table in Excel, you are on the road to learning more about analytics.
When entering formulas, use the F4 key to toggle between absolute and relative references. When typing the above example, after typing “A1”, press F4. Keep pressing F4 until the combination of absolute/relative references appears.
For a refresher on the differences between Relative and Absolute references, go to: