# Custom Number Format issue

• I have an excel to calculate the product price and difference in amount

I am using the number format as [>=10000000]" "##\,##\,##\,##0;[>=100000]" " ##\,##\,##0;" "##,##0

1) if the difference in amount is positive, the result value is satisfying the above number format

2) if the difference is negative, its not showing as above number format.

Pl. help in showing the result as expected.

Attachment: Attached is the Excel.

Thanks

KSK

## Files

• Hi,

Have you tested the custom format : [>=10000000]" "##,##,##,##0;[<0]" "\ ##,##,##0;" "##'##0

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• ## CarimApr 30th 2023

Changed the title of the thread from “Excel Number Format issue” to “Custom Number Format issue”.
• Hi Carim & Ingo - Thanks for your reply.

Carim

Applied your formula. [>=10000000]" "##,##,##,##0;[<0]" "\ ##,##,##0;" "##'##0

But only minus (-) is removed with the no number separator

COL A COL B COL C COL D COL E

Item A B C D

Qty 1,200 1,000 800 3,000

Price 2,200 2,000 1,200 4,000

Amount 26,40,000 20,00,000 9,60,000 1,20,00,000

Expected :- if the result is a negative number -xxxxxxxxxx. It should to displayed as -x,xx,xx,xx,xxx

Amount is the Product of Qty and Price.

=B4-C4 => 20,00,000 - 9,60,000 = 6,40,000. [resulted a postive number and number format seperated is ok (x,xx,xxx)

=C4-B4 => 20,00,000 - 26,40,000 = 640,000. (got result applying >=10000000]" "##,##,##,##0;[<0]" "\ ##,##,##0;" "##'##0

It should be displayed as -6,40,000.

What is needed :

For Negative result -> negative symbol with result & Number separator X,XX,XX,XX,XXX

ingo

When applying your formula, negative symbol is not showing, but the separator are ok.

=C4-B4 => 20,00,000 - 26,40,000 = 6,40,000

Needed -> negative symbol with result & Number separator X,XX,XX,XX,XXX

.

Edited once, last by KSKwin: correction ().

• Hi,

I tried this formula [<=-100000000]" "- ##\,##\,##\,##\,##0;[<=-100000]" "- ##\,##\,##0;0.

Seems ok.

Pl. let me know if any issue you forecast .

Thanks for your help.

• Glad to hear you have managed to fix your problem

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi, How to combine the below formulae {both positive & negative cases)

[<=-10000000]""- ##\,##\,##\,##0;[<=-100000]"" - ##\,##\,##0;" "##,##0

[>=10000000]" "##\,##\,##\,##0;[>=100000]" " ##\,##\,##0;" "##,##0

• You can't do that with just a number format. You'll need to use conditional formatting rules to apply one of the two formats based on the value of the cell.

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!