Formatting of numbers

Resolved · High Priority · Version 2019

Fri 1 Oct 2021, 15:44 Delegate Riz said...

Riz has attended:
Excel Advanced course

Formatting of numbers

Q1) a b c d e f
555,000 225,000 155,000 £555k £225.5k 155k

in excel def will not add shows as count only
why?

Q2) why is the sum in cell E5 not working, the figures need to be shown in format they are in

also i need more practice when doing =sum,

such as =-(-sum what is the purpose of these minuses in some formulas came across =-(+sum

or

=-sum(a2:c2)-sum(d2:f2)/1000

i need to understand what these - and + are doing and are dong and how and when to write them to get the write answers

because the currency is cells a2:c2 is different to E2:G2

Q3) in the course he did cover how to changes units but cant remeber how at work got sheets with 3 formats of figures need to know how to do them ie;

55,000 in sheet 1

55k in sheet 2

5.5 in sheet 3

all for the same figure 55,000 but shown differently

can you advise how i can achieve this i think it was format cell custom and selecting one of the options but need to know what # means by adding or taking it away and why , makes a big diff to how figure is stated

thanks for your help

Sun 3 Oct 2021, 20:59 Trainer Dennis said...

RE: formatting of numbers

Hi Riz

1) You can't add up text and numbers e.g. £50k = text, you need to put the correct value of £50000

2) I don't have a copy of your spreadsheet so cant answer why E5 is not correct.

2b) The - (minus) in front of a value or cell reference will either convert a positive number to a negative value or a negative value to a positive. This is the rules of mathematics not Excel.

3) Custom number formats allow you to display your number how you wish. #,##0.00 would display 1,000.00 the # represents any/optional number so the example would also work with 999,999,999.99. A 0 (zero) means you want that number to always be display so 0000 would show 1 as 0001

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Create your own custom list on Excel 2010!

If you know how to use the auto-fill option on Excel then why not create your own customs lists?

The auto fill feature saves you time by allowing you to enter one of the list entries into a cell and then use your mouse to automatically drag the rest of the list into the cells below, above or to either side of the initial cell. When using your mouse to perform this task you will see a thin black cross appear at the bottom right hand side of the cell. Click, hold and drag to make the list appear.

Default lists include weekdays and months. To create your own list in Excel 2010 do the following;

>File
>Options
>Advanced
>Scroll right to the bottom of the page and you will see a buttom "edit custom lists", click this button
>enter your list in the list entries
>click add

Now try it out. Good luck.
>

View all Excel hints and tips