Building an IF statement??

[rogster001]

Hi all,

I have a range of cells that at present are populated with a transpose array, that simple pulls data from another worksheet in the same file, so i have the formula:

{=TRANSPOSE(NEall!C30:BB30)}

This works fine.

However we would like to have the values that are ' 0 ' show as a small text string " n/a " for example.

Can we combine the transpose() into a larger function description that would do something like the following pseudoCode:

{ TRANSPOSE(NEall!30:BB30)
if(cellVal == 0)
{
     print "n\a";
}
}

I have little idea how to build up formula descriptions in excel, apart from very simple combinations of things, but i am a fluent C++ coder so hopefully will be able to grasp any replies!

Many thanks for any info.

 

[Airfix9]

Why not set the number format like this:

0;-0;"n/a"

That would do it.

 

[rogster001]

Ok thanks, i see what you mean..

I wonder if you could advise on the syntax i looked for in any case though? I just tried this with some sample data and it worked fine

IF(G24:G45,1, "n\a")

But I don't know how to combine it in a statement with this:

{=TRANSPOSE(Sheet1!N22:AI22) }CODE]
 
i tried this first off:
 
[CODE]{=TRANSPOSE(Sheet1!N22:AI22) IF(G24:G45,1, "n\a") }

But that syntax was wrong, i can continue fiddling here but wondereed if anybody could advise on the general rule of syntax for combining terms in a formula?

Cheers!

 

[Airfix9]

{=IF(TRANSPOSE(Sheet3!$A$1:$E$7)=0,"N/A",TRANSPOSE(Sheet3!$A$1:$E$7))}

The problem with doing it that way is that if the response is zero, a text string is entered instead of a number which can lead to problems later with some functions which need to sum data, if you use them. Using my method, the value stored in the cell remains 0, it just looks like a text string "n/a".

 

[rogster001]

the number format is a nice fix thanks, and i see what you mean, you just want the cells 'represented' differently,

i used:

0.00; - 0.00; "n\a"
as i require 2 decimal places, was very pleased to see that work haah!

Is any kind of call to something like TEXTCOLOR I could use? To have the "n\a" appear as light grey for example?

0.00; - 0.00; "n\a" (TEXTCOLOR 25)

Anything like that exist?

I tried this but whilst it did not complain about the syntax, it did not change text color

0.00; - 0.00; "n\a";[Red]

EDIT!

I got it!

0.00; - 0.00;[Red] "n\a"

Now i just need to know the colour codes or stringvalues for 'lightGrey' !

 

[Airfix9]

Use conditional formatting. If you are in Excel 2007, it's on the Home ribbon, under styles. Just format 0 how you want it to appear as, even though it shows "n/a" it is still a 0 to the computer, as I have already shown.

 

[Airfix9]

The problem with your number format is that you put the colour indication after another semi-colon. The basic rules for number formatting are as follows:

Postitive numbers ; Negative numbers ; Zero ; Text strings

So, to make the N/A red, you should have used:

0.00;-0.00;[red]"N/A"

The list of colours that can be supported in this way is, however, very limited. If you want more control, conditional formatting is your baby.

 

[rogster001]

Grat thanks, i have that fixed up now, but i need to apply the same thing for

if a number is exactly -100
my problem is the above and variants i have tried do not seem to work, my column contains figures that are other minus numbers, they too get changed or messaed about with by the ways i have tried so far,
so what would be the command required to isolate a single minus value in this way?

thanks a lot

 

[Airfix9]

How many numbers do you need to isolate?

This would work for the question, returning the text string response:

{=IF(TRANSPOSE(Sheet3!$A$1:$E$7)=-100,"Too Small",IF(TRANSPOSE(Sheet3!$A$1:$E$7)=0,"N/A",TRANSPOSE(Sheet3!$A$1:$E$7)))}

This would work for the number formatting response:

[=-100]"Too Small";[=0]"N/A";0.00

 

[rogster001]

Thats brilliant mate, thanks for the help..it is any -100 value found in the given column by the way, so i am going with the number formatting option, cheers!