# Calculation based on multiple Combo Boxes

1. ## Calculation based on multiple Combo Boxes

Here is what I'm trying to do.

I'm trying to calculate a price based on 4 columns, 3 of which are Combo boxes.

IE:
Columns are:
Trim Size
- Small Trim
- Large Trim

Format
-SGML
-InDesign
-Quark
-hardcopy

# of Pages (entered manually)

English
-Yes
-No

Price (this is calculated based on the previous fields)

On sheet2 I have the amounts in a table:
--------------- Small Trim------- Large Trim
SGML -------------- 1 ---------- 1.5
InDesign ------- 1.15 ----------- 1.7
Quark ---------- 1.4 -------------- 2.1
Hardcopy ------- 2.35 ------------- 3.3

If English is Yes 20% 30%

Is there are way to calculate this easily? I know VBA if I built a new form, but the person I'm working on this for wants it in Excel spreadsheet and I can't figure it out. Any help would be appreciated.

IE: If the user wants to know the price of 25 pieces of small trim, InDesign format, in English. It would be (1.15 * 25) * .20 = Price

2. ## Re: Calculation based on multiple Combo Boxes

Hi Emueller, try

=(INDEX(Sheet2!\$B\$2:\$C\$5,MATCH(B1,Sheet2!\$A\$2:\$A\$5,0),MATCH(A1,Sheet2!\$B\$1:\$C\$1,0))*C1)*IF(D1="Yes",0.2,0.3)

Assumes that the lookup table on Sheet2 is laid out:
A2:A5 are formats (SGML, InDesign, Quark, Hardcopy)
B1:C1 are sizes (Small Trim, Large Trim)
B2:C5 are price points

Sheet1 A1 contains the trim size.
Sheet1 B1 contains the Format.
Sheet1 C1 contains the # of pages.
Sheet1 D1 contains Yes or No.

3. ## Re: Calculation based on multiple Combo Boxes

That looks about right, but I'm getting a #N/A result. I'm thinking that my combo boxes must not be saving the selection to the appropriate cell.

4. ## Re: Calculation based on multiple Combo Boxes

Yes, that is exactly what it was. My combo boxes were wrong.

Paul, your formula worked great! Thank you very much for the help!

5. ## Re: Calculation based on multiple Combo Boxes

Actually, one things seems to not be working right.

The IF(D2="Yes", 0.2,0.3) isn't working correctly.

I think that it's written to multiply 0.2 if Yes, but 0.3 if no. I need it to multiply either if Yes, but 0.2 is Small Trim, 0.3 if Large Trim. Is there a way to figure that out in this formula?

*****************************************************************************
UPDATE: I figured it out. The whole formula now looks like this:

=(INDEX(Sheet2!\$B\$2:\$C\$5,MATCH(B2,Sheet2!\$A\$2:\$A\$5,0),MATCH(A2,Sheet2!\$B\$1:\$C\$1,0))*C2)*IF(AND(D2="Yes",A2="Small Trim"),1.2,IF(AND(D2="Yes",A2="Large Trim"),1.3,1))

Paul, thanks for your help on this, much appreciated.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1