Hacker News Reader

Comment by d--b | Hacker News Reader

> One reason is because we think that other, more inexperienced, programmers might have an easier time with visual programming. If only code wasn't as scary! If only it was visual! Excel Formula is the most popular programming language by a few orders of magnitude and it can look like this:

> =INDEX(A1:A4,SMALL(IF(Active[A1:A4]=E$1,ROW(A1:A4)-1),ROW(1:1)),2)

Ahem. Excel is one of the most visual programming environment out there. Everything is laid out on giant 2d grids you can zoom in and out. You can paint arrows that give you the whole dependency tree. You can select, copy, paste, delete code with the mouse only. You can color things to help you categorize which cell does what. You can create user inputs, charts and pivot grids with clicks.

View on HN

As a programmer who had used Excel for years, seeing my accountant start typing a formula, change sheets, select some cells, go back, repeat, was a learning process. I didn't even know you could do that, and also, I hated it. But it worked very well for him.

I've more recently been exposed to a few spreadsheets that are used to calculate quotes in major insurance businesses when I was asked to create an online process instead, replicating the questions and formula.

They're things of horrifying eldritch beauty. I seem to always find at least one error, and no one I'm allowed to talk to ever really knows how they work since they're built up over years. Those dependency arrows are a life saver.

> I seem to always find at least one error

Every time I see so spreadsheet where the dependency is hard to track, I've found enough errors that the results were completely bogus.

Also every time, nobody cared.

In my case at least, they probably accounted for it somewhere by adjusting rates elsewhere so it works out. So it's a bit risky to just change, similar to code that is known to be wrong but too hard to change since "everything works".

Oh sweet summer child. You will probably never experience running into the 10 million cell limit on a google sheet with more than a hundred sheets and waiting a quarter of an hour for your spreadsheet to update.

I have had to debug insane Excel sheets, which were used to generate C code, based on the geometric properties of an object.

Excel works very well for describing many, simple relationships. It totally falls apart the moment you have complex relationships, as they become mentally untraceable. Functions allow you to abstract away functionality, referencing cells does not.

I am pretty certain that Excel is one of the most misused tools and suffers the most from "I use it because I know it".

I had thought I had seen everything but kudos - Excel to generate C code is some sort of local extremum for software hacks.

Excel could do this so much better though (and I think excel is the best candidate for visual scripting overhaul). The cell could have two parts; top parts is the function signature (other cells could reference by signature, or by cell number), bottom part is the code. Each cell is a function.

People put huge unreadable basic functions in that tiny box. It's such an obvious pain point, surprised it's never been addressed. Replace vba with c#, have a visual line linking cells to other cell references, bam million dollar product.

A basic problem I have, looking at an Excel spreadsheet, is I don't know which cells are calculated by a formula, which are constants.

Maybe it would be easier if the spreadsheet was divided into an upper part with only constant-cells and a lower part with only calculated values, would that help me?

> A basic problem I have, looking at an Excel spreadsheet, is I don't know which cells are calculated by a formula, which are constants.

Use Ctrl-` (show formulas).

Thanks for the tip I will use it next time I open an Excel spreadsheet.

I'm also thinking in terms of perhaps having a different visual style for cells with formulas, when the spreadsheet is presented on paper etc.

Most people don't seem to realise that you can put line breaks and leading spaces for indentation into formulae. Also while by default the edit line at the top of the window only shows one line, you can drag the divider down to show multiple lines.

> You can paint arrows that give you the whole dependency tree.

Sorry, is that a manual process, or is there a option in Excel to show multi-ancestor dependencies?

I'm aware that you can double click to see a single cells inputs, but I want to go deeper.

https://support.microsoft.com/en-us/office/display-the-relat...

Stil impossible to know what a Excel sheet does only by looking at it. The 2d grid obfucates the relationships between data.

Power BI does (almost) everything Excel does but better.

You are right. You can see the data first, charts, even dependencies. And yet nobody is drawing `IF(ACTIVE[A1:A4]=E$1)`

Reply on news.ycombinator.com