Monthly Archives: March 2022

Uncategorized

Lambda: simple VAT calculation

For good one year, I have followed discussion about LAMBDA function in Excel. Finally the day came, that I found LAMBDA in my own Excel 365. LAMBDA is very versatile function, I just show one example to enjoy the LAMBDA.

If you are working with business-to-business sale persons, they talk prices without value added tax. When I think prices, I think with VAT. So, I need to multiply sales persons’ price with 1,24 to have the prices I understand.

In Excel this can be done in multiple ways.

Write a formula to add 24 % as above.

Another option is to write a custom function. Function is a predefined calculation which returns you a value based on input value(s).

Z_vat is a function. We need to have price to calculate Z_vat. Simply, price multiplied by 1,24 equals to Z_vat meaning the price with value added tax. Save the function in the personal workbook and the custom function is available in all the Excel workbooks.

Use Z_vat as any other function.

Also LAMBDA can be used for calculating value added tax price.

Select formulas and name manager.

The first argument in LAMBDA is price and second price multiplied by 1,24.

This is done with LAMBDA.

In this example, custom function with VBA might have been handier than LAMBDA. Still, LAMBDA offers lots of possibilities. VBA can get long and complex, then LAMBDA is better solution.

Business Intelligence

5M rows in MS Access

If you have a large data set consisting more than 1 M (=million) rows, it is not useful to download to Excel worksheet. You can use text file and then create a Power Query based on the text file. However, Access is my choice for storing this large data amount.

I have downloaded data from eforexcel.com page. Thanks for organizing the sample data. I chose 5M rows of HR data.

Create a blank database and download the data from an external source. The path is external data – new data source – from file – text file.

Import the text file, just activate the first row containing field names. Luckily data falls under correct headers.

In my version, I changed the decimal symbol from comma to dot. Then it is not confusing as field delimiter is comma.

It took some time to get data loaded, but it still works. Now I have 5M records in Access in one table.

I can still check with SQL script the total number of records.

Different education records are here:

Let’s see how many hits.

Data was divided evenly.

This is the SQL script

SELECT educationfield, COUNT(educationfield)

FROM 5M

GROUP BY educationfield;

A data model with 5M records was downloaded into Excel.

The same results can be found in Excel too.

Let’s make another check. Do genders differ when it comes to business travelling ?

The business travelling is pretty evenly divided.

SELECT gender, businesstravel, COUNT(businesstravel)

FROM 5M

GROUP BY gender, Businesstravel;

If you need to make an adhoc analysis for a data with 5M records, Access is a viable choice for storing the data. The reporting was done in this example in Excel. All the data was stored in one table and no relations were needed. One thing to notice, the Access file with the data was quite big 2 GB. That is taking capacity from the hard disk.