## Choose Your New Car - IF Then, Conditions, Weighted Totals

381 Views 4 Replies Latest reply: Jan 14, 2013 7:54 PM by Barry
Calculating status...
Currently Being Moderated
Jan 8, 2013 8:45 PM

Here is my problem:

A person really wants a new car. I have 4 cars that are very similar to one another, and the person really likes all four based on appearance only.  I figure that if I ask enough distinguishing yes and no questions, I can narrow it down to one car out of the four that would be best suited for the person and I can be certain they would be happy with it because of the information they provided.

So I want to weight each yes/no question (0-4) and create a condition or if-then (forgive me for my numbers illiteracy) so that if they answer yes to question #1 for example, then that weighted number (we will say 3 for this example) which happens to be associated with a honda civic (one of the four cars) will go into a cell that keeps a running total for the honda civic. At the end of the questions, based on the way the person answered, the highest total number would suggest one of the four cars would be best suited for him.

So, I am looking for a way to create a formula to satisfy what I am hoping to do. Can anyone help?  Below is what I thought it might look like.  Thanks!

MacBook, Mac OS X (10.6.3)
• Level 7 (29,095 points)

Tallying/counting and adding the points is a fairly simple task. The more difficult task is evaluating the importance of each feature without letting the knowledge of which vehicle has that feature and which one lacks it bias the value assigned.

How are you deciding the weightings for each answer?

Both the Honda and the Kia have side air bags, yet the weight given a 'yes' to this feature is different for each vehicle—4 for Honda, 3 for Kia. Why?

What are the criteria that make only the Hyundai 'kid friendly'? The term itself is an evaluation of several pieces of data.

The weighting figures given, say that side air bags are twice as important as 'kid friendly' (unless they're on a Kia, is which case they're only one and a half times as important), and 'kid friendly' is twice as important as having a spoiler.

And once you've decided how much weight should be given to each feature, there's the question of whether this is the same weighting that would be given by 'the person.'

Perhaps the simplest method is to allow 'the person' to assign the number of points to each feature, limiting the range to between 0 (not important) and 5 (very important).

Once you've solved those issues, coming up with a total for each vehicle is a piece of cake:

Table 1 is the input table, containing the list of features in column A and a set of pop-up menu cells with a seven item menu: " ", 0, 1, 2, 3, 4, 5

The first item is a 'blank' item, containing a single space (about as close as you can come to a 'blank' when creating a op-up menu by ordinary means).

Table 2, to the right of Table 1 is a checklist of the features found on each vehicle, plus a tally sheet collecting the weight value of each feature found on each car. The table contains one formula:

F2 (and filled right and down to I15): =IF(B,Table 1 :: \$B2,"")

Columns F through I should be hidden.

Table 3 is a summary table which summarizes the weighted points collected by each vehicle. It contains one formula:

A2 (and filled right to D2): =SUM(Table 2 :: F)

The revision of this formula below will suppress the calculations in Table 3 (leaving the four total cells 'empty') until a numeric weighting has been chosen for all fifteen of the features listed:

A2 (and filled right to D2): =IF(COUNT(Table 1 :: \$B)=ROWS(Table 1 :: \$B)-1,SUM(Table 2 :: F),"")

Regards,

Barry

• Level 4 (3,980 points)

Hi e,

Interesting! I assume the weightings are your knowledge and opinion, not the customer's wish-list. If so, instead of entering y (4) for Honda Civic airbags, you could just enter 4 (I assume that it gets a 4 because it has side air bags, and you consider those air bags to be reliable). I assume the Chevy Malibu has no side air bags, and so gets 0. Then you have just numbers that are easier to deal with.

How about this for the Features Table (I renamed this table Features in the left sidebar and added a label in cell A1):

 Features side air bags spoiler kid friendly TRUE FALSE TRUE Honda Civic 4 0 0 Chevy Malibu 0 1 0 Hyundai Elantra 0 0 2 Kia Forte Koup 3 0 0

There are check boxes in Row 2 under each feature (they show here as TRUE/FALSE). Tick means yes, it is important to the customer. No tick means no, it is not important to the customer.

You can insert check boxes with Inspector > Cells, click on Cell Format to choose tick boxes.

Add another table and rename it Outcome. The Outcome Table uses IF so that a feature only counts IF a checkbox is ticked. enter this in cell B3:

=IF(Features :: B\$2 = TRUE,Features :: side air bags Honda Civic,0)

Select and copy down then across to fill to cell D6. Please note that these formulas will only work if you name the Table "Features" otherwise you must use cell references.

 Outcome side air bags spoiler kid friendly Total Honda Civic 4 0 0 4 Chevy Malibu 0 0 0 0 Hyundai Elantra 0 0 2 2 Kia Forte Koup 3 0 0 3

