See formula in Formula Bar After entering

[gambils]

I want to be able to see the formula I have entered in the formula bar after I've entered it. In some of my worksheets I can- I can look at the cell and see 3 and look in the formula bar and see =6/2. But in some of my worksheets all I see is the result of the calculation. So, for instance, if I put in +(10/12) *8 I see .83333 *8.

So I'm not sure what option I have ticked where it works in some of my worksheets but not others

 

[Yongle]

are the affected worksheets protected?

 

[gambils]

Hidden is not checked

 

[joeu2004]

The problem ("partial evaluation") arises when the cell format is Currency or Accounting (or another Custom format that includes the currency symbol?) and the formula is entered with a leading plus ("+") instead of equal ("="). IMHO, this is a defect.

The obvious "work-around" is to enter formulas in the normal manner, typing "=" first instead of "+". Why not?!

Some painful alternatives:

1. Temporarily change the cell format.

2. Add redundant parentheses. For example, +(10)/(12)*(8) or (less ambiguous) +(8)*(10)/(12). You might experiment to find the minimum redundant parentheses.

3. Set the option File > Options > Advanced > Lotus Compatibility Settings > Transition Formula Entry.

I especially deprecate the latter, because there might unintended consequences. I do not know all the side-effects of setting that option.

I mention it, primarily, to highlight that __setting__ a Lotus Transition option disables the "feature". I suspect the __intent__ was to enable "partial evaluation" by setting this option. But a programmer inadvertently wrote the opposite in the implementation.

 

[Yongle]

The problem ("partial evaluation") arises when the cell format is Currency or Accounting (or another Custom format that includes the currency symbol?) and the formula is entered with a leading plus ("+") instead of equal ("="). IMHO, this is a defect.

@joeu2004
- does this only apply to older versions of Excel?
- I tried to replicate with Excel365 and the "=" is automatically added by Excel
- the formula remains visible

 

[gambils]

Will give the = a try. And thanks for giving me a better phrase to describe the situation- "partial evaluation."- I can see a lot of contexts I can use that in!

 

[Yongle]

I don't think the standard search and replace can be made specific enough to only replace + when it is the first character

Here is some VBA that you could test on a copy of one of your sheets
- it should correct every incidence in the sheet, but I cannot test it because my version of Excel behaves differently

Unprotect the sheet before running the code

Sub ReplacePlus()
    Dim fRng As Range, cel As Range, f$, a$, b$
    Set fRng = Cells.SpecialCells(xlCellTypeFormulas)
    For Each cel In fRng
        f = cel.Formula
        a = Left(f, 1)
        b = Right(f, Len(f) - 1)
        If a = "+" Then cel.Formula = "=" & b
    Next
End Sub

 

[gambils]

Using Excel 365 and the simple = seems to be working. That said, I'll hang onto this code in case I need to use it. Thanks!

 

[joeu2004]

The problem ("partial evaluation") arises when the cell format is Currency or Accounting (or another Custom format that includes the currency symbol?) and the formula is entered with a leading plus ("+") instead of equal ("=").

- does this only apply to older versions of Excel?
- I tried to replicate with Excel365 and the "=" is automatically added by Excel

I cannot say with impunity.

gambils indicates (later) that he uses Excel 365. So we might infer that the problem does indeed exist in some revisions of Excel 365. Perhaps you have a more-recent revision of Excel 365.

OTOH, details matter. Remember that the cell must be formatted as Currency or Accounting, for example, before entering the formula.

If we enter +8/12*6, yes, the "+" is replaced with (just) "=". But also 8/12 has bee replaced with 0.666666666666667 in the Formula Bar. Do you still see 8/12?

OTOH, if we enter +(8/12)*6, the "+" is replace with "=+". Go figure! (A defect is a defect. There is no rational explanation for its varying behaviors.)

That said, I cannot duplicate the problem using Excel Online. However, I am not convinced that Excel Online is exactly the same as any bona version of the Excel product, including Excel 365.

-----

I don't think the standard search and replace can be made specific enough to only replace + when it is the first character

Well, we could use Find/Replace to replace "=+" to just "=" in the latter example above.

But there is no point. It is too late. Again, the problem arises when we enter the formula, not afterwards.