Dear All,
I would like to demonstrate how the well known Excel function VLOOKUP can fail.
Let's assume that we are looking for the value 21.01 in column C and that we want to return the value in column D.
I have created a dropdown list to be sure that the value I am picking up is exactly like the one that is in column C and I have entered my VLOOKUP function to get the letter in column D
=VLOOKUP(C12,$C$3:$D$8,2,0)
As expected, selecting from the dropdown menu 21.01, the VLOOKUP function returns the letter W.
I have then entered in column F three numbers: 7, 0.01 and 14 which the sum is 21.01. In column C I have entered the formula =SUM(F3:F5) [F3:F5 contains the numbers] and in column D there is again my VLOOKUP formula =VLOOKUP(C13,$C$3:$D$8,2,0).
Surprisingly, VLOOKUP returns #N/A.
However, the same example using the number 21.02 doesn't return an error.
How is this possible? Why VLOOKUP is not working? Why the first 21.01 from the dropdown list is different from the 21.01 as a result of a formula?
Here the explanation:
If we consider the decimal part of the two numbers 0.01, we can discover that they are actually different.
The 0.01 from the dropdown list is recognized by Excel as 0.010000000000002 while the one that is calculated is 0.009999999999998. Why is this happening?
This problem occurs because of the precision of your computer, which must represent and manipulate numbers in binary. Microsoft Excel compares the exact binary representation of the numbers, rather than their decimal equivalents, which are displayed on the screen. Therefore, rounding errors can occur in the binary representation of the numbers that are not evident when you compare the decimal values visually.
When storing numbers, a corresponding binary number can represent every number or fractional number. For example, the fraction 1/10 can be represented in a decimal number system as 0.1. However, the same number in binary format becomes the repeating binary decimal 0001100110011100110011 (and so on) and can be infinitely repeated. This number cannot be represented in a finite (limited) amount of space. Therefore, this number is rounded down by approximately -2.8E-17 when it is stored.
All numbers expressed in floating-point format are rational numbers. Irrational numbers such as π or the square root of 2, or non-terminating rational numbers must be approximated. The number of digits of precision also limits the accuracy of the numbers. Excel store 15 significant digits of precision. For example, the number 1234567890123456 cannot be exactly represented if 15 digits of precision are used.
Many combinations of arithmetic operations on floating-point numbers may produce results that appear to be incorrect by very small amounts. For example, the equation
=1*(0.5-0.4-0.1)
may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0. This is due to the fact that the IEEE 754 standard requires numbers to be stored in binary format. As I described earlier, not all decimal numbers can be converted exactly to binary, as in the case of 0.1. The conversion caused the loss of precision.
Going back to our example, we can see that the 21.01 from the dropdown list is exactly the same of the original list in column C [which is the source for that number], which means that it is affect by the same error, while the one that is calculated is different.
If we convert the decimal part of both numbers as a binary number, we get the following:
Typed 0.01 = 000000101000111101011100001010001111010111000011000000
Calculated 0.01 = 000000101000111101011100001010001111010111000010000000
as you can see, the last 7 digits are different, therefore the numbers are considered by Excel as two different numbers.
To correct the precision error we can change our VLOOKUP formula as follow:
=VLOOKUP(TRUNC(C13,2),$C$3:$D$8,2,0) or =VLOOKUP(ROUND(C13,2),$C$3:$D$8,2,0)
I hope this helps all of you who incurred in this error.
Best,
Paolo
Excel VBA Macros
Excel, VBA, Dashboard, Excel Dashboard
Search This Blog
Monday 12 August 2013
Tuesday 16 April 2013
Conditional Formatting on a filtered table every Nth rows
Dear All,
Sometimes happens that we have to format a filtered table, with one or more filters applied and every Nth rows.
In this situation it would be nice if the formatting could change automatically and accordingly with our filters. Unlikely, the solution it might not be so straight forward due to the non hidden rows.
The first approach could be to use the well known formula below, changing the MOD function to much our Nth rows:
=MOD(ROW(),2)=1
but unfortunately this formula is not working on a filtered table due to the hidden rows.
Changing the formula as:
=MOD(SUBTOTAL(3,$B$6:$B$110)-SUBTOTAL(3,$B8:$B$110),5)=4
where B8:B112 is our range of data [please change it accordingly with your data].
In the example above the conditional formatting is applied every 5 rows.
Please find below a more fancy solution with custom Nth rows: https://docs.google.com/file/d/0B2P1gDTiLkkSY3Npay1tN1dZMnM/edit?usp=sharing
Best,
Paolo
Sometimes happens that we have to format a filtered table, with one or more filters applied and every Nth rows.
In this situation it would be nice if the formatting could change automatically and accordingly with our filters. Unlikely, the solution it might not be so straight forward due to the non hidden rows.
The first approach could be to use the well known formula below, changing the MOD function to much our Nth rows:
=MOD(ROW(),2)=1
but unfortunately this formula is not working on a filtered table due to the hidden rows.
Changing the formula as:
=MOD(SUBTOTAL(3,$B$6:$B$110)-SUBTOTAL(3,$B8:$B$110),5)=4
where B8:B112 is our range of data [please change it accordingly with your data].
In the example above the conditional formatting is applied every 5 rows.
Please find below a more fancy solution with custom Nth rows: https://docs.google.com/file/d/0B2P1gDTiLkkSY3Npay1tN1dZMnM/edit?usp=sharing
Best,
Paolo
Tuesday 21 August 2012
Project Revenues and Costs Tracking Dashboard
Dear All,
I am very happy to announce one of my latest projects I did for a customer.
My customer needed a dashboard to sort out the following issues:
The dashboard is the page were data are analysed and it consists of different sections
1.1 Select Project:
In this area you select the project you want to analyse the data for, in the example above the data are shown for a project called Pluto.
On the left side of the page you can see a menu. Buttons will help you to navigate through the spread sheet.
1.3 Quick Summary:
In this section there are three main information about the project.
The number of year shows since how long the project is active, based on the first transaction [income or expenses] of the Database sheet. This will be discussed later. 2. Database
Open balance shows the profit or loss of the project when is created [i.e. for past projects that you want to import in the dashboard]. The balance is setup on the Database sheet.2. Database
1.4 Profit Trend:
This section shows a graph of the profit of the project for the past 10 year only.
The profit is split by year and the amount per year is shown on top of each bar of the histogram
1.5 Income, Expenses and Profit Summary:
This section has 3 subsections to show:
1. The income:
a. As total income for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]
2. The expenses:
a. As total expenses for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]
1. The profit:
a. As total profit for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]
This section shows a quick snapshot of the past 10 years Income, Expenses and Profit.
The total Income, Expenses or Profit is split by year and the icons show the trend compared to the highest income in a specific year.
Please note that this tables do not show the total income, expenses or profit for the all project life but for 10 years only.
This is one of the most important section of the dashboard.
Staging Tables section. Please note that if the percentages are all the same, Income, Expenses and Profit should show the same values. 3. Staging Tables
The Database is the core of the financial model and here is where the magic happen.
As you can see from the picture, there are two main coloured area.
The areas shaded in yellow contain formulas and do not need to be updated, whole the striped area need to be manually filled.
The striped area contains the following fields:
2.1 Date:
This is the date of the transaction
2.2 P&L:
P&L represent the type of transaction you want to enter.
Two types are allowed:
I. Income
II. Expenses
We choose income if we receive money in, while we choose expenses if we pay money out.
Income or Expenses is selectable from a dropdown menu.
2.3 Project:
Project is the name of the project for what we want to allocate an income or expense.
The project is selectable from a dropdown menu and projects are setup on the Staging Tables section. 3. Staging Tables
2.4 Type:
3.2 Active Staff Table:
This is also a very complex table because the setup will affect from when the Income, Expenses and Profit will be split.
In this section there are 3 other tables that are used for calculations and further setup.
To download the dashboard please click here: Revenues and Costs Tracking Dashboard
Please save the Dashboard on your Computer before using it. Once you have saved the file, unzip it using 7z [http://www.7-zip.org/] and install it.
Following on from the above also note that LockXLS add-in will execute the file with Excel.
I hope you enjoy the spread-sheet.
Paolo
[Copyright © 2012, Paolo Succo]
I am very happy to announce one of my latest projects I did for a customer.
My customer needed a dashboard to sort out the following issues:
- Tracking his revenues and costs by project showing to total profit
- Showing the past 10 years revenues and costs split by year
- Splitting the revenues and costs between the active staff based on the percentage of contribution to costs by staff. Inactive staff should not be counted for revenues and costs
1 Dashboard:
The dashboard is the page were data are analysed and it consists of different sections
1.1 Select Project:
In this area you select the project you want to analyse the data for, in the example above the data are shown for a project called Pluto.
Projects are selectable
from a drop down menu and it is possible to analyse only one project at time.
On the left side of the page you can see a menu. Buttons will help you to navigate through the spread sheet.
Hovering the mouse on
the button will show you were it is pointing.
1.3 Quick Summary:
In this section there are three main information about the project.
The number of year shows since how long the project is active, based on the first transaction [income or expenses] of the Database sheet. This will be discussed later. 2. Database
Open balance shows the profit or loss of the project when is created [i.e. for past projects that you want to import in the dashboard]. The balance is setup on the Database sheet.2. Database
Total profit show the profit for the project as at today.
1.4 Profit Trend:
This section shows a graph of the profit of the project for the past 10 year only.
The profit is split by year and the amount per year is shown on top of each bar of the histogram
1.5 Income, Expenses and Profit Summary:
This section has 3 subsections to show:
1. The income:
a. As total income for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]
2. The expenses:
a. As total expenses for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]
1. The profit:
a. As total profit for the full project life since the beginning of the project to today
b. As a comparison with the same period for the previous year [saying period means from January to today]
This section shows a quick snapshot of the past 10 years Income, Expenses and Profit.
The total Income, Expenses or Profit is split by year and the icons show the trend compared to the highest income in a specific year.
Please note that this tables do not show the total income, expenses or profit for the all project life but for 10 years only.
This is one of the most important section of the dashboard.
In this section is
shown:
a. The staff names
b. The Income, Expenses
and Profit contributions based on the percentages of risk agreed on the
Staging Tables section. Please note that if the percentages are all the same, Income, Expenses and Profit should show the same values. 3. Staging Tables
2 Database:
The Database is the core of the financial model and here is where the magic happen.
As you can see from the picture, there are two main coloured area.
The areas shaded in yellow contain formulas and do not need to be updated, whole the striped area need to be manually filled.
The striped area contains the following fields:
2.1 Date:
This is the date of the transaction
2.2 P&L:
P&L represent the type of transaction you want to enter.
Two types are allowed:
I. Income
II. Expenses
We choose income if we receive money in, while we choose expenses if we pay money out.
Income or Expenses is selectable from a dropdown menu.
2.3 Project:
Project is the name of the project for what we want to allocate an income or expense.
The project is selectable from a dropdown menu and projects are setup on the Staging Tables section. 3. Staging Tables
2.4 Type:
Type is the kind of
expense.
Names on this field
should be kept as mu as possible consistent and is absolutely compulsory the
name "Open Balance" to allocate an open balance to a project.
Open balance affects the
amount that appear on the Quick Summary section, please check the link below if
you need more information about this section. 1.3 Quick Summary
This field contains the
name of the person who enter the transaction.
This field contains the
name of the client or supplier.
This field can be
eventually used for further analysis like income/expenses by client/supplier
and so forth.
The description shows
what is a transaction for.
More details are added
on the description and more it is easier to remember what was a transaction
for.
This field contains the
amount of the transaction.
3 Staging Tables:
The Staging Tables
section contains the setup for the dashboard.
Several tables are in
this section. Some of the tables are for calculation only while others are for
proper setup of other sections like Dashboard or Database.
This table contains the
name of the projects.
Adding a project here,
will automatically add it on the dropdown menu of the Database section/Project
field, and it will be selectable for income or expenses allocation.
3.2 Active Staff Table:
This table is vital for
the Income, Expenses and Profit allocation.
In this section there is
the setup of the percentages used to split the Income, Expenses and Profit
between the staff.This is also a very complex table because the setup will affect from when the Income, Expenses and Profit will be split.
The table has the
following columns:
This column contains the
name of staff. Active staff will appear on the 10 Year Tables on the dashboard
section. 1. Dashboard
This column contains the
percentages used to calculate the Income, Expenses and Profit for each member
of staff.
Please note that the Total
Active Staff Sharing should be 100% otherwise it means that costs are over
or under split.
This column makes a
member of staff Active or Inactive. Only active members are used for the
income/cost calculations.
This column is vital
because allows to setup from when a member of staff will take part of the
income, expenses and profit sharing.
i.e. in the table above
none of the members will get sharing of Income or Costs for transactions that
happened before the 01/07/2003.
This column is vital
because allows to setup up to when a member of staff will take part of the
income, expenses and profit sharing.
i.e. in the table above
non of the members will get sharing of Income or Costs for transactions that
happened after the 31/12/2013.
The From and To columns
can be used to setup the length of a contract for a specific member of staff.
This column is a
calculate field and based on the column active, it will recreate a list of
active members only.
Active members appear on
the Current Band Staff and Income/Costs Allocation Based on Contribution Percentage
section on the dashboard.
This table contains the
project for what you want to track costs.
If you need to add a
project, please do that on the first empty row below the table. The table will
expand automatically and the whole dashboard will update accordingly.
In this section there are 3 other tables that are used for calculations and further setup.
The first table called Dates
calculation for last year income and expenses is used on the calculations
on the Income Summary and Expenses Summary on the Dashboard section. 1. Dashboard
The second and the third table
are used to calculate the 10 years period and to change automatically the title
of the graph on the dashboard section in the next years
To download the dashboard please click here: Revenues and Costs Tracking Dashboard
Please save the Dashboard on your Computer before using it. Once you have saved the file, unzip it using 7z [http://www.7-zip.org/] and install it.
Following on from the above also note that LockXLS add-in will execute the file with Excel.
I hope you enjoy the spread-sheet.
Paolo
[Copyright © 2012, Paolo Succo]
Saturday 28 January 2012
Timesheet Dashboard
Dear All,
I am very happy to announce I have created a “Timesheets” Dashboard application.
Unlike many versions you can find online are working perfectly, I have developed a new timesheet format. Timesheet 2012 Dashboard has a new and more user friendly way to fill out your timesheet.
Timesheet 2012 Dashboard is a dynamic spread-sheet and needs the macros enabled to be used. If you do not know how to enable the macros, please follow the instruction at the link http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx
The dashboard has two dynamic menus which allow you to actively interact with the spread-sheet. The first menu, called Active Control and shown below, gives you the option to select a specif month or all the month. This can be very helpful to avoid to scroll up and down your timesheet or create a PDF of your timesheet.
The second menu, called User Menu and shown below, has 6 buttons:
1. Update: in spite of your timesheet is automatically updated [not in this Demo version, you will get an error saying it cannot find the server instead] as soon as you open it, this button allows to perform a further manual update. Please note that the Demo version is for demostration only. There is a final version which can be used on an intranet to manage the all companies timesheets
2. Email Admin: this button opens your Outlook and allows sending an email to Admin, copying another address as well. You should email Admin if you need help with your timesheet
3. Create PDF: this features allows to create a PDF of the visible area in your timesheet [Selecting All on the Active Control you will PDF the 12 months while selecting only one month you will PDF only the selected month]. The PDF will be automatically saved on your PC with the format FileNameCurrentDate.pdf [Document folder or desktop]
4. Quick timesheet: this button is the most powerful tool on the new timesheet. This control allows to select a range and fill it out with the same number of hours. [further explanation below]
5. Save/Close: Save and close button. Please use this button to save your timesheet and resetup Excel with the normal view
6. Admin: only Admin is allow to use this tool. The spreadsheet automatically recognize who is logged in
The Quick Timesheet tool:
In spite of you still should fill out your timesheet in the “old fashioned” way, and which I strongly recommend to do, I have developed a tool to fill out the timesheet faster.
As already aforementioned, this tool allows you to fill out a range of cells with the same number of hours.
In the example below, the days from 2 to 6 and from 9 to 11 have been selected and the Quick Timesheet Tool will fill them out with 8 hours a day as soon as the OK button is clicked.
You can enter letter as well, i.e. H for Holiday and so forth
IMPORTANT:
On top of your timesheet there are some information about the staff. Title and Location have a scroll-down list [the list is dynamic and updatable if connected to the server].
The project area:
As shown below, you can select a project number through a scroll-down list [the list is dynamic and updatable if connected to the server]. Please note that you can select a maximum 15 different projects per month. Projects without hours or vice versa, are not allowed and you cannot save the timesheet if this happen.
To download the timesheet please click here: Timesheet Dashboard
Please save the Timesheet on your Computer before using it. Once you have saved the file, unzip it using 7z [http://www.7-zip.org/] and install it.
Following on from the above also note that LockXLS add-in will be to execute the file with Excel.
I hope you enjoy the spread-sheet.
Paolo
[Copyright © 2012, Paolo Succo]
I am very happy to announce I have created a “Timesheets” Dashboard application.
Unlike many versions you can find online are working perfectly, I have developed a new timesheet format. Timesheet 2012 Dashboard has a new and more user friendly way to fill out your timesheet.
Timesheet 2012 Dashboard is a dynamic spread-sheet and needs the macros enabled to be used. If you do not know how to enable the macros, please follow the instruction at the link http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx
The dashboard has two dynamic menus which allow you to actively interact with the spread-sheet. The first menu, called Active Control and shown below, gives you the option to select a specif month or all the month. This can be very helpful to avoid to scroll up and down your timesheet or create a PDF of your timesheet.
The second menu, called User Menu and shown below, has 6 buttons:
1. Update: in spite of your timesheet is automatically updated [not in this Demo version, you will get an error saying it cannot find the server instead] as soon as you open it, this button allows to perform a further manual update. Please note that the Demo version is for demostration only. There is a final version which can be used on an intranet to manage the all companies timesheets
2. Email Admin: this button opens your Outlook and allows sending an email to Admin, copying another address as well. You should email Admin if you need help with your timesheet
3. Create PDF: this features allows to create a PDF of the visible area in your timesheet [Selecting All on the Active Control you will PDF the 12 months while selecting only one month you will PDF only the selected month]. The PDF will be automatically saved on your PC with the format FileNameCurrentDate.pdf [Document folder or desktop]
4. Quick timesheet: this button is the most powerful tool on the new timesheet. This control allows to select a range and fill it out with the same number of hours. [further explanation below]
5. Save/Close: Save and close button. Please use this button to save your timesheet and resetup Excel with the normal view
6. Admin: only Admin is allow to use this tool. The spreadsheet automatically recognize who is logged in
The Quick Timesheet tool:
In spite of you still should fill out your timesheet in the “old fashioned” way, and which I strongly recommend to do, I have developed a tool to fill out the timesheet faster.
As already aforementioned, this tool allows you to fill out a range of cells with the same number of hours.
In the example below, the days from 2 to 6 and from 9 to 11 have been selected and the Quick Timesheet Tool will fill them out with 8 hours a day as soon as the OK button is clicked.
You can enter letter as well, i.e. H for Holiday and so forth
IMPORTANT:
On top of your timesheet there are some information about the staff. Title and Location have a scroll-down list [the list is dynamic and updatable if connected to the server].
The project area:
As shown below, you can select a project number through a scroll-down list [the list is dynamic and updatable if connected to the server]. Please note that you can select a maximum 15 different projects per month. Projects without hours or vice versa, are not allowed and you cannot save the timesheet if this happen.
To download the timesheet please click here: Timesheet Dashboard
Please save the Timesheet on your Computer before using it. Once you have saved the file, unzip it using 7z [http://www.7-zip.org/] and install it.
Following on from the above also note that LockXLS add-in will be to execute the file with Excel.
I hope you enjoy the spread-sheet.
Paolo
[Copyright © 2012, Paolo Succo]
Monday 2 January 2012
Sum of cells depending on cell colour formatting
Hi All,
I recently came out with a problem using the sum function.
Imagine that you have a range of cells and you used a background color to identify certain specific cells and you wish to calculate the sum of only the cells with, for example, the red background in the range.
How can this problem be sorted out? Here the solution:
Function SumColor(DefClr As Range, DefRng As Range)
Dim MyCell As Range
Dim MyCol As Long
Dim SumColCell
'Identify the colored cell
MyCol = DefClr.Interior.ColorIndex
'Sum the colored cells in the range
For Each MyCell In DefRng
If MyCell.Interior.ColorIndex = MyCol Then
SumColCell = WorksheetFunction.SUM(MyCell, SumColCell)
End If
Next MyCell
SumColor = SumColCell
End Function
The VBA formula above as two parameters. With the first one you identify a colored cell in the range that you want to sum, the second is the range that you want to sum.
E.g.
Let's assume that this is what you have on column A:
A1. with red background
A2. with red background
A3. with yellow background
A4. cell with no background
A5. cell with no background
If you want to know the sum of only the red background cells, proceed as follow:
enter in A6 =SumColor(A1,A1:A5)
Enjoy
I recently came out with a problem using the sum function.
Imagine that you have a range of cells and you used a background color to identify certain specific cells and you wish to calculate the sum of only the cells with, for example, the red background in the range.
How can this problem be sorted out? Here the solution:
Function SumColor(DefClr As Range, DefRng As Range)
Dim MyCell As Range
Dim MyCol As Long
Dim SumColCell
'Identify the colored cell
MyCol = DefClr.Interior.ColorIndex
'Sum the colored cells in the range
For Each MyCell In DefRng
If MyCell.Interior.ColorIndex = MyCol Then
SumColCell = WorksheetFunction.SUM(MyCell, SumColCell)
End If
Next MyCell
SumColor = SumColCell
End Function
The VBA formula above as two parameters. With the first one you identify a colored cell in the range that you want to sum, the second is the range that you want to sum.
E.g.
Let's assume that this is what you have on column A:
A1. with red background
A2. with red background
A3. with yellow background
A4. cell with no background
A5. cell with no background
If you want to know the sum of only the red background cells, proceed as follow:
enter in A6 =SumColor(A1,A1:A5)
Enjoy
Wednesday 21 December 2011
Comparing Two List
Hey Guys,
having problems in ffinding duplicates between two numerical list?
Here the solution:
Download the two lists comparing spreadsheet
The spreadsheet can be used for text lists as well but it might be possible you need to reformat the texts before comparing them.
Enjoy
having problems in ffinding duplicates between two numerical list?
Here the solution:
Download the two lists comparing spreadsheet
The spreadsheet can be used for text lists as well but it might be possible you need to reformat the texts before comparing them.
Enjoy
2D & 3D Animation With Excel
Hi Guys,
please find enclosed an Excel File showing 2D and 3D animations with Excel.
Download the animation:
2D & 3D Animation with Excel
Enjoy
please find enclosed an Excel File showing 2D and 3D animations with Excel.
Download the animation:
2D & 3D Animation with Excel
Enjoy
Subscribe to:
Posts (Atom)