Open Office migration
upgrade from earlier versions to 3.2.0 or later.
If you have an
estimating program that is version Easy-Pro estimator V8_0 or earlier.
Then if you upgrade to the latest OpenOffice you will find that your old file/s
have the error #VALUE throughout the Summary page and all the
Worksheets.
Open Office have changed the way a
formula reads cells and so a Totals formula will no longer read text strings.
The formula will only read numeric. If there is text in a cell that the formula
calculates on, then the #VALUE error shows in the Total column. See example
below.
| Per |
Labour |
+Materials |
+ Other |
x Unit |
=
Total |
| Estimate |
$450.00 |
$250.00 |
|
num? |
#VALUE! |
| Get a Quote |
|
$500.00 |
|
2 |
$1,000.00 |
| Hour |
|
|
$3,500.00 |
1 |
$3,500.00 |
| Each |
|
$1,250.00 |
|
0 |
-$0.00 |
| Hour |
|
|
|
|
|
This is part of
your typical worksheet. The formula in the 1st and 2nd xUnit column
is =IF((C3+D3+E3)>0;"num?";0) If a figure is put in Labour or Materials or
Other, then the formula was designed to show "num?" to remind you that you must
put a number 1 or more in the xUnit column to make the Total formula work. When
you enter a figure as in the 2nd column the Total calculates. But as you can see
in the 1st Total column, having the text "num?" in the xUnit column causes the
error #VALUE to show. This is then reflected all the way to the Summary page as
well.
In the 3rd and 4th and 5th
cells of the xUnit column above I have changed the formula to
=IF((C6+D6+E6)>0;-0.00000001;0) . This will work in a similar way to
remind you to always enter a number in the xUnit column but it will not create
the #VALUE error in the Total column. In the 4th cell of the xUnit column you
can see that a 0 has appeared and there is a -$0.00 in red in the Total column. Using this formula will
multiply the Labour plus Materials plus Other cells by the xUnit
entry. As soon as you make an entry in Labour or Materials or Other ,
the formula will cause a 0 to appear in the xUnit column and it will multiply
the figure you put in by a negative small fraction of a cent to create the red
negative zero dollars in the Total column. This shows you very clearly that you
need to enter a figure in the all important xUnit column to trigger the
Total. If you keep prices in your template program with no entry in the xUnit
column, the small fraction of the cent if left will have no impact on your
Summary Pricing figure.
So the quick fix to your old job program files
showing the #VALUE error is to do either of the following to all your Worksheets
from Preliminaries through to Spare 4
Choice
1
Just highlight right click and Delete > Okay all the empty
cells that have the old formula in the xUnit column.
Choice
2
Type this
formula =IF((C6+D6+E6)>0;-0.00000001;0)
into a blank cell in the xUnit column (F3 to F28)
Click on and Copy (crtl C)
this cell then highlight all the empty cells in the xUnit column and Paste
(crtl V)
Do this to all your
worksheets.
You may need to do the above to entries in
your lower Calculations areas on your Worksheets as well.
Now you should
also do this to your master Template file so the next job you start will not
show #VALUE throughout.
Ubuntu Linux users
I noticed
a further cause of the #VALUE error showing up in Ubuntu Linux. If the Space bar
has been used to blank out an entry in the cells of Labour or Materials or
Other, then this also causes #VALUE to show in the Total column. To fix this
just highlight the empty cells that may be causing the problem and Delete
> Okay .
Hopefully this has been of asistance to anyone having
this problem.
Return to Home