Any LibreOffice Calc knowledge?

[MDM]

There have been comments that the case study spreadsheet does not work as well in LibreOffice Calc (or Google Sheets, 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 x-axis variable on the chart (using Excel's "Data Table" column input).  Calc fills those with identical values. 

Anyone know how to correct that?

[MDM]

Bump.

[jim555]

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.

[MDM]

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, 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 x-variable 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 x-axis variable.  E.g., B11, or D31, or B3, etc.
V75 = cell in which one enters the desired y-axis 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.

[jim555]

You could add a custom function.  Alt-F11, add module to workbook, paste in:

Public Function fFormulaText(txtCell as Range)
       fFormulaText = txtCell.Formula
End Function

Use fFormulaText() like any other function.

[MDM]

You could add a custom function.  Alt-F11, 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/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496), and eliminate FORMULATEXT and INDIRECT.

Still don't understand differences between Excel's {TABLE} vs. Calc's MULTIPLE.OPERATIONS functions....