Skip to main content
A
akelly12-Amethyst
12-Amethyst
October 6, 2020
Question

Excel Component - is there a better work flow for processing external data?

  • October 6, 2020
  • 1 reply
  • 2890 views

Using Mathcad 15 to do curve fitting of measured data. It's pretty tedious to down-select a subset of the data, analyze it and publish the results. over-and-over-and-over again (e.g. every time the test laboratory runs a new test).  Wondering if there's a faster way.

 

I'm using the Excel Component rather than File Input (or other means of getting external data into Mathcad).

 

I have a bunch of test data in Excel. Every test is stored in a different file.  Every file has different numbers of rows.  Inside the data are several events.  I'm only interested in doing data analysis between two specific events. Meaning the size of the data I'm interested in and its location in the file is variable every time the test is run.

 

My work flow looks something like this:

  • Excel
    • save-a-copy of my pre-processing / post processing template.
    • Copy / paste the data from the test lab's Excel file into my template.
    • Inspect the template for the measurements that indicate the start and end of the event.  Note the start/end rows of the data of interest.
      Note: I haven't figured out how to do this by equation - it requires external information and a bit of "engineering judgement".
  • Mathcad
    • save-a-copy of my curve-fitting template
    • double-click on the excel component to activate it.
    • delete the data in the excel component
  • Excel
    • Copy the subset between the start and end of the event
  • Mathcad
    • Paste the data from the excel template into the excel component
    • Note the number of rows in the pasted data
    • update the output ranges so it matches the size of the pasted data
    • Inspect the results of curve fits to various equations
    • choose a "winner" by inspection. I'll call them outputs A and B.
  • Excel
    • copy / paste Mathcad Output A into the Excel Template.
      All of the dependent formulas and plots are updated after pasting
    • Copy / paste the plots of the post-processed data into PowerPoint or Word.
  • PowerPoint / Word
    • Paste the Excel plots
    • Paste the Mathcad Output B value.

Yes, I'm a Mathsoft hold-out.  I like the Word processing features that still haven't been ported to Prime 6 and Mathsoft's plots still look more professional.

    1 reply

    L
    LucMeekes23-Emerald IV
    23-Emerald IV
    October 7, 2020

    Yes, there is. 

    You shouldn't need to go through Excel before your first operations in Mathcad.

    Display, tabulation and selection can also be done in Mathcad. If Excel can Read a file, Mathcad can (be tought to) do the same.

     

    Why don't you share an example?

     

    Luc

     

    A
    akelly12-AmethystAuthor
    12-Amethyst
    October 8, 2020

    Attached.  This comes from an HP/Agilent data logger, I think.  Someone else is taking the data and sending me the spreadsheets.  Every run will have a different number of scans.

    1. normalize the measured data as Output/Input.  Call it Output.normal
    2. find the null offset.  In THIS data set, it's an Output.null is an average of Output.normal from Scans 1-71, however this is variable from test to test.
    3. Isolate the "events" of interest.  There are two.  In THIS data set, Event 1 happens between scans 75 and 1887 or 1888.  Event 2 happens between scans 1892 and the end of the data.
    4. Note the time at the start of Event 1.  Create a new column Time.elapsed such that the start of Event 1 happens at Time.elapsed=0 minutes.
    5. Perform curve fits of Output.normal_Event_1 vs.Time.elapsed.  I don't have a universal function to describe this event, so I'm doing curve fits to about 7 different equations.  Doing a visual inspection of all of them and using engineering judgement to decide which one of them best describes the measured data. Some of the curve fits are unusable (e.g. undefined for some values of Time.elapsed, e.g. produces imaginary numbers, etc.) 
    6. Get Output.start from the best curve at Time.elapsed=0.  Get Output.end from the best curve at Time.elapsed=10 minutes.
    7. Calculate Stability=(Output.end-Output.start)/Output.start
    8. Create a new column as Output.change=(Output.normal_event_1-Output.start)/Output.start
    9. Create a new column as Output.recovery=(Output.normal_event_2-Output.null)/Output.start
    10. Plot #1 has 2 series.  Output.change vs. Time.elapsed for event 1. And the curve fit over the same time scale.
    11. Plot #2 has 1 series. Output.recovery vs. Time.elapsed for Event 2.
    Terms & ConditionsCookie settingsAccessibility statement