+ Reply to Thread
Results 1 to 5 of 5

How can I get Excel to automatically chose one of two answers?

  1. #1
    Erwin
    Guest

    How can I get Excel to automatically chose one of two answers?

    I am trying to design an automobile purchase order form that uses option
    codes and its corresponding prices. When I enter one option code on one
    cell/row (i.e. LCB4 for Leather Upholstery), the cell next to it will
    automatically kick out the correct price of $1450. However, another option
    code (i.e. ZPP for an automobile Premium Package) at the price of $2900
    includes Leather Upholstery. If ordered, I need to enter ZPP on another
    cell/row that will generate the priceof $2900 next to ZPP; however, I do not
    know how to get Excel to automatically recognize the ZPP code and change my
    Leather Price of $1450 to $0 since it is included in the Premium Package.
    Can anyone assist? Thanks again.
    --
    Erwin

  2. #2
    L. Howard Kittle
    Guest

    Re: How can I get Excel to automatically chose one of two answers?

    Hi Erwin,

    Maybe something like this in F1 which returns a price from range J1:K5 for
    LCB4.

    =IF(E2="zpp",0,VLOOKUP(E1,J1:K5,2,0))

    Where column E is where you are listing the options
    E1 = LCB4
    E2 = ZPP (or not)

    HTH
    Regards,
    Howard

    "Erwin" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to design an automobile purchase order form that uses option
    > codes and its corresponding prices. When I enter one option code on one
    > cell/row (i.e. LCB4 for Leather Upholstery), the cell next to it will
    > automatically kick out the correct price of $1450. However, another
    > option
    > code (i.e. ZPP for an automobile Premium Package) at the price of $2900
    > includes Leather Upholstery. If ordered, I need to enter ZPP on another
    > cell/row that will generate the priceof $2900 next to ZPP; however, I do
    > not
    > know how to get Excel to automatically recognize the ZPP code and change
    > my
    > Leather Price of $1450 to $0 since it is included in the Premium Package.
    > Can anyone assist? Thanks again.
    > --
    > Erwin




  3. #3
    Dave O
    Guest

    Re: How can I get Excel to automatically chose one of two answers?

    I mocked up a scenario and used VLOOKUPs to price various option codes.
    For the LCB4 code, leather seats, I used this formula:
    =IF(SUMPRODUCT(--(B2:B6="ZPP"))>0,0,VLOOKUP(B6,I6:J10,2,0))

    The formula looks for the existence of ZPP in the range where option
    codes are entered. If it finds ZPP, it returns $0 for leather seats.
    If ZPP is not found, it performs the VLOOKUP for LCB4 as usual. In
    this mockup, B2:B6 is the range that holds option codes; I6:J10 is the
    array holding the option code and the dollars associated with them.


  4. #4
    Erwin
    Guest

    Re: How can I get Excel to automatically chose one of two answers?

    Hi Howard,

    Thank you so very much! Very very helpful.

    Erwin
    --
    Erwin


    "L. Howard Kittle" wrote:

    > Hi Erwin,
    >
    > Maybe something like this in F1 which returns a price from range J1:K5 for
    > LCB4.
    >
    > =IF(E2="zpp",0,VLOOKUP(E1,J1:K5,2,0))
    >
    > Where column E is where you are listing the options
    > E1 = LCB4
    > E2 = ZPP (or not)
    >
    > HTH
    > Regards,
    > Howard
    >
    > "Erwin" <[email protected]> wrote in message
    > news:[email protected]...
    > >I am trying to design an automobile purchase order form that uses option
    > > codes and its corresponding prices. When I enter one option code on one
    > > cell/row (i.e. LCB4 for Leather Upholstery), the cell next to it will
    > > automatically kick out the correct price of $1450. However, another
    > > option
    > > code (i.e. ZPP for an automobile Premium Package) at the price of $2900
    > > includes Leather Upholstery. If ordered, I need to enter ZPP on another
    > > cell/row that will generate the priceof $2900 next to ZPP; however, I do
    > > not
    > > know how to get Excel to automatically recognize the ZPP code and change
    > > my
    > > Leather Price of $1450 to $0 since it is included in the Premium Package.
    > > Can anyone assist? Thanks again.
    > > --
    > > Erwin

    >
    >
    >


  5. #5
    Erwin
    Guest

    Re: How can I get Excel to automatically chose one of two answers?

    Hi Dave,

    Thank you so much too! Very very helpful.

    Not knowing all the functions of Excel, I was stumped for the longest time.

    Best regards,
    Erwin

    --
    Erwin


    "Dave O" wrote:

    > I mocked up a scenario and used VLOOKUPs to price various option codes.
    > For the LCB4 code, leather seats, I used this formula:
    > =IF(SUMPRODUCT(--(B2:B6="ZPP"))>0,0,VLOOKUP(B6,I6:J10,2,0))
    >
    > The formula looks for the existence of ZPP in the range where option
    > codes are entered. If it finds ZPP, it returns $0 for leather seats.
    > If ZPP is not found, it performs the VLOOKUP for LCB4 as usual. In
    > this mockup, B2:B6 is the range that holds option codes; I6:J10 is the
    > array holding the option code and the dollars associated with them.
    >
    >


+ 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