Custom formatting that uses K,M,B

[Ang24]

Hi
Further to my previous question I was wondering if there was a way to add this [>=1000]#,###; to this custom format [<999950]0,"k";[<999950000]0,,"m";0.0,,,"b" so if the number is below 1000 it displays as is?

Because I notice that the chart will only respond to the custom formatting not the conditional formatting.

Thanks in advance

 

[myall_blues]

I looked at your other question and I don't believe there is a way to get number ranges into a custom format (although I may stand corrected). This is because custom formats only allow for four components - positive; negative; zero; text but not ranges within those components.

The only way I can think to do what you want is with a small piece of VBA code. Once you have your numbers, select the range and run this macro to format them. You'll probably need to play around with the formats to get them how you want as I just took a guess, noting that the one provided above ([<999950]0,"k";[<999950000]0,,"m";0.0,,,"b") didn't work.

Sub MyFormat()
    Dim nc As Range, fstring As String
    For Each nc In Selection
        If Application.WorksheetFunction.IsNumber(nc.Value2) Then
            If nc.Value2 < 1000 Then
                'do nothing
            ElseIf nc.Value2 >= 1000 And nc.Value2 < 10000 Then
                nc.NumberFormat = "#,##0.0, k"
            Else
                nc.NumberFormat = "#,##0.00,, " & """m"""
            End If
        End If
    Next nc
End Sub

This produces the following results

Book1
	A	B
1	Before running macro	After running macro
2	900	900
3	9500	9.5 k
4	15000	0.02 m
Sheet1

 

[Ang24]

Thank you. I will take a look at that tomorrow and let you know how I go.

 

[Ang24]

Thank you so far that seemed to work. As I'm not familiar with VBA would you be able to add the following conditions please

Numbers from	Numbers to	Examples	Decimal places
0	1,000	999	do nothing
1,000	10,000	7.2k	one decimal place
10,000	1,000,000	203k
1,000,000	1,000,000,000	532m
1,000,000,000		3.6b	one decimal place

 

[myall_blues]

Try this updated version.

Book1
	A	B
1	Before running macro	After running macro
2	999	999
3	7,200	7.2k
4	203,000	203k
5	532,000,000	532m
6	3,600,000,000	3.6b
Sheet1

Sub MyFormat()
    Dim nc As Range, fstring As String
    For Each nc In Selection
        If Application.WorksheetFunction.IsNumber(nc.Value2) Then
            If nc.Value2 < 1000 Then
                'do nothing
            ElseIf nc.Value2 >= 1000 And nc.Value2 < 10000 Then
                nc.NumberFormat = "#,##0.0,k"
            ElseIf nc.Value2 >= 10000 And nc.Value2 < 1000000 Then
                nc.NumberFormat = "#,##0,k"
            ElseIf nc.Value2 >= 1000000 And nc.Value2 < 1000000000 Then
                nc.NumberFormat = "#,##0,," & """m"""
            ElseIf nc.Value2 >= 1000000000 Then
                nc.NumberFormat = "#,##0.0,,," & """b"""
            Else
                nc.Value2 = CVErr(xlErrValue)
            End If
        End If
    Next nc
End Sub

 

[Ang24]

Thank you Murray that is wonderful. I really appreciate your help as I am new to this part of excel.

When I ran the macro it worked well but now I realise that I need the macro to automatically update the table every time I make a new selection from the drop down boxes.
The drop down boxes and the graphs are located on a dashboard which is on a different page to the table.
Ever time I select an item from the drop down box it changes the data in the table, so I need the macro to automatically update so the formatting in the graph is correct.
Is this possible? The dashboard is for a housing project that I have created over the last few years. It has the potential to make a real difference to the lives of many.

Thank you in advance. I hope you can help.

 

[myall_blues]

It's certainly possible but to do it I need to know a bit more about your table. Not so much the dashboard but the data that changes that needs formatting.
Specifically need to know:

• What column does the data appear in?
• What sheet is it on?
• Does the number of rows change when you select an item from the drop down such that the data changes?
• Are the numbers coming from formulas?

Ideally if you could post a sample of your data page (anonymised if necessary) using XL2BB, but if not a picture will do. If it has to be a picture remember to include the row and column numbers.

 

[Ang24]

Thank you Murray. I will organise first thing tomorrow morning. Really appreciate your help. Cheers

 

[Ang24]

Hi Murray,

Here are two images of the spreadsheet. The first shows the lowest values and the second shows the highest values. The graphs needs to be able to be able to move easily between the two. Thanks again and let me know if you need anything else. Cheers

 

[Ang24]

To answer your questions above no the number of rows don't change, and yes the numbers come from formulas that draw the data from multiple tables depending on what is selected from the drop down boxes. The sheet name is called - DB Data - CHP Thanks again