Need to display a blank field instead of $0

Platform discussions Special workflows & use cases
, ,
1

I have many formula columns that I have returning a blank value if the formula within the column doesn’t contain a value or is $0 using the =“0”,“” format. For some reason no matter what I try for a formula in a column that totals the values in three other formula columns it either breaks the formula or displays $0. Formula is below. In many cases 1st, 2nd and 3rd funds do not contain a value and I just want the column totaling those to be blank instead of $0.

ROUND(SUM({1st Funds},{2nd Funds},{3rd Funds}),2)

image
image829×246 14.3 KB

2

Hey Seth,

Thanks for sharing this with us.

I’ve had some expert help from one of our awesome formula gurus, so let me know if either of the two formulas help achieve your end goal here?

Formula 1

IF({1st Funds}="0", "", ROUND(SUM({1st Funds},{2nd Funds},{3rd Funds}),2))

This formula includes an IF() condition that says - if the 1st funds column is blank, then output blank, if it’s not blank, then do the calculation - assuming that 1st, 2nd, and 3rd funds are in order…

Formula 2

IF(AND({1st Funds}="0", {2nd Funds}="0", {3rd Funds}="0"), "", ROUND(SUM({1st Funds},{2nd Funds},{3rd Funds}),2))

This formula will output the correct value, based on the cells that are not blank (populated with values).

Check out the differences in the formulas below:

image (147)
image (147)1262×498 12.2 KB

Let me know what you think!

3

Thank you Bianca,

I actually had to do it with the 1st % column instead since that comes before 1st Funds, so once I made that small change the formula worked.

1 Like
4

I do have one more formula question. Currently I have formulas calculating commissions based on a status in a “Team Split” column. If using a ,“” at the end to display nothing if the chosen status would not apply a commission to a broker. Formula and screenshot below.

IF({Team Split}=“DM + SB - 50%”,(MULTIPLY({Team Split Funds},0.075)),IF({Team Split}=“DM + SB - 65%”,(MULTIPLY({Team Split Funds},0.0975)),“”))

image.png
image.png1468×510 75.2 KB

Now if I simply add in some language to round down to two decimal points instead of three it works if there’s a value in the field but then puts a red exclamation point in every other field instead of just the blank value like before.

ROUND(IF({Team Split}=“DM + SB - 50%”,(MULTIPLY({Team Split Funds},0.075)),IF({Team Split}=“DM + SB - 65%”,(MULTIPLY({Team Split Funds},0.0975)),“”)),2)

image.png
image.png1451×508 77 KB

Can’t figure out how to get around that.

5

Hi Bianca,

I sent this email almost three weeks ago and never got a response.

image.png
image.png1468×510 75.2 KB

image.png
image.png1451×508 77 KB

6

Hey Seth,

My sincerest apologies for missing your reply here! I have taken your request to our formula gurus and will get back to you as soon as we find a solution. Thank you for your continued patience and sorry again for my oversight :pray:

8

Hey @shunkins - thanks for your patience!

The issue here was related to the fact that we are applying the ROUND() function to a non-numeric (“”)…

To get around this, we just need to apply the ROUND() function separately to the 2 values that produce numeric outputs as follows:

IF({Team Split}="DM + SB - 50%",ROUND((MULTIPLY({Team Split Funds},0.075)),2),IF({Team Split}="DM + SB - 65%",ROUND((MULTIPLY({Team Split Funds},0.0975)),2),""))

Does this help?

9

Awesome, yes that did the trick!

1 Like