+ Reply to Thread
Results 1 to 5 of 5

Calculation based on multiple Combo Boxes

  1. #1
    Registered User
    Join Date
    10-10-2008
    Location
    St. Louis, MO
    MS-Off Ver
    Office 365
    Posts
    31

    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
    Last edited by emueller; 09-13-2010 at 11:26 AM.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    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. #3
    Registered User
    Join Date
    10-10-2008
    Location
    St. Louis, MO
    MS-Off Ver
    Office 365
    Posts
    31

    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. #4
    Registered User
    Join Date
    10-10-2008
    Location
    St. Louis, MO
    MS-Off Ver
    Office 365
    Posts
    31

    Thumbs up 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. #5
    Registered User
    Join Date
    10-10-2008
    Location
    St. Louis, MO
    MS-Off Ver
    Office 365
    Posts
    31

    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.
    Last edited by emueller; 09-10-2010 at 10:42 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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