Skip to main content

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

  • December 10, 2020
  • 5 replies
  • 59 views
L

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.

5 replies

Justin_Barrett
Forum|alt.badge.img+21

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.

L
L
  • Lisa_Nelson
  • Author
  • New Participant
  • December 11, 2020

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.


Our largest subtotal will be in the ten thousands.

L
  • Lisa_Nelson
  • Author
  • New Participant
  • December 11, 2020

@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
Forum|alt.badge.img+21

@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)

)


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:

L
  • Lisa_Nelson
  • Author
  • New Participant
  • December 11, 2020

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:


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.

Justin_Barrett
Julian_E_Post
Terms & ConditionsAccessibility statement