How to exclude striked through values in a calculation?

[rene_spies]

I am calculating some budgets. For this, I am writing down everything that is wanted and then decide if it makes sense to buy it or not. When it does not make sense, I do not delete the value but strike it through. Of course, striking through values does not affect the calculated budget after the purchases but this is what I want.

How may I solve this problem?

[igorlius]

Hello,

i build a macro based solution that does what you want.
Just put it into the Tools → Macros → “Edit Macros” and you can call it from inside any cell via

=SUMNOSTRIKE("A1:A5") 

• The first argument is the range quoted as a string.
  You can still select the range normally but you have to add quotes ("") before submitting the formula

Here the Module:

Sub testSUMNOSTRIKE
    Print SUMNOSTRIKE("A1:A3") 
End Sub

Function SUMNOSTRIKE(range As String)

	range = Trim(range)

	Dim sum As Double
	Dim i As Integer
	Dim j As Integer
	
	Set oSheet = ThisComponent.CurrentController.ActiveSheet
	Set oRange = oSheet.getCellRangeByName(range)

	sum = 0.0
	
    For i = 0 To oRange.Rows.getCount() - 1             
        For j = 0 To oRange.Columns.getCount() - 1
            Set oCell = oRange.getCellByPosition( j, i )
	            if (oCell.CharStrikeout = 0) then
	            	sum = sum + oCell.Value
	            endif
        Next j
    Next i 
    
	SUMNOSTRIKE = sum
	
End Function

Hope this helps and have a nice day.

[mikekaganski]

In Calc, cell/text formatting is not considered data, just a decoration. Yes, strike-through is not taken into account when calculating. So a user of a spreadsheet should prepare the data with that in mind (know your tool).

An advise would be to populate the data in a way that gives something that Calc may proceed. E.g., you might had created a dedicated column with - put for the entries that you decided not to purchase, so you wouldn’t use strike-through for those, just put - in that adjacent column next to the item. You might also arrange a conditional formatting on your entries column, so that when adjacent cell has -, the style of the main cell becomes stroke-through (just for viewing convenience). The conditional formatting formula could use those - as data, just as your budget formulas.

But of course, that requires planning the data layout with program capabilities taken into account.