Excel numeric picture

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Trying to understand how to format my output so it puts a plus sign in front of positive numbers. But even when I copy and paste the example from Microsoft's website, Excel tells me it is an invalid formula.
Adds a plus sign to a positive result, a minus sign to a negative result, or a space if the result is 0 (zero). For example, { = 100 - 90 \# +## } displays "+10", and { = 90 - 100 \# +## } displays "-10".
When I try those examples in Excel it gives me errors. Am I not suppose to use the Curly Braces?

Basically what I am trying to do is take the number in column B, subtract it from 200 and put a plus sign in front of it, if it is a positive number.
How do I change this formula so that the output will put a plus sign in front of the output.
=(B2-200)

Basically what I really want the output to do as well is print EVEN if the result is equal to zero, but I could live without that feature. So I would really like to have the output like this.
-10
EVEN
+10
 
Joined
Jul 25, 2004
Messages
5,458
Hi there,

No curly brackets. What you are looking for is a custom format. I'm sure you know this, but even with a custom format (any format for that fact) is just basically a mask of sorts, and does not truly show what the underlying value is.

So if we give you EVEN as a 0 value, then the value of the cell is still 0, even though it will show EVEN.

That in mind, use the custom format:

"+"0;"-"0;"EVEN";General

Custom format goes with 4 specific parameters, seperated by a semi-colon. The first is greater than zero values, then less than zero values, then equal to zero values, finally textual values.

If you use any braces, make them square brackets, not curly. I think the curlies are for Word fields, not really sure on that one. Wish that ******* child Word could be more like Excel. hehe (j/k)
HTH
 

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Ok I went into format cells and made a custom format and that works great but I kind of thought I had to put it in the cell formula itself, like the example above, that I got from MS site.. This spreadsheet is going to be moved around to different computers. I would assume that custom format will only be available on the computer I created it on. Is it possible to put it in the cell formula itself. I would assume so from looking at the example above.
 
Joined
Jul 25, 2004
Messages
5,458
Oh yeah, it is. Sorry, I misunderstood.

=IF(A1>0,"+"&A1,IF(A1<0,A1,"EVEN"))

Assuming your number is in A1. The distinct difference with this is that the result is NOT a number. That is the benefit of using a format. Formats transfer per workbook, but custom formats don't per se transfer at all. But they do stay with the spreadsheet they were created in. In fact, they will be available anywhere in that workbook if you go to your custom format list.

Is this what you're talking about?
 

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
yes, you are correct. I moved the spreadsheet to another computer and the custom format was still in the spreadsheet. This will work fine. I am not even going to bother with trying to put in the formula.

The actual formula I am using is just
=(B2-200)

And according to MS site I can make the result of that have a + or - sign if I use there example
Adds a plus sign to a positive result, a minus sign to a negative result, or a space if the result is 0 (zero). For example, { = 100 - 90 \# +## } displays "+10", and { = 90 - 100 \# +## } displays "-10".
so I would think I could just do this in my formula but it doesn work.
=(B2-200\# +##)
 
Joined
Jun 24, 2004
Messages
114
firefytr said:
Wish that ******* child Word could be more like Excel.
You mean be limited to calculation and unable to handle text very well? :)

Squashman.

If you want to use formatting in a cell as an alternative to Zack's solution you can use the TEXT function ...

=TEXT(A1,"+0;-0;""EVEN"";@")
 

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
TonyJollans said:
You mean be limited to calculation and unable to handle text very well? :)

Squashman.

If you want to use formatting in a cell as an alternative to Zack's solution you can use the TEXT function ...

=TEXT(A1,"+0;-0;""EVEN"";@")
That was it.
Thanks to both of you. Will probably need some more help with this spreadsheet over the next couple of days. It is for my brother who runs a monthly bowling tournament. He knows next to nothing about computers, so I gotta add lots of bells and whistles to this spreadsheet so that all he has to do is click buttons in it to make the calculations or sort a defined column.
 
Joined
Jul 25, 2004
Messages
5,458
I was about to post about the TEXT function and Tony beat me to it. (No hard feelings on the Word app, eh Tony? ;) )
 

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Gotta add to that formula. This is what I am using.
=TEXT(C2-200,"+0;-0;""EVEN"";@")

If there is no value in C2, I don't want the formula to calculate. I am sure this is a simple IF Statement.
 

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
firefytr said:
Yup..

=IF(C2="","",TEXT(C2-200,"+0;-0;""EVEN"";@"))
Ok guys. I got a sorting issue because of using EVEN for Zero. I don't think there is anyway around it but to just print a zero there in order to get the column to sort correctly. I would assume this is because it is doing an alpha-numeric sort and sorting the numbers first and then the letters.

If I change the formula to
=IF(C2="","",TEXT(C2-200,"+0;-0;0;@"))

The column will sort correctly because it will just print a zero in the column. But I was hoping to use the formula to print EVEN when the value of the cell is zero and still have the column sort correctly.

+34
+12
EVEN
-10
-13

Do I have any options?
 

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Ugh, this isn't going to work at all. I just noticed this is what happends when I sort ascending in that column. I need it in numerical order but I guess I can't do that because of the plus sign. Guess I can't sort by this column.

+1
+10
+11
+12
+13
+2
+20
+21
+22
+3
+30
+4
+40
+45
+5
+50
+6
+7
+8
+9
0
0
-12
 

Squashman

Thread Starter
Retired Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Well, I think I got this figured out. I changed the formula to:
=IF(C2="","",(C2-200))

Then I made a custom cell format and applied it to those cells.
"+"0;"-"0;"EVEN";General

Now everything sorts correctly.
+50
+45
+40
+30
+22
+21
+20
+13
+12
+11
+10
+9
+8
+7
+6
+5
+4
+3
+2
+1
EVEN
EVEN
-12
 
Joined
Jul 25, 2004
Messages
5,458
Sorry I saw these replies a little late. Glad you got it working though!!

Remember, it sorts because the underlying value is (numerically) recognized. It wasn't sorting correct (as you desired) because it was alpha-numeric, as you said.

:)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top