The Money Mustache Community
Learning, Sharing, and Teaching => Ask a Mustachian => Topic started by: MDM on January 16, 2018, 12:30:24 PM

There have been comments that the case study spreadsheet (http://forum.mrmoneymustache.com/foruminformationfaqs/casestudyspreadsheetupdates/) does not work as well in LibreOffice Calc (or Google Sheets (https://forum.mrmoneymustache.com/askamustachian/anygooglesheetsknowledge/), but that is another topic) as it does in Excel.
It appears some differences are easily fixable, such as Excel treating a blank cell as a zero for VLOOKUP purposes, but Calc needing to see an actual zero (e.g., cell G6).
A significant issue, however, is the marginal rate chart near cell F81. Works great in Excel, but not in Calc. Perhaps the most significant difference is between Excel's {TABLE} vs. Calc's MULTIPLE.OPERATIONS functions. Some of that is cosmetic, e.g., getting cells U84 and O82 to work correctly, and probably manageable.
Where a more knowledgeable set of eyes is most needed is cells Q83:Q583. Excel fills those with the tax results corresponding to the different values of whatever one has chosen for the xaxis variable on the chart (using Excel's "Data Table" column input). Calc fills those with identical values.
Anyone know how to correct that?

Bump.

I took a quick look at the spreadsheet. The marginal rate chart near cell F81 works in 2013 Excel, but 2010 and 2016 Excel both have problems with it.
Will investigate further.

It does work in Excel 2016, because that's what I have, so...?
There is a recent Bogleheads thread, Social Security “Hump” and the new tax rates???  Bogleheads.org (https://www.bogleheads.org/forum/viewtopic.php?f=2&t=237823), in which someone noted a problem with Excel 2010.
I believe the Excel 2010 issue has to do with the FORMULATEXT function and how problems with that affect cell O82 and thus the graph scaling. One can get around that, and make updating the graph xvariable faster, using a macro attached to a button. That has the downside of causing Excel to warn people the file "contains macros." So it goes  no perfect solution (that I've found).
Because the macro will (I believe) work in Excel 2010 through 2016, while FORMULATEXT does not work in 2010, I'm planning to put the macro into the next release. Work in progress below, with
DTI = named range for cells P82:R583
U75 = cell in which one enters the desired xaxis variable. E.g., B11, or D31, or B3, etc.
V75 = cell in which one enters the desired yaxis variable. E.g., D61, or D65, etc.
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+n
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
Range("U75").Select
X = ActiveCell.Value
Application.Goto Reference:="DTI"
Selection.Table ColumnInput:=Range(X)
Range("U84").Value = X
'
Range("V75").Select
Y = ActiveCell.Value
Range("U83").Value = Y
'
Range("Q82").Formula = "=" & Y
'
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'
End Sub
I make no pretense regarding Excel macro expertise, so any "obvious" improvements gladly accepted.

You could add a custom function. AltF11, add module to workbook, paste in:
Public Function fFormulaText(txtCell as Range)
fFormulaText = txtCell.Formula
End Function
Use fFormulaText() like any other function.

You could add a custom function. AltF11, add module to workbook, paste in:
Public Function fFormulaText(txtCell as Range)
fFormulaText = txtCell.Formula
End Function
Use fFormulaText() like any other function.
Thanks for that idea!
Did decide to try the macro route (see https://forum.mrmoneymustache.com/foruminformationfaqs/casestudyspreadsheetupdates/msg1879496/#msg1879496), and eliminate FORMULATEXT and INDIRECT.
Still don't understand differences between Excel's {TABLE} vs. Calc's MULTIPLE.OPERATIONS functions....