Trying tô make a spreadsheet for Best Price Comparison

You could use the MIN function to get the lowest price:

=MIN(C2:G2) in cell H2 and fill it down the column by dragging the yellow dot.

To get a total at the bottom in row 7, defined as a Footer Row, you could use =SUM(H) .

SG

I'm not sure checkboxes are the best choice (since they are really designed so you can make more than one choice at a time) but here is one way:

The formula in M2, filled down the column:

=C*D+E*F+G*H+I*J+K*L

SG

Hi macmithosbr

The blue triangle is a "warning triangle." Clicking on the triangle will open the warning message:

In this case it is warning that you are using the TRUE or FALSE value returned by the checkbox as if it were a number. Warning triangles are a sign that what is being done is 'not quite proper,' but that it will not affect the functioning of the formula—you'll get the same answer as you would if you replaced each unchecked box with 0 and the checked box with 1.

SG noted that checkboxes are designed for situations where one or more or the boxes may be checked. If you want to ensure that you have checked only one vendor in a row, you could make this addition to the formula:

IF(COUNTIF(C2:L2,TRUE)=1,C×D+E×F+G×H+I×J+K×L,"check one box")

Regards,

Barry

Hi macmithosbr,

I see two images in your post.

The top one shows the formula in the Formula Editor, and a correct result in the selected cell.

The second shows what appears to be the same table with the same data, but with error triangles in cells M2 and M7.

Diagnostic steps (these can be skipped, but may offer a learning opportunity):

The error message in M7 will be "Cell M2 contains an error.

What is the error message in cell M2? (Click the triangle to display the message.)

What is the formula in M2? How is it different from the formula in my example?

If you see no difference, try checking the box in column K. Do you get a correct result ("check one box")?

Now uncheck the box in column G. Do you get a correct result ("R$6,23")?

If those two results are both correct, then the error is in the data in H2.

Simple correction steps (less learning involved):

Click once on cell M3 to select it.

Move the mouse pointer to the top edge of M3.

Click on the Fill Handle (yellow circle) that appears at the center of the top boundary of the cell and drag it upward to fill the formula from M3 into M2.

Both error triangles should disappear and the results should be visible in M2 and M7.

If this does not correct the errors, please post:

Screen shot of the error message in M2

Screen shot of error message in M7

Formula copied from M2, and pasted into your message.

Regards,

Barry

Note that your formula in your screenshot doesn't display any of the colored address tokens:

That indicates Numbers has failed to recognize it as a formula.

Try:

1. Select M2 and hit <delete> to remove all contents and formatting (the Data Format should become Automatic)
2. Select and copy Barry's formula from his post above exactly as it is displayed (i.e., without a leading =)
3. Click M2 and type = to call up the formula editor
4. Command-v to paste in Barry's formula
5. Click the green tick or hit <return> to accept the formula.

SG

"Now is back to what it was before your new formula and again it is possible to check 2 prices or more in the row making a SUM of them at the end."

OK. That means I had wrongly assumed you had placed my version into M3 as well—which doesn't really make sense, seeing that one was working and the other was not. It also explains the syntax error in my formula, and why it does not affect SG's earlier version.

In English speaking areas of North America, the period ( . ) is used as the decimal separator ( 0.12 ), and the comma is used as a list separator in functions. In other regions, the decimal separator is the comma ( , ), and the list separator is the semi colon ( ; ). The 'list' here is the three parts of the IF statement

Here's the change you need:

Change the three commas in the formula to semi colons.

Revised: IF(COUNTIF(C2:L2;TRUE)=1;C×D+E×F+G×H+I×J+K×L;"check one box")

When the formula is working, fill it down to M6.

Regards,

Barry