Choose Your New Car  IF Then, Conditions, Weighted Totals

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 popup 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 opup 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

Like (0)


Yellowbox New South Wales, Australia
Hi e,
Interesting! I assume the weightings are your knowledge and opinion, not the customer's wishlist. 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.

Like (0)


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 noncalculation 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

Like (0)


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

Like (0)
