# Calculation on Empty Field

## Recommended Posts

I have two fields called MAP and Price. I have another field called Profit which is a calculation of (MAP - Price). However for some records the MAP isn't required and is therefore empty or has 0. So of course profit ends up being calculated as a negative in those instances. Is there a better way to make that calculation so that it ignores the empty or 0 MAP field?

##### Share on other sites

Case(

isempty (Table::Map); Table::Price;Table::Map-Table::Price

)

##### Share on other sites

that didn't seem to work. The Profit field is still just showing as a negative. Most of the MAP fields actually have a value of 0 in them.

Edited by OneStop

##### Share on other sites

What type of field is MAP? And what should the result be when MAP is 0 or empty?

##### Share on other sites

MAP is a number field.

The problem is thus: I have a Price field and MSRP field, a MAP field, a Profit from MSRP and a Profit from MAP field.

Price of course is the vendor cost. To calculate basic profit I just subtract Price from MSRP (MSRP - Price) = Profit from MSRP

But in some cases there is a MAP price which I use to calculate the MAP profit. (MAP - Price) = Profit from MAP

A lot of the records don't actually have a MAP price so the field is set to 0. In those cases when I calculate the MAP - Price, it's showing a negative value.

##### Share on other sites

You did not answer my question. What should the result be when MAP is 0 or empty?

##### Share on other sites

Ideally, I'd like it to not show anything. In other I'd like the Profit from MAP to only be calculated if the value in MAP is >0

Edited by OneStop

##### Share on other sites
18 minutes ago, OneStop said:

I'd like the Profit from MAP to only be calculated if the value in MAP is >0

Well than either:

`If ( MAP > 0 ; MAP - Price )`

or just:

`If ( MAP ; MAP - Price )`

should work for you.

Although knowing a little about what MSRP and MAP stand for, I would expect you to actually want  to do:

`If ( MAP ; MAP ; MSRP )  - Price `

that is subtract the Price from MAP, if it exists - otherwise subtract it from MSRP.

--
Note: Ideally, if you don't have data for a field, the field should be empty - not contain 0.

##### Share on other sites

The 0 got imported from the Vendor's CSV file that way. I suppose I could have replaced it but I figure it's going to be an ongoing concern every time they import a new vendor file.

That last formula works perfectly. Thank You!

Quick question can you tell me what  the (MAP ; MAP ;  MSRP ) part actually does?

`If ( MAP ; MAP ; MSRP )  - Price`

##### Share on other sites
16 minutes ago, OneStop said:

can you tell me what  the (MAP ; MAP ;  MSRP ) part actually does?

The If() function has the following signature:

`If ( test ; result_if_true; result_if_false )`

The test argument is always evaluated as Boolean, so that:

`If ( MAP ; MAP ; MSRP )`

is just a shorthand for :

`If ( GetAsBoolean ( MAP ) ; MAP ; MSRP )`

And the expression GetAsBoolean ( number ) will return false when number is 0 or empty. Since MAP is not expected to be negative, you could just as well use:

`If ( MAP > 0 ; MAP ; MSRP )`

Edited by comment

##### Share on other sites

Excellent. Thank you so much for all your help. Learned something new today.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

×
×
• Create New...