7

Is there a methodology that would work well for replacing Excel with Mathematica?

Typically I use Excel to make various lists of values with formulae that relate those values. Below is a very typical example of a spreadsheet I might make showing radar range equations. I color input cells blue and computed cells are left black. It is very easy and simple to do:

enter image description here

Is there an equally easy approach to generating and organizing information like this in Mathematica?

UPDATE: I would like to re-open this question with the specific solution idea of using 2D Tables/Matrices. I did not realize this ability existed to create 2D input arrays using Mathematica.

Can I use 2D input arrays to simulate spreadsheet functionality?

Tyler Durden
  • 4,090
  • 14
  • 43
  • Something like TableView[]? – garej Mar 03 '16 at 20:53
  • @garej That just produces output. You cannot edit the cells of a TableView directly as far as I know. – Tyler Durden Mar 03 '16 at 20:54
  • I think you would need Mathematica's Dynamic functionality, from which you could hack up some reactive programming which makes Excel so popular. – Eric Brown Mar 03 '16 at 20:55
  • 7
    As the question specifically requests "equally easy approach", the answer is simply no. I would say, this is many orders of magnitude more complicated in mathematica. But I think that stems from the confusion of the purpose of each software. You probably would not like to use Mathematica instead of CorelDraw or Photoshop... – atapaka Mar 03 '16 at 20:58
  • 2
    Here is maybe a start? – Emy Mar 03 '16 at 22:14
  • I wouldn't advise replacing excel with mathematica for various reasons. Mathematica dosen't really have table support imo.I have added a bounty on the following question http://mathematica.stackexchange.com/questions/23864/mathematica-functions-from-vba I haven't gotten it to wok just yet – William Mar 03 '16 at 22:27
  • 2
    Mathematica Link for Excel is an excellent product for getting the best from both worlds. Some bucks involved, though. – Dr. belisarius Mar 03 '16 at 23:25
  • I successfully use Mathematica for similar engineering calculations. You should let go of the idea that it needs to be organized the same as in Excel. Hand calculations on paper are laid out similarly to the Notebook format, and served engineers very well for many decades. I find Notebooks easier to read, check and pass on to others. You can use Grid to summarize inputs and results, and the new template features can generate reports suitable for customers. – David Creech Mar 08 '16 at 23:38
  • @DavidCreech Yes, I have no attachment to the exact layout as Excel. I am more interested in making it so that there are distinct input areas and the output clearly shows values, units and so forth distinctly. What I want to avoid is a mass of code where I am hunting through text to put a value in the right place. Maybe a Manipulate frame with input boxes is the right paradigm. – Tyler Durden Mar 09 '16 at 05:12
  • I only use Manipulates in a very limited fashion for inputs. I don't like to have dynamic elements in a calculation because it increases the complexity, makes it harder for others to understand and check, and can cause problems saving the state of the inputs so the notebook can be reopened. When I do use them, usually for situations where I want to see live graphics or calculations to guide the inputs, I set them up to CellPrint an Input cell setting each of the input variables, e.g. P=Quantity[1000000,"Watts"]. – David Creech Mar 09 '16 at 13:49
  • I don't find any problems with finding the inputs in a mass of code. First off, you can put your functions in a package or in an initialization section of your notebook, with the cell closed to hide it until a checker needs to review it. Second, I use sections to group all the inputs together. Third, I use Placeholder to show the places that need to be filled in. For the previous example, you would type P=Quantity[Placeholder["peak power transmitted"],"Watts"] then select the placeholder code and use Evaluate in Place to replace it with the resulting placeholder box. – David Creech Mar 09 '16 at 13:55
  • The calculations and results output can be separated into other sections. Finally, I save this notebook with functions and placeholders and any explanatory text needed, and then open a copy for each calculation. For larger calculations I will use an output function to create a new notebook suitable for a customer submittal document. – David Creech Mar 09 '16 at 14:01
  • So to summarize, I keep the calculation notebook as straightforward as possible, laid out just like a typical hand calculation (problem description, assumptions, inputs, calculations, results), and create a pretty output report for the customer. – David Creech Mar 09 '16 at 14:05
  • If the question is about editing a 2D table in excel-like form, then this is probably a duplicate now. – bobthechemist Apr 10 '16 at 00:57
  • @leosenko Don't tell me how to use software – Michael Feb 21 '20 at 02:54

2 Answers2

6

To get empty sheet

tmp = TableView[]

Then enter some values and use Normal to derive the result as MMA list:

enter image description here

Result:

{{}, {Null, "Value1", "Value2"}, {"Normal", 3, 6}, {"Vector", 4, 7}}

Back to Table:

%//TableView

Of course, it is not recommended to emulate cell reference system of Excel in Mathematica.

garej
  • 4,865
  • 2
  • 19
  • 42
  • Do not need to do that. Just want to have a table-like input form with things lined up in columns and editable so users can enter input values without having to edit code. – Tyler Durden Apr 09 '16 at 20:11
  • I consider it a weakness of Mathematica that you can't do reactive programming well. – Michael Feb 21 '20 at 02:58
  • @Michael , what is reactive programming? – garej Mar 03 '20 at 12:36
  • The classic example would be a spreadsheet, where as soon as you update a value all of its dependencies are recalculated. Like Dynamic. – Michael Mar 03 '20 at 14:31
  • @Michael , but we may in principle mimic it via “Rule-based programming”. – garej Mar 03 '20 at 14:36
4
x = Table[0, {3}, {3}];
Grid[Table[ InputField[Dynamic[x[[#[[1]], #[[2]]]],
      Initialization -> (x[[Sequence @@ #]] := Null)]] &@{i, j},
  {i, 3}, {j, 3}]]
george2079
  • 38,913
  • 1
  • 43
  • 110