topic Re: How do I create a formula with multiple "if" conditions? in Discussions

How do I create a formula with multiple "if" conditions?

dbc — Tue, 27 Feb 2024 02:56:35 GMT

I'm working with a real estate data set. Several thousand rows are missing bathroom data. I'd like to write a formula for a new column that creates bathroom data based on the average bathroom by bedrooms data in the rest of the data set.

For example, if baths is missing then beds </= 2 is 1 bath, beds = 3 is 2, beds </= 5 is 3, beds </= 8 is 4, beds >/= 9 is 5.

Or, is there a better way to estimate the bathroom data for the missing values?

Re: How do I create a formula with multiple "if" conditions?

txnelson — Tue, 27 Feb 2024 03:34:15 GMT

Welcome to the Community.

My suggestion is that you plot the data with the number of beds on the Y axis, and the number of baths on the Y axis and the number of beds on the X axis. Either Graph Builder of Fit Y by X will work for this. From there you should be able to see what the best estimates for baths should be.

You could also just use the mean value for the number of baths for each group of beds.

For Each Row(
	If( Is Missing( :baths ),
		:baths = Col Mean( :Baths, :Beds )
	)
);

To handle your original specification, the script would be

For Each Row(
	If( Is Missing( :baths ),
		If(
			:Beds <= 2, :Baths = 1,
			:Beds == 3, :Baths = 2,
			:Beds <= 5, :Baths = 3,
			:Beds <= 8, :Baths = 4,
			:Baths = 5
		)
	)
);

Re: How do I create a formula with multiple "if" conditions?

dlehman1 — Wed, 28 Feb 2024 12:14:09 GMT

Besides txnelson's suggestion, there are two non-script ways to accomplish what you decide to do. If there are many different IF clauses, you may find Recoding the column to be the easiest way to do it. To use the IF function and include multiple IF clauses, highlight the box for the Else clause and press the comma key. That will add a second IF clause to the original dialog. You can do this as many times as you want to get as many IF clauses as you desire (with the last one being Else). As I said, if there are many of these, recoding is probably easier.

Re: How do I create a formula with multiple "if" conditions?

dlehman1 — Wed, 28 Feb 2024 12:19:00 GMT

Sorry, I misread your question. I see that you want to estimate the missing data. I do think you need to start with a visualization - if there is a fairly close relationship between number of bedrooms or size with number of bathrooms, then it can make sense to estimate the missing data from the relationship derived from the nonmissing data. If the relationship doesn't look very systematic, then this is probably not a good idea. If you are building models using this data, then the Informative Missing option will take care of the missing data for you - but in a particular way. It will replace missing continuous data with the average value (which may not be the best approach, depending on what the visualization tells you) and add a new indicator column for whether or not the data was missing (it adds that for continuous or nominal missing data).

Re: How do I create a formula with multiple "if" conditions?

WebDesignesCrow — Mon, 11 Mar 2024 02:26:17 GMT

To add the explanation by @txnelson & @dlehman1 ,

you can also check the JMP notes here regarding IF statement:

https://www.jmp.com/support/help/en/17.2/index.shtml#page/jmp/if-function.shtml