

Ask HN: Excel experts – Formula IDE in Excel (MVP screenshot) – is it useful? - damian_r

Inspired by two discussions that happened this week about problems with Excel I thought of a simple<p>Excel add-in and would love to hear your opinion.<p>The aim is to let Excel users write formulas in simple IDE inside Excel. Instead of editing each of them in a formula bar you could just edit all formulas in one file (or for more advanced projects in multiple files).<p>What are the advantages? You could save your formulas to a separate file and import them through the add-in. This lets you easily share your formulas with others and add version control and code review to your project.<p>Checkout this awesome MVP I&#x27;ve just built in Paint:
http:&#x2F;&#x2F;i.imgur.com&#x2F;yBaFArn.png<p>Questions I was inspired by:<p>Ask HN: Language compiled to Excel spreadsheets?
https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=9941086<p>Ask HN: What are the biggest problems with spreadsheets?
https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=9935357
======
jasode
_> What are the advantages? You could save your formulas to a separate file
and import them through the add-in. This lets you easily share your formulas
with others and add version control and code review to your project._

Is this plugin meant for programmers or non-programmers? The distinction is
critical because...

 _> Inspired by two discussions [...]_

You have to be careful with criticisms about Excel made by _programmers_.
Discussion on HN will skew toward the programmer skillset.

The mindset of the non-programmers that use Excel is very different from
programmers. To many programmers, the use of Excel looks illogical. It's a
spaghetti of dependencies when formulas refer to other formulas. A complex
spreadsheet becomes a ball of mud.

Those criticisms are true but they completely miss the reason why non-
programmers find Excel compelling: the UI starts with a visual grid of the
"output".

On the other hand, a programmer's UI starts with a blank text file (ready for
the programmer to type in "do while x<10" etc.

If one studies how all these disparate groups of _non-programmers_ use Excel,
the grid's visual immediacy is the common feature that they all prioritize.
Diverse groups of non-programmers such as:

\- the Wall Street quants playing with rows & columns in financial models,

\- the project manager using Excel as a grid for itemizing tasks instead of
using MS Project,

\- the facilities manager using Excel as an adhoc inventory system instead of
learning a database product like MS Access, FileMaker, or MySQL

It's true that Excel is spaghetti but non-programmers prioritize the grid over
the resultant spaghetti. In contrast, programmers prioritize code organization
(formulas) over the visual grid. These 2 groups are not even on the same
wavelength.

Your proposed tool is helpful but it doesn't really address a burning need for
_non-programmers_. For example, your mockup shows the formulas off to the side
and disconnected _spatially_ from the cell. That separation is fine for
programmers (who can mentally connect things like CSS rules in a .css file to
elements in the .html) but not user-friendly for non-programmers.

~~~
damian_r
Thanks for this perspective.

As a programmer I haven't actually thought much about how important this link
between a cell in a grid and its formula may be.

I have seen huge (i mean HUGE) Excel files full of complex formulas being
built by non-programmers. Some even took whole night to recalculate. There was
a programmers mindset emerging from those files - extracting common formulas
to separate cells (DRY), writing 'unit' tests by using formatting (if result
for sample input is less than 0 then make the cell red - which means someone
broke the formula).

The question arises - is there a point where a non-programmer starts thinking
like a programmer, on a different level of abstraction? Is it possible to
teach non-programmers some basic programming practices to make their work
easier?

------
wjnc
I'll just relay my thoughts. It's a bit sour, but me and Excel we go way back.
Perhaps you can find something in it:

1\. Anyone who can get an add-in pasted corporate software security can also
use a better tool outside Excel. In other words: think carefully about the
customer. Users don't buy plugins in larger businesses, etc.

2\. It looks pretty useful and would help users create a bit more structure in
their sheets and calculations.

3\. My idea of why terrible Excelsheets seem to be the norm, is that 'build
once-use once' does not exist in business. Too often your boss wants you to
redo your calculation and after a few iterations, time not spent documenting /
cleaning catches up with you in Excel.

