Forum

Notifications
Clear all

Design numbers in thousands of chess

6 Posts
2 Users
0 Reactions
80 Views
(@usb)
Posts: 244
Honorable Member
Topic starter
 

In the attachment I have a table of numbers
I need to present them in thousands of chess
Dog instead of 1310 - see 1
Instead of seeing 855 - they'll see 0 because it's a number below 1000
But I don't want to change the existing numbers because they are important to me for a different calculation,
I want to design cells that will see the number in short - 1 - in the design
And the number will be 1310 - only 1 will see 1310

Thank you!!
Leah

 
Posted : 24/02/2020 12:48 am
(@purfleet)
Posts: 412
Reputable Member
 

You want to see the number rounded down to the nearst 1000 and only show the thousands, but the real number is still in the cell?

Worth having a read of this Blog by Mynda https://www.myonlinetraininghub.com/excel-custom-cell-formats

I assume you want it like this? If so select the range and goto Number formats > More Number Formats > Custom and in the type box type #,

01/2019 01/2019
1 1,310.80
1 855.01
  421.60
2 2,122.92
1 716.72
11 11,025.34
16 16,452.39
3 2,713.50
7 6,950.89
15 15,100.80
5 4,633.04
12 12,234.35
7 7,005.48
49 48,638.06
65 65,090.45
 
Posted : 24/02/2020 2:43 am
(@usb)
Posts: 244
Honorable Member
Topic starter
 

Thanks for the quick reply and helpful answer!
Only now I have to show the numbers below 1000
As in the yellow cells in the attachment -
To be written 0
How to do it?

 
Posted : 24/02/2020 2:55 am
(@purfleet)
Posts: 412
Reputable Member
 

Sorry missed that bit

#0,

Purfleet

 
Posted : 24/02/2020 3:34 am
(@usb)
Posts: 244
Honorable Member
Topic starter
 

From France precision I need to have a number design

מספר .JPG

 
Posted : 24/02/2020 3:58 am
(@purfleet)
Posts: 412
Reputable Member
 

Looks like number formatting will only use normal rounding and you cant add in any INT or rounddown function.

As a work around you could create a linked sheet to have the real numbers on one sheet and a calc somthing like the below on another

=IF(טבלה2[@[01/2019]]<1000,טבלה2[@[01/2019]],ROUNDDOWN(טבלה2[@[01/2019]]/1000,0))

Also there is loads of information here from Mynda that might help

https://www.myonlinetraininghub.com/excel-custom-number-format-guide

Purfleet

 
Posted : 24/02/2020 3:55 pm
Share: