English Deutsch Français Italiano Español Português 繁體中文 Bahasa Indonesia Tiếng Việt ภาษาไทย
All categories

Is it possible to display numbers as positive and negative or as text? I am showing the movement of something in a chart. At the moment the formula in the column gives the answers as -3,-2,-1,0,1,2,3 etc, I would like to display as: down 3, down 2, down 1, same, up 1, up2, up 3. or if that is not possible then -1,-2,-3,0,+1,+2,+3
Please help!!!

2007-02-20 01:57:31 · 7 answers · asked by miss oxon 3 in Computers & Internet Software

7 answers

The only way I can think of this is to use another column for the results and put them in the format you require.

The formula you need is:

=IF(A1=0,"same",IF(A1<0,"down "&(0-A1),"up "& A1))

This puts "same" if it a 0, else if it is less than 0, it will be down ... else it will be up ...
The formula above works for the results being used in Column A, so change for a different column if necessary. Put this in the same row as the first cell thencopy the cell and paste for the rest of the column.

Put further question in Additional comments of you need more explanation.

2007-02-20 02:10:53 · answer #1 · answered by Marky 6 · 1 0

You can do it in two adjoining cells immediately to the right of the values that are -1, -2, 0, 5, etc. In the cell immediately to the right put a nested if statement like this =IF(A1<0,"down",IF(A1=0,"same",IF(A1>0,"up"))), format the cell right justified. To the right of that put in =IF(A1=0,"",ABS(A1)) which returns the absolute value or leaves the cell blank if the value is 0. Format that cell left justified and it looks like the way you want it . Now if you want to put the results in one cell for each value. You copy both columns and paste them as unformatted text in a word document. Then using find and replace, find all tab characters and replace them with spaces. Then recopy the results and paste back into your spreadsheet. You should have one column with text entries like
up 3
down 2
same
down 1

etc.

Careful! Yahoo Answers truncated my nested if statement in my preview.

2007-02-20 02:37:41 · answer #2 · answered by Turbidite 6 · 0 0

ought to objective a pivot table - set it up with "count type" as documents on the column and you're able to see a 2 against any duplicated entries. only facilitates if the duplicated numbers are precisely an identical format - beware for areas and so on. OR variety on the type col as others have stated, then Conditional format so as that any type an identical because of the fact the only above has a diverse color font. in the past you initiate, put in a momentary count type column so which you would be able to unsort lower back later.

2016-11-24 20:03:00 · answer #3 · answered by campbel 4 · 0 0

Not sure this is the right way but it seems to work

- Select the cell
- Right click and select Format Cells
- On the Number tab select 'Custom' in the Category field
- Enter this in the Type field: [$up] #;[$down] #; 0
- Press ok to see the result

2007-02-20 02:16:25 · answer #4 · answered by casper 2 · 2 0

Casper's answer is correct, except replace his 0 at the end, with [$same].

2007-02-20 05:33:29 · answer #5 · answered by Joliet Jake 3 · 0 0

1st:
=IF(formula_result<=0, IF(formula_result=0,"same","down " & ABS(formula_result)), "up " & ABS(formula_result))

2nd:
=IF(formula_result<=0, formula_result, "+" & ABS(formula_result))

2007-02-20 02:20:29 · answer #6 · answered by beauty_sandy_lv 2 · 0 0

fedest.com, questions and answers