I need to create a custom format code with the following characteristics:

Positive numbers: "£" sign, coloured blue, two decimal places (including trailing zeros), comma seperator, one leading zero.

What should my code look like? And what exactly is it getting at with the 'one leading zero' crap? I'm not quite sure what that bit means.

Should resemble something like this perhaps?

£#,##0.00;[Blue] etc.


Help!

One leading zero would display .1 as 0.1 . Its one zero displayed before the decimal point if the value is less than 1.


This should do it -

[Blue]£#,##0.00;[Red]-£#,##0.00

Displays as £5,190.00 in blue (positive) and -£3,324.00 in red (negative).

Not to sound like a dick, but Excel help has a huge amount of info on how to format these, but they can be bastards of things to get right.

___________________
ID THESE TRACKS PLEASE

IDing tracks is like having sex, the more you get the happier you'll be!!

Want to download a Digweed set? TA ID HUNT!

I found the help section too long winded, and for the purpose of my exams I don't need to get much more complicated than what I asked for above. Cheers.

Question actually...

If £#,##0.00 displays £5,190.00, then what happens if the number was 5,1990? Would it automatically know that there has been an extra number added and expand the code to 3 hashes after the comma?

I guess what I'm asking is do the 2 hashes after the comma represent just 2 numbers, or do they represent a string of x amount of numbers?

Cheers.

Oh yea, and another question.. How do I add zero values as "*" to my original code?

quote:

Originally posted by DJ Mikey Mike Question actually... If £#,##0.00 displays £5,190.00, then what happens if the number was 5,1990? Would it automatically know that there has been an extra number added and expand the code to 3 hashes after the comma? I guess what I'm asking is do the 2 hashes after the comma represent just 2 numbers, or do they represent a string of x amount of numbers? Cheers.

Probably (not verified though) it represents just 2 numbers. The comma is the separator between every three numbers in front of the decimal point. One is 0 (fixed), the other two are filled in depending on the number you entered.

This didn't make any sense, did it?

Also, with the comma being a separator between thousands, I don't know how Excel reacts to 5,1990. Trial and error, I suppose.

___________________

d&b session 20090519
My take on... (various mixes planned, updated when I can be arsed)

quote:

Originally posted by DJ Mikey Mike I found the help section too long winded, and for the purpose of my exams I don't need to get much more complicated than what I asked for above. Cheers.

Out of interest is this for the Office Specialist exams or something else??

I'll have another look at these once I get back home.

___________________
ID THESE TRACKS PLEASE

IDing tracks is like having sex, the more you get the happier you'll be!!

Want to download a Digweed set? TA ID HUNT!

Nice one. And it is mate, yes. I've already got my Word Expert, Power Point, Access and Outlook done and dusted, and on Monday I've got my Excel Expert exam to complete the package.

When I did my Excel expert there was a question on this stuff. I remember there was also one on XML, which was a bitch of a thing. Getting that Office Master cert fucking owns though

IIRC they say in the question "Create one of these godawful things that makes the numbers appear with commas, one leading zero, blue if negative, red if positive and that does other shit as well". Skip the question till the end and type a big number in a cell, format that cell (Format -> Cells -> Number -> Custom) and then selecting one of the options displays the result in the 'sample' column. So you can do trial and error till it works.

quote:

Originally posted by DJ Mikey Mike Question actually... If £#,##0.00 displays £5,190.00, then what happens if the number was 5,1990? Would it automatically know that there has been an extra number added and expand the code to 3 hashes after the comma? I guess what I'm asking is do the 2 hashes after the comma represent just 2 numbers, or do they represent a string of x amount of numbers? Cheers.

Just tried £#,##0.00, the 0.00 means that those digits are always displayed. eg: £5.99 is displayed, and not £05.99 or £0,005.99. The # I think says "if this figure is here, include it, if not, dont print a zero", wheras adding the 0 says "print a zero if there is no figure here".

Trying £#,##0.00 and entering 5,1990 gives £51,990. It dumps the comma thats entered and adds its own. But if I remember, I think they give you reasonably sensible numbers to work with.






And know Pivottables and Pivotcharts. Almost guaranteed question.

___________________
ID THESE TRACKS PLEASE

IDing tracks is like having sex, the more you get the happier you'll be!!

Want to download a Digweed set? TA ID HUNT!

quote:

Originally posted by DJ Mikey Mike Nice one. And it is mate, yes. I've already got my Word Expert, Power Point, Access and Outlook done and dusted, and on Monday I've got my Excel Expert exam to complete the package.

You've got those as college classes or something like that?

microsoft certification

Yea, as said above it's Microsoft Certification, and it's all taken care of by the company I work for thank fuck because it does get a bit pricey. For the other 4 office programs I just showed up and took the exam because I am very comfortable with them, but I think the norm is to enrol on a 2 day course before hand. And this is exactly what I did with Excel Expert because it's probably the most complex out of all the office applications, and it's the one I deal with the least in every day usage.

I'll come back to this thread on Sunday when I sit down and do a bit of revision before the big day. Got the MCSA course too look forward to next - Haven't decide what modules I'm going to take yet though. Anyone here done this yet?