Jump to content

Calculation on Empty Field


This topic is 2635 days old. Please don't post here. Open a new topic instead.

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? 

Link to comment
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
Link to comment
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. 

Link to comment
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
Link to comment
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.

Link to comment
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
Link to comment
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
Link to comment
Share on other sites

This topic is 2635 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.