Often you also build one workflow and apply it to many not-quite the same
situations. Tweak a few cells here and there. Hence custom workflows per
worksheet and again, broken (re-)usability.

Your tool would help to start with a more long term perspective, and help in
iterating on a sheet. Might even be faster to type, than to click-point and
type.

But my baseline response would be near number 1. Plugins from Excel to R and
Python exist too, but still all the sheets I see internally, and code even
professional consultants come pitching is in VBA or some proprietary format.
The Venn-diagram between programming and Excel has no overlap imho.

~~~
kohanz
_> 1\. Anyone who can get an add-in pasted corporate software security can
also use a better tool outside Excel. In other words: think carefully about
the customer._

What other tools are available? A lot of technical people don't choose Excel,
but come into situations where Excel has been relied upon historically and the
non-technical people do not want to move away from it. So I could see such a
plugin having value in these situations (non-technical people continue to use
the spreadsheet as-is, while the programmer improves it via the plugin), which
I would imagine are quite common (I have been in this situation before).

 _> Users don't buy plugins in larger businesses, etc._

Fact or opinion?

~~~
zhte415
>> Users don't buy plugins in larger businesses, etc.

>Fact or opinion?

Users definitely buy plugins, but these bring significant functionality and
often tie-in to additional services. Financial data being the most familiar to
me. These plugins, while wrapped in VBA, are often in C++ (again, finance
world) for robustness and performance, and try to be as agnostic to whatever
nutty layout or logic a user chooses to have for their spreadsheet.

So, agree, with the pragmatic assertion that technical people love Excel as a
UI, but they're probably not necessarily developer-technical (could be, given
the richness of Excel plugins can have on some platforms), but technical in
another field and Excel is an easily hackable UI for what they really want to
do, which is mainly, as the OP suggests, using Excel to change stuff and do
what-ifs, which spreadsheets were designed to do (as if, to close the circle).

------
philbroberts
Have you checked out PowerPivot? I've been developing in Excel for 15 years
and created many a convoluted formula along the way. Two years ago I moved all
Excel development to Powerpivot and I hardly write any formulas in "regular"
Excel anymore. PowerPivot provides more of a formula IDE (along with DAX
Studio), along with a ton of other functionality. I think it's clear that's
the direction sophisticated Excel development is going.

~~~
damian_r
I haven't seen that. I tried installing it on my Excel but it looks like kind
of pro version is needed for PowerPivot. I'll try to find some more
information on it. Thanks!

------
brudgers
Excel is already programmable via everything from Powershell to VB to F# via
its .NET bindings. The problem isn't that anything is missing from Excel, it's
that it's used by so many people who are not 10x software engineers that no
higher-level approach will ever work with 99,5% of the Excel out there.

The people who are organized enough and use Excel enough to find value in
saving formulas in a library are already extreme edge cases...and then you are
selling into that tiny market against the idea of using full blown tools like
Visual Studio and C#.

And it seems to me that really sound previously snakebit engineers aren't
going to go around changing working software that isn't backed up by a suite
of regression tests. The problem isn't Excel, it's bad code. The cause of the
bad code is bad programming practice, and the reason for that is that the
people programming are not programmers and even if they were they're under
just give me a kludge time pressure.

Spreadsheets are successful because they're really easy to use. Excel
dominates because making one that's userful for important work is really
really hard.

Good luck.

------
LarryMade2
I like the concept, when debugging some complex sheets that could come in
handy - though having all the formulas in a windows might hinder understanding
the layout of the sheet, especially if you are lazy and don't label all your
cells (or just know enough about excel to be dangerous).

I thought it would be cool if formulas would pop into little syntax
highlighted text edit areas when double clicked (or some other meta-click)

I think having a look at some more complex sheets would be good. (Ask for your
company's accounting department I'm sure they have some Excel monsters lurking
on desktops).

~~~
damian_r
I like the concept of simple editor for the formula.

Maybe an editor that would extract all formulas from the sheet and give you
different view after double click would be a good solution. On one hand you
would still have your normal spreadsheet but you can go to a different level
if you want to do refactoring etc.

