• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

minus versus equals minus

C

Cognitor

New Member
Hi

I did not know what to use as a title, so I used what I tried to google....


This is my first Chandoo.org post (even though I have been a frequent vistor for a couple of years), and if I am not making sense let me know!


I have a sheet with some numbers in column R. If I reference these numbers by typing -R3, instead of the output being -6 (if 6 was the number in cell R3), my output is -3.

Using =-R3 gives the correct answer. This happens irrespective of whether the column is formatted as currency or numbers.


I am using excel 2003 at work, after loving 2010 at my previous employer


Could this be because I often use currency R (South African Rands)?.

It is not a big problem, and easy to circumvent, but I am just interested.
 
Cognitor


Firstly, Welcome to the Chandoo.org Forums


I'm not really sure what your asking, but


Always start formulas with an = sign


If you enter =-R3 you will get the negative value of cells R3

so If R3 has 6 you will get -6, If R3 has -4 you will get 4.


Don't use R as Rand in the values in a Cell

You can display values as R 123.4 using Custom Number Formats


Type Custom Number Formats into the Google Search Box at the Top of this page
 
Hi Hui


Thank you for your reply.

It does not really answer my query, but I was obviously not very clear.

Why is there a difference between =-R3 and -R3?
 
Cognitor

How are you entering formulas, when I type -R3 excel converts it to =-R3
 
I usually type it as =-R3.

Sometimes (when I am in a hurry, and only want to make a quick calculation without needing to setup a sheet for future use) I type -R3.

If I reference another column (eg -S3) I do not have the problem (excel changes the formula to =-S3), it is only (as far as I am aware, I have not tested it for all of the colums) when referencing column R
 
I can't replicate what your seeing


Excel does keep R and C as special characters when using RC Formula Formats, But I assume your not using that format


I would just start your formulas with an = or a + instead
 
Hi ,


I tried out what you posted , by changing my Regional settings to Afrikaans , upon which my currency symbol changed to R ( for Rand ).


Thereafter , if I enter -R3 , it becomes -3 ; this is displayed as -3 if the cell format is General , and is displayed as R -3 if the cell format is Currency.


Only if I enter =-R3 does Excel take the formula as a reference to cell R3.


I think the reason for this behaviour is that your currency symbol is R.


Narayan
 
Thank you, I will be more careful when using formulae without starting with =.

It is easy to circumvent, I was just interested in the possible reason.


I checked, I cannot recreate the problem when trying to duplicate in column C - so it cannot be that. Excel still auto corrects in column C
 
You must log in or register to reply here.
Back
Top