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]

You're welcome

 

[rogster001]

Like Colombo says "Just one more thing" !!

The final piece i need to fit in this number format puzzle is to make " Any minus figure that IS NOT -100 appear in RED " AND "Any positive figure is black"

I have this so far - but i cannot seem to stop the positive figures showing in RED also:

[=-100]" ";[=0] " ";[Red] 0.00

Any ideas?

Cheers

:

 

[Airfix9]

Use conditional formatting, that is your best way. If you're in Excel 2007, you can set multiple conditions.

 

[rogster001]

Use conditional formatting, that is your best way. If you're in Excel 2007, you can set multiple conditions.

Cheers, Its only 2003, so i was stuck at three conditions, but then i happily found that the conditional formatting could be combined with the custom format to 'invisiblise' the -100, and it's nailed...thanks for all the help!