TranceAddict Forums (www.tranceaddict.com/forums)
- Chill Out Room
-- Excel Geeks
Pages (2): [1] 2 »

Excel Geeks

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.

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.

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.

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.

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?

I use Excel only for basic stuff... Sum and average functions.

Score required to pass: 650
Maximum score: 1000

My score: 960


Feel free to tell me how amazing that I already know I am. Ironically, the only question I didn't get 100% on was the custom format code part.

quote:

Originally posted by DJ Mikey Mike Feel free to tell me how amazing that I already know I am.

You're an amazing geek, well done.

Actually, this Microsoft Certification deal seems like a pretty cool thing to have in the CV, does that give you a possibility of a bigger pay-check or what? I need to start knowing this stuff since I'll graduate this year on Computer Engineering (software).

It is indeed a good thing to have on the old CV. When I went on the course for Excel the instructor was telling me that a lot of companies will send their employees on Microsoft courses to get the knowledge, but won't put them forward for the exam for fear that it'll improve their credentials to the point where they can move on to better positions / jobs.

Worth doing I reckon.

top bombing mike.

After all that help you still fucked up the most important question

Congrats , getting that Master cert through the post fucking owns.

I hear all the birds in the UK go wild for a fellow with his MS certification.

Be sure to wear your medal/badge/diplomma/receipt with pride, Michael, and just be careful to wrap your bits & pieces thoroughly to help dodge the inevitable case of the clap or the syph that you're bound to run the risk of, what with all that sweet, sweet MS-Pussy you're bound to be knee deep in.

Indeed. This should result in some serious fanny coming my way.


Cheers fellas.

MIKEY!1

I need help with my mail merge.

Seems like it's already working.

quote:

Originally posted by MeLLyMeL MIKEY!1 I need help with my mail merge.

Okay. But it's gonna cost you.

quote:

Originally posted by DJ Mikey Mike Okay. But it's gonna cost you.

Seriously.


I'm to lazy to enter the dollar amount for every investor. So when I put the amount to be entered in the Mail Merge it shows up with out a comma.

It looks ghetto.

500000.

Where are you pulling this 'dollar amount' data from? An Excel spreadsheet? If so that's a piece of piss. Just highlight all cells with the 'dollar amount' values in them and go to Format -> Cells -> Number Tab -> Select 'currency' -> Select the most suitable pre-set (probably $1234.10)

Or have I got the wrong end of the stick?

Powered by: vBulletin
Copyright © 2000-2021, Jelsoft Enterprises Ltd.