I'm working on improving a modeling formula and have spreadsheets worth of data to use, but my work so far has been quite tedious. I'm essentially pulling 20 rows of data from each separate sheet of a spreadsheet to put through the new formula, and I'm having to do a cell evaluation for each row rather than a single evaluation per sheet. As I said, it's getting tedious.

These can be declared first:

a = 0;
m = 34;
sheet = 1; (* which sheet of the spreadsheet I'm pulling from *)
r = 2; (* the first row I pull from *)
w = 8;
d = 4;
ex = .591;

For each sheet, these values (except r) will be the same (as I'm having to change r twenty times for each sheet). I then have this:

gs = Import["filename", {"Data", sheet, {r}, {6}}] + a
ga = Import["filename", { "Data", sheet, {r}, {7}}]

Here I pull the data from the sheet and row declared earlier, from static columns 6 and 7. From here, it's just plugging this data into the components of the formula:

y = ((gs + ga)/m)^ex
k = Gamma[1 + 1/y]
gsa = (gs/m) + 0.5
gaa = (ga/m) + 0.5
new = m*((3*N[Sum[-((Exp[-((k (c + 1))/gsa)^y] - Exp[-((k* c)/gsa)^y]) (1 - 
             Exp[-((k* c)/gaa)^y])), {c, 0, w}], 40]) + 
    N[Sum[(Exp[-((k (c + 1))/gsa)^y] - 
         Exp[-((k* c)/gsa)^y]) (Exp[-((k (c + 1))/gaa)^y] - 
         Exp[-((k* c)/gaa)^y]), {c, 0, d}], 40])
final = Ceiling[new]

Redundant statements: as of right now, for every row I want to import, I have to manually adjust r for each evaluation. That's 20 tedious adjustments per sheet, and while it takes all of, like, ten seconds to then record the final value and increase r by 1, it's just not a feasible way of churning through volumes and volumes of data.

Is there a way to split this into two cells, with the first declaring everything before gs, and the second giving the value of final for each r from 2 to 21? This would, quite honestly, be a 20x time saver. I'm just too unfamiliar with Mathematica (still on the trial, licensing when it expires) to figure out the matrix or loop (?) to save me the time.

Any help would be so, so, so appreciated.

  • 3
    $\begingroup$ Can't you import the whole spreadsheet (.xls etc.) and manipulate the data in Mathematica session then write them back, rather then read 2 rows each time? $\endgroup$ – qazwsx Oct 29 '12 at 4:00

In Mathematica when we want to do things repeatedly, we define a function that we can call repeatedly. I translated your code into a Mathematica function, which should be applicable to your problem. I use "should" rather than "will" because I don't have access to your data and I couldn't test my work.

enter image description here

I hope this helps to solve your problem. It may require some debugging before it works for you.

| improve this answer | |
  • 6
    $\begingroup$ Strictly speaking, it won't work at all. That is to say, it's a picture of code. Would it not be easier simply to paste in the code itself? (Maybe I shouldn't look a gif source in the mouth...) $\endgroup$ – Daniel Lichtblau Oct 29 '12 at 14:53
  • $\begingroup$ @m_goldberg. I see quite a lot of your answers involve screenshots of code. Any particular reason for that? Mma.se has quite a good code highlighter... Of course nobody else (but the original poster) will try out your answers, which is quite a shame. $\endgroup$ – Ajasja Oct 29 '12 at 21:05
  • $\begingroup$ @Ajasja. Yes, it's the Mathematica code editor's formatting capabilities that have seduced me to the dark side. I try to limit my use of PNGs to answers where I don't expect anyone will want to copy, paste, and evaluate the code I am posting. $\endgroup$ – m_goldberg Oct 29 '12 at 23:17
  • $\begingroup$ I think this post is good example of where a PNG makes sense. Nobody but the original poster could want to execute the code being discussed here. It wouldn't work even if it were not a PNG, but posted in markdown. $\endgroup$ – m_goldberg Oct 29 '12 at 23:23
  • $\begingroup$ Another thought. A good argument against posted even this answer in PNG format is that nobody in the community can edit it should find an error that needs correcting. It looks like I need some utility that will convert Mathematica code to good-looking markdown. Any recommendations. $\endgroup$ – m_goldberg Oct 29 '12 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.