# Thread: formulae (2000/xp)

1. ## 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. ## 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. ## 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

4. ## Re: formulae (2000/xp)

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

5. ## 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
•