Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formulae (2000/xp)

    I'm trying to build a pricing spreadsheet. I have a service that charges different rates for a piece of test equipment. This charge is based on the size of the equipment being tested. For example, from 0 to 400 is one price, from 401 to 750 is another price 751 to 1000 etc:. I want to be able to input any size ie:,(500) in a cell [A1] and calculate the cost, placing the answer in another cell [a10]. My attempts to ise (if) or (or) statements are failing me. Please help !!

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: formulae (2000/xp)

    Hi,

    Here is a worksheet with some sample VLOOKUPs which I think will do what you want.

    Also Checkout VLOOKUP in the Excel Help.

    Good Luck!

    Peter Moran

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formulae (2000/xp)

    Hi,

    When using IF statements, you can only nest them 7 levels deep. This can be a problem if you have more than 7 conditions to test. So, a lookup table is often better. Also, when using a series of IF statements to test whether a number exceeds a set of threshholds, you'd generally start with the largest one first, then work your way down. For example:
    =IF(Items>1000,Items*22,IF(Items>750,Items*23,IF(I tems>400,Items*24,Items*25)))
    Conversely, if you want to test whether a number is less than a set of threshholds, you'd generally start with the smallest one first, then work your way up.

    The attached demo shows what can be achieved with a lookup table. You've already got one solution using VLOOKUP. As well as using named ranges, this one uses combined INDEX & MATCH functions, which I find more flexible.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: formulae (2000/xp)

    Hi,
    Just as an alternative, in your example you could also use:
    =LOOKUP(A10,SizeTable)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formulae (2000/xp)

    Thanks to all... I think I'll use the vlookup function. It's not as intimidating as the other method.

Posting Permissions

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