5 Replies Latest reply on Mar 22, 2017 1:16 AM by jasongogal

    Excel Import Question

    DanKennedy

      Is there a way to limit the number of decimal places displayed when importing tag values into Excel?  I tried to apply  this formula in the Filter expression =ROUND(Sheet1!$D$4,2) all that is returned is a single zero.   Currently when it imports it shows me 10 to 12 decimal places and I would like to limit each column to 2 decimal places.

       

      Thank you,

       

      Dan

        • Re: Excel Import Question
          rkoonce

          Hello Dan,

           

          One method for your consideration is to use the number formatting in MS Excel. After selecting the number format for the cell, the buttons highlighted can be used to control the number of significant digits displayed.

           

          Cheers!

          Richard

            • Re: Excel Import Question
              DanKennedy

              Hi Richard,

               

              Thank you, the problem is each time I refresh the data is the PI import over-writes the Excel decimal formatting.  I was hoping there was a way to right a filter expression in Pi  Import function to prevent this.

               

              Thank you,

               

              Dan

                • Re: Excel Import Question
                  rkoonce

                  Hi Dan,

                   

                  Try editing the cell directly, like so =ROUND(PICurrVal(Sheet1!$D$4,2),2) and use <CTRL>+<Shift>+<Enter> to complete the edit. Using <F9> or <CTRL>+<Equal> will recalculate/ refresh the data without changing the edited formula. However, if you go to the DataLink's ribbon for "Current Value" and click <Apply> or <OK>, this would overwrite your edit, removing the ROUND function.

                   

                   

                  For the curious that might read this and wonder...Why <CTRL>+<Shift>+<Enter>? It is because the DataLink function is an array formula. You can easily identify arrays by examining the functions in the formula bar, noting that the functions are being encased in { }. If you try to edit an array and use the <Enter> key, you will be presented with this dialog:

                  ... But, actually, you can edit an array using the keys I mentioned above... so the dialog is actually a bit misleading.

                   

                  Cheers!

                  Richard

                  1 of 1 people found this helpful
              • Re: Excel Import Question
                jasongogal

                You can refresh the cell(s) in question with a macro function as well. I found a way to record a macro of updating a cell, but you can also use the VBA that is bundled in the example spreadsheet in the Datalink supporting files.