Check Excel #N/A using ISNA Function

Posted on December 16, 2015 by Wyman W

This Excel tutorial explains how to check Excel #N/A cells using worksheet formula ISNA and VBA Function. You may also want to read: Excel IFERROR Function Cause of Excel #N/A #N/A is usually caused by lookup related functions where a value cannot be found in lookup table. Lookup Functions include Vlookup, Match, HLookup, Lookup. Another common cause is that when you use an Add-In formula, and then send the file to someone who doesn't have that Add-In. The formula was fine at the time...

Access Excel extract file name from file path

Posted on December 7, 2015December 26, 2016 by Wyman W

This Excel tutorial explains how to extract file name from file path using VBA and without VBA. Excel Access VBA extract percentage from text Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Excel extract file name from file path Assume that you have a file path such as C:\Program Files\Google\GoogleToolbarNotifier\a.jpg If you want to extract file name a.jpg from the path in Excel, first we need to think about how to de...

Create Excel Histogram

Posted on November 14, 2015November 27, 2015 by Wyman W

This Excel tutorial explains how to create Excel Histogram. You may also want to read: Excel clustered column chart Excel scatter chart using text name Definition of Histogram Before reading this post, you are recommended to read my below post. Types of data in statistics (Interval, Nominal, Ordinal). Histogram is a column chart that counts frequency of numbers and categorize the numbers into numerical groups (the groups are called bins). Usually people don't leave space between t...

Excel VLookup different format (a mix of text and number)

Posted on October 29, 2015September 17, 2017 by Wyman W

This Excel tutorial explains how to Vlookup different format (a mix of text and number). You may also want to read: Case sensitive lookup Excel verify Text format and convert number to text Excel verify number format and convert Text to Number Issue with VLookup different format (a mix of text and number) Sometimes your data source table may contain a mix of text and number, let's say Employee ID in column A. In the Lookup table, when your lookup value (column D) are all number,...

SPSS Excel one sample T Test

Posted on October 25, 2015April 3, 2016 by Wyman W

This tutorial explains how to use Excel and SPSS to perform one sample T Test. You may also want to read: Statistics Hypothesis Testing Calculate Z Score and probability using SPSS and Excel What is T Test In statistical inference, we are interested to know whether a small sample comes from a population. To inference using sample mean, when the population standard deviation and population mean are known, we can use Z test to interference the population mean from sample mean. In rea...

Statistics Hypothesis Testing

Posted on October 16, 2015October 24, 2015 by Wyman W

This tutorial explains the concepts of hypothesis testing in statistics. Hypothesis Testing Many text book uses the court case example to explain hypothesis testing. In the U.S. (but not all countries), the court finds evidence to prove the defendant guilty, instead of proving the defendant innocent. There are two hypothesis for a court case: The defendant is guilty - when we have a theory and try to prove it, the hypothesis is called H1 The defendant is innocent - the alternat...

Excel VBA clear cell contents with ClearContents Method

Posted on October 15, 2015October 16, 2015 by Wyman W

This Excel tutorial explains how to clear cell contents / clear cell formats using Clear, ClearContents, ClearFormats Method. Excel VBA clear cell contents There are several kinds of "clear" related Excel VBA Methods for different scenarios. I will also explain how to clear single cell contents, clear merged cells contents, and clear blank cells. Clear single cell contents with ClearContents There are two ways to clear single cell contents (clear value in a Cell but not formatting) As...

Excel Delete Blank Rows

Posted on October 12, 2015February 26, 2017 by Wyman W

This Excel tutorial explains how to delete blank rows in Excel with VBA and without VBA. You may also want to read: Excel delete blank rows and blank cells before import into Access Excel delete duplicated data in consecutive rows Excel Delete Blank Rows Sometimes we have data that contains blank rows, one example is data with pivot table layout where data are grouped together and repeated items are blank. Previously I wrote a post about filling blank rows, this post is about delet...

SPSS Excel Covariance, Correlation, Least Squares Method

Posted on September 28, 2015April 3, 2016 by Wyman W

This SPSS Excel tutorial explains how to calculate Covariance, Correlation and Least Squares Method in Excel and SPSS. Covariance and Correlation When we plot a scattered graph to see the relationship between two interval variables, we can see how straight a line is formed (whether the relationship is strong) and whether the relationship is positive or negative (upward or download). However, when we want to look deeper to see the strength of the relationship, we need to calculate covari...

Calculate Z Score and probability using SPSS and Excel

Posted on September 14, 2015March 27, 2016 by Wyman W

This tutorial explains how to calculate Z Score and probability using SPSS and Excel. You may also want to read SPSS Excel one sample T Test Calculate probability of a range using Z Score Calculate Z Score and probability using SPSS and Excel In statistical inference, we are interested to know whether a small sample comes from a population. To inference using sample mean, when the population standard deviation and population mean are known, we can use Z test to interference the popula...