CODEWITHSUNDEEP
excelexcel-formula
Afel
Afel

Reputation: 65

Previous cell value before some formula changed it

Is it possible to use previous cell value before some fomula changed that value into something else?

For example:

A1=1.78541
A1=trunc(1.78541)

Is it still possible for me to use the previous value A1=1.78541?

I know that I can have 2 cells so I can use in this example both values, but that's not what I want. I want to have 1 cell and use both values: 1.78541 and trunc(1.78541).

Upvotes: 2

Views: 1640

Answers (3)

rayzinnz
rayzinnz

Reputation: 1948

One option is to use number formatting to display a value, but calculations are performed on the underlying value.
Another option, if that is not feasible, is to have two sheets, (e.g. named "Raw" and "Trunc"), in which you store raw data in the Raw sheet, and formulas in the Trunc sheet, e.g.

=Trunc(Raw!A1)


or VBA to write explicit values to the Trunc sheet.
e.g. on the Raw sheet event

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.Sheets("Trunc").Range(Target.Range.Address).Value = int(Target.value)
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

Is this doable on a limited scale. Yes. Is it recommended? No. I understand your dilemma (math, logic and canonical practises vs. bat-crap-crazy ideas) and have run into enough idiotic requests from people with brand new MBAs on their wall that I would like offer up a method that you could use to retain the underlying cell value's five place decimal precision while showing only a truncated (not rounded) value with three decimal places.

The reason this can only be accomplished on a limited scale is that you will need to create a custom number format for every different value. There are a limited number of placeholders for custom number formats and if the values change often, you will run of of available space to create new ones.

For purposes of demonstration, I'll write the VBA into a public sub that processes any cell or group of cells selected.

Sub Display_Not_Truncate()
    On Error GoTo Fìn
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim rng As Range
    With Selection
        For Each rng In Selection
            If IsNumeric(rng) Then
                rng.NumberFormat = _
                  Replace(Replace(Application.RoundDown(rng.Value2, 3), "0", "\0"), ".", "\.") 'have to escape reserved characters like zeros and decimal points as literals
            End If
        Next rng
    End With
Fìn:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Select any cell or cells to receive this treatment and run the macro. If the values change, their underlying value will change but not the displayed value unless you rerun the macro.

        Display not truncate

You can see in the formula bar that the underlying value for A2 (the one used for calculation) is 1.78541 but the displayed value is 1.785. The custom number format for that particular cell was 1\.785. The underlying values of the cells in column A are shown in column B. Columns C and D demonstrate the differences between using the true value with a truncated display and actually using the truncated value.

With regard to the limited number of custom number formats: this is one of those Limited by available memory things. I ran out of available formats at ~175 custom number formats while I was creating and testing this. The custom number formats are retained by the individual workbook and are not global across all Excel workbooks.

There is a VBA command for deleting custom number formats but you have to know the format before you can use it. Example:

ThisWorkbook.DeleteNumberFormat NumberFormat:="1\.785"

I know of no way to tell if any particular custom number format is actually being used or not beyond cycling through every cell in the workbook and examining what is being used in that cell. I suppose Find ► Choose format from cell could help.

So yes, it is doable; just not recommended. If this is for a client, make sure that they know (in writing!) that this is counter to accepted worksheet and accounting practises and that the implementation is not only limited in scope, but has serious problems with keeping changing values updated.

Upvotes: 1

dokaspar
dokaspar

Reputation: 8624

How about this:

A1=1.78541
A2=trunc(A1)

Now you can work with A2

Upvotes: 0

Related Questions