Text and numeric value in one table

[#1]

Hi everyone,

I'm trying to format a straight table with numbers and text in it. Unfortunately, it doesn't show the text only " - ", the numbers work fine though.

This is my code:

=IF([Customer.Year Value 2011]>1,Num([Customer.Year Value 2011],'#.###,#'),

If([Customer.Year Value 2011]<1,Num([Customer.Year Value 2011],'###,#%'),

text([Customer.Year Value 2011])))                      --> this is not working

Many thanks!!

[kamalqlik]

Hi cdettmer,

                    Can you clarify more about the problem.Or you can show me the script.

regards

Kamal

[#3]

Hi Kamal,

the problem is that in the table there are severyl different data types

percentage numbers, integer numbers and text

I want to define all three types in an if condition.

So this:

" =IF([Customer.Year Value 2011]>1,Num([Customer.Year Value 2011],'#.###,#'),

  If([Customer.Year Value 2011]<1,Num([Customer.Year Value 2011],'###,#%'),      "

defines the numeric values.

But unfortunately now the text isn't recognized as text anymore. instead of NA what is supposed to stand there, only the sign " - " is shown.

Sorry but I can't show you the script

[kamalqlik]

Can you give a liyyle bit example of each datatype.

ie Number,text....so that I can identify your problem

[m_woolf]

Am I reading your expression correctly?:

=IF([Customer.Year Value 2011]>1,'what to do if >1',

If([Customer.Year Value 2011]<1,'what to do if < 1','

'what remains is what to do if =1'))  

[#6]

=IF([Customer.Year Value 2011]>1,'what to do if >1',

If([Customer.Year Value 2011]<1,'what to do if < 1','

yes this is right but as the third expression what I want to express is:

'what remains is text'))

e.g. my table looks loke this

a     9%

b     2,1

c     NA

a and b works but c is displayed as follows:

c     -

Thanks for your help!!!

[m_woolf]

It is not c that is displayed as "-", it is Customer.Year Value 2011 = 1.

There are no values =1. the - is the null symbol.

You need to rethink your IF statment.

[#8]

Ok I tried this

=IF([Customer.Year Value 20111]>1,Num([Customer.Year Value 20111],'#.###,#'),If([Customer.Year Value 20111]<=1,Num([Customer.Year Value 20111],'###,#%'),

if(istext([Customer.Year Value 2011]),text([Customer.Year Value 2011]))))

But it's not working either

[#9 · Accepted solution]

Ok I got it, I just changed the presentation of the Null value and set Null Value = NA

Thanks for the explanation of the if condition!!