Sir Clive Sinclair...(and other stuff)
Pages: 12
excel is one of the best programs ever written.
at some point in a lab a teacher asked for the travelling salesman / greedy approach in excel. I did it with a 1 line formula + a button to sort the input by cost. Even modern C++ needs a little more than that. Ive used it for all kinds of things, but more often for string manipulation / file cleanup than math. Its great at math, I just rarely think to fire it up for that, unless I also want a plot.
at some point in a lab a teacher asked for the travelling salesman / greedy approach in excel. I did it with a 1 line formula + a button to sort the input by cost. Even modern C++ needs a little more than that. Ive used it for all kinds of things, but more often for string manipulation / file cleanup than math. Its great at math, I just rarely think to fire it up for that, unless I also want a plot.
Last edited on
helios, I am also interesting in handling amounts of data that are too annoying to process by hand, but not worth writing a one-time-use script for. Can you give an example of a significant text/data manipulation task that is easier to solve in excel than, say, a Python/C#/bash script?
Well, for instance I have a program that records a temperature and saves it to a CSV once a minute. Say I want to take a bunch of measurements and reduce the resolution by 60 times. I start on B30 "=average(A1:A60)" and copy that to every cell down to the bottom. Then on C31 down I write "=mod(B30+1; 60)", then on D "=if(C30=0;B1;"")". Then I take all of column D and filter out all empty lines in a text editor.
I still need a better method for the last step than replacing "\r\n\r\n" with "\r\n" until there are no more matches. It's rather slow.
Other stuff it's also good for it's sorting, and trying out different formulas to see how they fit with your data.
I still need a better method for the last step than replacing "\r\n\r\n" with "\r\n" until there are no more matches. It's rather slow.
Other stuff it's also good for it's sorting, and trying out different formulas to see how they fit with your data.
But Helios, if you want to do moving averages on the first column of a csv file it's a simple python script:
You can improve that for large datasets with a rolling sum, but it's a bit prone to round-off error.
If I want to plot a simple graph from columnar data I'd do it in gnuplot as a one-line statement:
Failing that, again I'd use a simple python script:
I think Excel is actually a bit over-rated. It's the fact that you have to load data into it in the first place, manually copy formulae cell-to-cell, and it's extremely hard to script.
import numpy as np
LENGTH = 60
a = np.loadtxt( "data.csv", usecols=(0), unpack=True, delimiter="," )
f = open( "data.out", 'w' )
for i in range( 0, a.size - LENGTH + 1 ):
print( a[i:i+LENGTH].sum() / LENGTH, file=f ) |
You can improve that for large datasets with a rolling sum, but it's a bit prone to round-off error.
import numpy as np
LENGTH = 60
a = np.loadtxt( "data.csv", usecols=(0), unpack=True, delimiter="," )
f = open( "data.out", 'w' )
sum = a[0:LENGTH].sum(); print( sum / LENGTH, file=f )
for i in range( 1, a.size - LENGTH + 1 ):
sum += a[i+LENGTH-1] - a[i-1]
print( sum / LENGTH, file=f ) |
If I want to plot a simple graph from columnar data I'd do it in gnuplot as a one-line statement:
p "data.txt" u 1:2 w l |
Failing that, again I'd use a simple python script:
import numpy as np import matplotlib.pyplot as plt x, y = np.loadtxt( "data.txt", unpack=True ) fig, ax = plt.subplots() ax.plot( x, y ) plt.show() |
I think Excel is actually a bit over-rated. It's the fact that you have to load data into it in the first place, manually copy formulae cell-to-cell, and it's extremely hard to script.
Last edited on
If that's a joke, it's very well executed.
If it's not a joke, your "simple script" perfectly exemplifies why I do this in Excel instead of coding something. Something like that would take me at least two or three minutes, and that's if I don't make any mistakes. With a spreadsheet it takes me like thirty seconds at most. You just need to compare the total number of tokens in each.
(Also I hate Python, but that's neither here nor there.)
If it's not a joke, your "simple script" perfectly exemplifies why I do this in Excel instead of coding something. Something like that would take me at least two or three minutes, and that's if I don't make any mistakes. With a spreadsheet it takes me like thirty seconds at most. You just need to compare the total number of tokens in each.
(Also I hate Python, but that's neither here nor there.)
Got curious, did websearch for "R moving average". (I don't use R.) First hit: oneliner. Bit lower: https://www.storybench.org/how-to-calculate-a-rolling-average-in-r/
I'd say that the times you need to repeat the same procedure might affect more whether you "script it" than just the size of data.
I'd say that the times you need to repeat the same procedure might affect more whether you "script it" than just the size of data.
it depends on what you have. you can write, in any language, something to add the formula to an output file so that you get a csv you can open in excel that is ready to read/print/use. Its just simple text processing, repeating the formula with the cell number updates, maybe a total or whatnot extra entry. file opens and is populated.
the basic code in excel is easy and potent too.
py is awesome, though slow. excel can be slow too, depends on what you are doing again.
I can't see one as better than the other for general purpose/simple data dump stuff.
the basic code in excel is easy and potent too.
py is awesome, though slow. excel can be slow too, depends on what you are doing again.
I can't see one as better than the other for general purpose/simple data dump stuff.
I hadn't used Python until last year when I did the Google IT Automation with Python Professional Certificate for fun. I wouldn't say it's a brilliant language but at some point I want to dig deeper into it.
| People underestimate Excel, but other than it's clunky syntax it's actually quite capable. |
"Clunky syntax" is an understatement. I had to find a Youtube video just to figure out how to make a flipping graph for ten data points.
My boss wanted me to use it, otherwise I would've used pencil, tri-square, protractor, and paper. So much easier.
As for Python, I'm just learning it, and all I can say so far is that it's fairly easy to learn.
Topic archived. No new replies allowed.
Pages: 12