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