As a complete javascript novice I've been trying to duplicate the formulae in an operating Excel file, in an Acrobat 'form'. Below is an example of the typical Excel formula (of which there are x40), with reference to calculations performed on various cell data :
=D14-(D22*1000*0.02)/($T$20*35.6)-$M$5
I have attempted to use Acrobat 'custom calculation script' to perform the same process and arrived at this:
Sorry, I confused the issue. In the Excel file there are some referenced cells "D12, D22, D32, D42, D52" - that all have a set value 0.326. I thought that it would be easier for me to 'read what was going on' if I substituted the Cell ref for its set value.
the sum I get from the above calc has to be converted from metres to feet
I think you need to break it down and look at the exact detail. The formula looks plausible, but of course we don't know what's in your fields or what you see. Take one case where it doesn't do what you expect.
-Tell us the value Excel gives and the value found in D14, D22, T20 and M5.
-Tell us the value Acrobat gives.
-And tell us the value of 22_AWG01, Total_current and Metres
(As well, of course, as telling us why you've replaced the reference to D22 by the constant 0.326)
Test Screen Name, thanks for replying. I'll do as you ask.
The Acrobat form is to calculate voltage drop over a 'custom' user entered 'wire length (max 2m)' & user entered 'wattage' figure.
A figure is calculated for each of the different wire gauge (AWG) sizes: 22 (0.326mmsq), 20 (0.5mmsq), 18 (0.75mmsq), 17 (1.0mmsq), 15 (1.5mmsq), 13 (2.5mmsq), 11 (4mmsq) and 9 (6.6mmsq)
As you indicate, it's my data entry into the javascript that's at fault
Hi Test Screen Name I've been through the figures, and the calculations do appear to be correct. The main issue I now think is that the x80 fields containing calculations don't all update simultaneously when the custom user data is inserted. As I've been working on this project I found the first & second rows (2x8=16 fields at the top of the page) were calculating instantly - as I scrolled down the page, what I thought were incorrect calculations (eg my errors) were fields that hadn't yet re-calculated. If I retun to the pdf 15 seconds later all the calculations have updated correctly. I have been through and checked the field calculation order which luckily, for the most part, followed my construction of the field layout.
Is there a way I can speed up the calculations, or add a button to click to 'Update all fields'? As there are a few more issues to resolve on this project I'm happy to reimburse for time spent helping. Attached is the 'bare bones' design of the form showing the fields which I intend to copy into a designed layout. Steve
Hi Bernd I have looked into this. Would you suggest performing all the 'add/multiply/subtract/division' calculations before performing the conversion (metres to feet) calculations?
Don't try and follow rules about what kind of calculation. It really is the order of calculation, and only you can know this information. (Ironically, Excel can know this without you tell it!)
In a simple case consider a form like this
Item 1 price ... A
Item 2 price ... B
Subtotal ... C C=A+B
Tax .... D D=C*0.2
Total to pay .... E E=C+D
You can see you cannot calcuate E until you have calculated C and D. You cannot calculate D until you have calculated C. And so on. We know this insinctively as humans, and we also tend to go from top to bottom. Acrobat is not smart, and does not automatically go from top to bottom, so we have to tell it the right order. If not sure, just work through on piece of paper without making any assumptions.
I think I've ordered the calcs correctly, but will keep experimenting to see which way offers quickest response. After further searching I found this 'this.calculateNow()' which I have assigned to a button (labelled 'Click to refresh') next to the two user input fields, which seems to satisfactorily force the calculation update.
I've seen that it's possible to attribute a background colour to a cell using 'conditional formatting'. Is it possible through a condition to change a calculated field result into a text string. If the field result is below a certain figure (x8 different instances) I'd like the field to turn red, with white text saying N/A?
Thanks both for your contributions. Unfortunately this week I've found out that the form/calculations have been updated to include a further x2 input variations. Given that I was struggling with the simpler form (and I have no further time to experiment), I think it would be wise to commission a third party to produce the form for me.
10
Replies
AdChoices