Using multiple cells to decide if false

**danjstock** · 06-10-2010, 05:37 AM

Hi,

I am building a spreadsheet to display a quotation for customers. I have created two drop down lists that contain the width and height of kitchen doors. I want to use the IF function to look at the two selected sizes within each drop down list to generate a price from a table on sheet two.

Firstly, is the IF function the right thing to be using? I can get this to work fine with one drop down list but I can't work out how to get it to check the contents of two cells to then generate a result.

The function I have written is:

=IF(A2=115&B2>160,'Kitchen Prices'!E4,0)

Thanks

D

**sweep** · 06-10-2010, 05:47 AM

Hi,

You might be better served by using index and match. I presume that on the "Kitchen Prices" tab there is a table of width, height and price of the doors?

If so, you could use

=INDEX(table_of_prices,MATCH(A2,Range of widths,0),MATCH(B2,range_of_heights,0))

**davesexcel** · 06-10-2010, 05:49 AM

Possibly set up a table, length down the side and width across the rows, then use the index match formula, or sumproduct.

Here is a good start for index match

http://contextures.com/xlFunctions03.html#Index

**danjstock** · 06-10-2010, 06:15 AM

Thanks for the tip on index / match. The data is set out with door heights in a column and width along a row.

I've modified the command to include the kitchen prices and the column / row for height / width as below.

The cell just returns a 0 no matter what combination of sizes I select from the lists...

=INDEX('Kitchen Prices'!C4:K24,MATCH(B2,width,0),MATCH(A2,height,0))

**davesexcel** · 06-10-2010, 06:53 AM

It may be best to supply a workbook with an example of the table get your question solved faster.

**sweep** · 06-10-2010, 06:56 AM

So width and height are named ranges defined as C3:K3 and B4:B24 (or wherever the width and height are stored on the axis of the price table)?

If so, this should be working for you. Can you post your work book?

**danjstock** · 06-10-2010, 07:22 AM

Worksheet attached.

Thanks

D

**sweep** · 06-10-2010, 07:52 AM

OK,

Change prices to reference ='Kitchen Prices'!$C$4:$K$24

use this formula

=INDEX(prices,MATCH(A2,height,0),MATCH(B2,width,0))

**danjstock** · 06-10-2010, 08:17 AM

Ok so I have entered

=INDEX(prices,MATCH(A2,height,0),MATCH(B2,width,0))

Where do I change prices to:

='Kitchen Prices'!$C$4:$K$24

Thanks

D

**sweep** · 06-10-2010, 08:19 AM

You have a named range called "prices".

That needs to refer to ='Kitchen Prices'!$C$4:$K$24

To access the names editor press ctrl and f3.

**danjstock** · 06-10-2010, 08:23 AM

Fantastic! Thanks a lot for the help.

I'm pretty good with Excel on a day to day basis, what is the best way to learn more without going on a course? I have Excel for Dummies but its not very good!

**sweep** · 06-10-2010, 08:26 AM

I guess as good a way as any is to keep looking at forums, and trying stuff out. There are generally many ways to achieve your goal using excel.

**danjstock** · 06-10-2010, 08:37 AM

As always I guess the internet is the best place to get this information.

I have added another drop down list after the cost column called finishes as the price varies depending on the finish. I have created a range detailing the different finishes and in the next column shown the percentage increase for each option.

To calculate the final price including finish type I guess I would have to use the Match / Index function again and not the IF command, am I right?

Would it be:

=INDEX(finish,MATCH(D2,finishcost,0)

finishcost is a range of percentages
finish is a range of finish types

I then need to add the percentage on to the standard cost but I'm not sure how to add an addition function to the index function

Using multiple cells to decide if false

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Re: Using multiple cells to decide if false

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions