Spreadsheet Tips (4): Use Excel to Write Excel Formula

In my previous article, I share some tips on how to use Excel to write FoxPro SQL coding ("FoxPro Tips (1): Use Excel to Write Your Codes"); in this article, I will share another tip of using Excel to write coding - but this time is for writing formula in Excel spreadsheet itself! You may find out some tedious formula can actually be written in a very quick & simple way.

Let me share with you real case study. One of the exercises I have completed with my client recently was checking Prophet model point files ("MPF") produced from a new tool - to find out if there are any differences from the existing MPF:

STEP 1: Import MPF into Excel

Figure 1: New MPF data (labeled as "data1")
Figure 2: Existing MPF data (labeled as "data2")

To compare new and existing MPF, import the MPF into Excel (note: MPF are in comma delimited format) and organize the data as per Figure 1 & 2 below. If you examine "data1" sheet and "data2" sheet carefully, you will find out that the field sequences for these 2 sets of MPF are different. For example, the first field of Sheet "data1" is "Field1", whereas for Sheet "data2" is "Field2".

STEP 2: Setup Comparison Sheet

Figure 3: Copy "data1" Field Header to Sheet "Compare"

Insert a new sheet and rename it to "compare". Copy the field headers from Sheet "data1" and paste them in Row 5, as shown in Figure 3 (you can use field headers from Sheet "data2" if you would like to make Sheet "data2" as the comparison base).

Figure 4: Copy "data1" Field Header to Sheet "Compare"


Copy the field headers from Sheet "data2" and paste them in Row 1. Below each Sheet "data2" field header, key in the column name (A, B, C, ... either upper case or lower case), as shown in Figure 4. The labels in Row 2 represent the column name for Sheet "data2" field headers.

Figure 5: Look-up column name for Sheet "data2" field headers

In row 3, use HLOOKUP to look-up the column name from "data2" sheet for the corresponding field name stated in row 5. For example, the formula input in Cell A3 is "=HLOOKUP(A5,$A$1:$AI$2,2,FALSE". Sounds a bit confusing? Well, the "c" in Figure 5 above means that "Field1" is located in Column C in Sheet "data2".

STEP 3: Build Excel Formula

Figure 6: Compare the first record of Field 1

In Cell A6, compare the value of first record of Field 1 - if the values are the same for both Sheet "data1" and Sheet "data2", return 0 as cell value (and 1 vice versa). You can do the comparison in the normal way you use Excel - for example, the formula you will create in Cell A6 is "=IF(data1!A2=data2!C2,0,1)".

Figure 7: Build Excel formula

Instead of showing "0" in Cell A6, now you are going to change value to "=IF(data1!A2=data2!C2,0,1)" as shown in Figure 7. How? Let me share with you the most important trick of this article - you change the formula to '="=IF(data1!"&A2&"2=data2!"&A3&"2,0,1)"' and cell A6 will have the the value as shown above.

Figure 8: Copy and paste value the formula built

Copy Cell A6 and paste in Row 6 for all column with field headers in Row 5. Copy all cells with formula in Row 6 and paste value. Now the formula in Cell A6 has been converted from '="=IF(data1!"&A2&"2=data2!"&A3&"2,0,1)"' to "=IF(data1!A2=data2!C2,0,1)". But why Cell A6 doesn't show "0" instead? Don't worry, you need to do one more step to get Cell A6 showing "0".

Figure 9: Convert Excel formula


If you configured your Excel for Prophet previously, you can use "Refresh" found in "Add-Ins" tab. Otherwise, you can press F2 and followed by Enter for each cell in Row 2 to convert the "written" formula to "real" formula, as shown in Figure 9 above.


STEP 4: Copy Formula to Remaining Cells

Figure 10: Complete Comparisons

Copy Row 2 and paste in the remaining rows below Row 6 (as per no. of records of Sheet "data1"). Normally, it is recommended to add a total in Row 4 so that you will know which field contains inconsistent values. Conditional formatting is a good way to indicate which field have 1 or more records with different values.

Comments

Post a Comment

Popular posts from this blog

Other Ways to Prepare Prophet Model Point Files? Try FoxPro!

Image
If you are a frequent Prophet user, you must be definitely familiar with "Model Point Files" ("MPF"), i.e. the policy data that you compile in a specific format that Prophet can recognize as inputs for Prophet runs. If my guess is right, most probably you use Data Conversion System ("DCS") to convert the source data you download from your policy administration system (may be in various format, e.g. fixed ASCII / fixed width, comma delimited, tab delimited, etc.) into the required MPF, based on the definitions you specified in your DCS programs. In case your source data are divided into various files, you may need to carry out more steps to prepare MPF, for example: Open different source files in Excel Combine required fields from different source files by using commands (such as) VLOOKUP, SUMIF & MATCH. You may have automated this process by using VBA. Convert the required files into text files Use DCS to convert those text files into MPF  How...
Read more

How do You Setup Indicators in Your Prophet Model?

Image
In my previous post, I have discussed areas that you may consider in setting up input variables . Before I proceed my discussion on core variables, I would think that it is good for use to take a look on indicators - as indicators play an important role in setting core variables with various formulas (known as "variable definitions" in a Prophet model).  The funny thing about indicators is: it DOES NOT carry any formulas that you need to use in calculating a variable! Basically, they are just some texts that you can combine using different logical operators (AND, OR, NOT), such as "ORD_PAR AND SING_PREM" for a single premium ordinary participating product. 
Read more

How Should I Do My UAT? (1): Program / Spreadsheet / Model

Image
If you are working in the actuarial department of an insurance company, it may not be uncommon to you involving in various testing exercises, which are known as User Acceptance Testing ("UAT") in general. Some of us may think that UAT is only applicable to the policy administration system implementation project (Oops... Do I remind on the non-stop follow-ups from your Project Manager?), but in fact UAT is also needed for the programs, spreadsheet templates or actuarial model we use to perform various actuarial studies - whether they are developed internally or by external consultant. It is particularly important for those tools which we are going to use to perform regular studies. In my view, there is no one right answer for the approach to be used in performing UAT on program / spreadsheet / model - most importantly, the approach that you adopt should allow the required testing to be carried out in a structured manner, and it should not create too much "processes...
Read more