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

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

.

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.

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

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

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