The Total column uses  =SUM(B3:D3)

Copy down to D6.

I hope this helps.

Ian.

• Level 7 (29,095 points)

OK, I've had a chance to look at a partial data table that goes with ebrowning's question.

The actual question (and the data) turned out to be a bit more complicated than what was stated above. Here's a sample from the data table that goes along with the question:

I started with a reorganization of the data into two tables; one to contain the point values for 'Yes" answers to each question, the other for the point values of the "No" answers. The separation simplified the SUMIFS formula I intended to use to calculate the total points for each vehicle.

As the questions would likely be listed in a column (or more than one column) down the page, I also transposed the tables to list the questions in the first column and the cars in the first row. Row 1 and Column A of each of these tables was made a Header row and Hearer column respectively to mark them as non-calculation rows/columns for some functions.

Here's (part of) one of the transposed data tables:

The "No" table is a duplicate of this "Yes" table, with the "No" data in the body cells. These tables contain no formulas.

Data input was set up as a two column table containing the questions in column A and checkboxes in column B. A checked box indicated a desirable feature. This table contains no formulas, but one could be used in column A to pull the questions from one of the data tables, ensuring that these were in the same order.

Totals for each vehicle are calculated on a three row table, with a column for each car, part of which is shown here:

There are three formulas on this table:

B2, and filled right: =SUMIFS(Yes :: B,Questions :: \$B,TRUE)+SUMIFS(No :: B,Questions :: \$B,FALSE)

For each column (car), this sums the point values on the same row of the Yes table as the checked boxes on Questions plus the point values on the same row of the No table as the unchecked boxes on Questions.

Note that Yes, No and Questions must each have the same number of rows.

A2: =COUNTIF(2:2,"="&MAX(2:2))

It is possible that two or more cars will score the same total, and that this total will also be the highest score. The formula counts the number of cars scoring the highest total. This number is used later in the Top Picks table.

C2, and filled right: =B2+COLUMN()/100000

This formula is used to ensure that there are no duplicate values in row 2 (the total scores for each car), by adding a tiny amount to each score based on its position (column) in the table, ensuring that the fractional part of each score is unique to the row, making each amended score detectable.

Note that there is a bias—for any two cars with identical scores in row 2, the one further right will have the larger score in row 3.

The final table(s) extract the top scoring cars and display them and their scores. Only one of these tables would be used.

Both tables use the same functions to extract the names and scores of the top picks:

Top Picks (A)::B1, and filled Right:

=OFFSET(Totals :: \$A\$1,0,MATCH(LARGE(Totals :: \$3:\$3,COLUMN()-1),Totals :: \$3:\$3,0)-1)

This finds the six largest values in row 3 of totals, determines the position of each in that row, and uses the result to retrieve the name of the car from row 1 of Totals.

Top Picks (A)::B2, and filled Right:

=OFFSET(Totals :: \$A\$1,1,MATCH(LARGE(Totals :: \$3:\$3,COLUMN()-1),Totals :: \$3:\$3,0)-1)

Same formula and function as above. The single change (row offset is changed from 0 to 1) makes it retrieve the point total from the cell below the car's name in Totals.

Top Picks (B) embeds the formula above in an IF statement to suppress the display of cars with totals below that of the top scorer(s). In the B1 example below, the added parts are shown in regular type, the part kept from the original in italics.

(B)::B1, and filled Right:

=IF((COLUMN()-2)<Totals :: \$A\$2,OFFSET(Totals :: \$A\$1,0,MATCH(LARGE(Totals :: \$3:\$3,COLUMN()-1),Totals :: \$3:\$3,0)-1),"")

B2: same as B1, with the same change as in B2 of Top Picks (A)  ( 0 becomes 1 ).

For ease in constructing (and reading) the formulas, all tables were placed on the same sheet. In use, Any of the tables can be moved to another Sheet (after that Sheet has been added to the Document). Numbers will adjust the formulas to acommodate the change.

Questions would probably be on Sheet 1, Top Picks also on Sheet 1 or on Sheet 2, and Yes and No on Sheet 2 or 3, out of sight.

Regards,

Barry

• Level 7 (29,095 points)

Next step: Expanding the document to accept and process more questions.

Start with the Question table. Drag the Row handle (bottom left of the table when selected) to add enough rows for all questions, then enter the questions. Select and Copy (all of) the question cells. Most cells in Totals and Your Pick (either version) will now show red error triangles. These will disappear when you correctly complete modifications to the Yes and No tables.

Next, do  the two data tables, Yes and No.  Click on the first question. Paste. Repeat with the other table. Enter the corresponding points on the appropriate table.

Totals and Your Pick tables require no modifications.

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.