Custom Value When Result is "0" Still Seen as a Number

[Lisa_Nelson]

I’m struggling with formatting my formula values. I’m trying to design a proposal in Page Designer. We list the price of all items, even when we don’t think we will use them so the client knows what they would cost if they are used. On the proposal, I want the subtotal value to be blank or * instead of $0.00. Easy enough, but then I want it to still format as a dollar value for the other items.

I have column “Qty” where I manually enter the quantity of the item to go on the proposal. “Subtotal” multiplies the “Qty” and “Price”. “Qty for Proposal” and “Subtotal for Proposal” are the fields that are used for Page Designer.

Is there a way to have my cake and eat it too? Thanks.

[Justin_Barrett]

Yes, it’s possible to have the output you want. First, though, what’s the largest number you envision having as a subtotal? The number of digits will determine the design of the formula to reformat the value as currency.

[Lisa_Nelson]

Our largest subtotal will be in the ten thousands.

[Lisa_Nelson]

@Justin_Barrett Your response led me to find a previous post of yours talking about Pretty-Print Routines.

I made a simple addition (first line and last line) to your formula found in the Number table and it works lovely. There are some extras in the formula that I don’t need and will probably pare down later when I have time. but I’m up and running now.

Thank you!

IF(Subtotal=0,"",

IF( LEFT({Subtotal}&’’,1,1)=’-’, ‘-’, ‘’)&’$’& IF( ABS(VALUE({Subtotal}&’’))>=1000000, INT(ABS(VALUE({Subtotal}&’’))/1000000)&’,’& IF( INT(MOD(VALUE({Subtotal}&’’),1000000)/1000)<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({Subtotal}&’’),1000000)/1000)&’’)), ‘’)&INT(MOD(VALUE({Subtotal}&’’),1000000)/1000)&’,’& IF( INT(MOD(VALUE({Subtotal}&’’),1000))<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({Subtotal}&’’),1000))&’’)), ‘’)&INT(MOD(VALUE({Subtotal}&’’),1000)), IF( ABS(VALUE({Subtotal}&’’))>=1000, INT(ABS(VALUE({Subtotal}&’’))/1000)&’,’& IF( INT(MOD(VALUE({Subtotal}&’’),1000))<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({Subtotal}&’’),1000))&’’)), ‘’)&INT(MOD(VALUE({Subtotal}&’’),1000)), INT(ABS(VALUE({Subtotal}&’’)))))&’.’& IF( LEN(ROUND(MOD(VALUE({Subtotal}&’’),1)*100,0)&’’)<2, ‘0’, ‘’ )& ROUND(MOD(VALUE({Subtotal}&’’),1)*100)

)

[Justin_Barrett]

I’m glad that you found a solution, but I should point out that the thread you linked isn’t mine. I simply made a brief comment in it. :slightly_smiling_face:

[Lisa_Nelson]

Thank you for the correction. I have been reading so many solutions written by you, that I incorrectly credited you with the post. Thank you @W_Vann_Hall for your post on Pretty-Print. I even enjoyed the history lesson on the terminology.