Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Custom Value When Result is "0" Still Seen as ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

1
1588
1

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 10, 2020 03:47 PM

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 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 10, 2020 11:23 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 11, 2020 08:47 AM

Our largest subtotal will be in the ten thousands.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 11, 2020 10:44 AM

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

)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 11, 2020 11:37 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 11, 2020 11:58 AM

Reply