IF Function (I Think!)

[Andy Roberts]

What I want to do is as follows:-

I have a set of fees I want to apply to prices depending on their values
I have may fees listed e.g. <1.00 = 10p, 1.01 to 4.99 = 20p 5.00 above = 30p

In one column I have a price (say £1.50) and in another I want the value to
be whichever applies to the price in the first column (in this case 20p).
Is this possible. I think it is the IF function. The auto calculating cell
needs to look to another cell for a value based on the initial price. If I
alter the fees say from 10p to 15p then this would need to update the auto
calculating cell.

Hopefully I've explained myself (?)

Thanks

 

[Anne Troy]

Actually, you might want a vlookup. Check out
www.myexpertsonline.com/freedls/pricelist.xls and see what happens when you
hit the dropdown and choose a value. For yours, you'd be typing the value
in, I suppose.
Here's the skinny on vlookup:
http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Ron Rosenfeld]

What I want to do is as follows:-

I have a set of fees I want to apply to prices depending on their values
I have may fees listed e.g. <1.00 = 10p, 1.01 to 4.99 = 20p 5.00 above = 30p

In one column I have a price (say £1.50) and in another I want the value to
be whichever applies to the price in the first column (in this case 20p).
Is this possible. I think it is the IF function. The auto calculating cell
needs to look to another cell for a value based on the initial price. If I
alter the fees say from 10p to 15p then this would need to update the auto
calculating cell.

Hopefully I've explained myself (?)

Thanks

Probably a VLOOKUP formula would let you more easily alter your fees, in the
future.

For now set up a two column table named 'tbl'

0 10p
1 20p
5 30p

Then use the formula:
=VLOOKUP(price,tbl,2)

(price and tbl can be replaced by cell references)




--ron

 

[Andy Roberts]

Thanks Ron

I may be wrong here but will your suggestion only work with specific prices
i.e. if the cost is £1 it will return X etc. What if the price is 75p?

What I want to do it say that any price less than £1 is X, between £1.01 and
£5.00 is Y and above £5.00 is Z.

Thanks

 

[Andy Roberts]

Thanks Ron

I may be wrong here but will your suggestion only work with specific prices
i.e. if the cost is £1 it will return X etc. What if the price is 75p?

What I want to do it say that any price less than £1 is X, between £1.01 and
£5.00 is Y and above £5.00 is Z.

Thanks

 

[Ron Rosenfeld]

Thanks Ron

I may be wrong here but will your suggestion only work with specific prices
No

i.e. if the cost is £1 it will return X etc. What if the price is 75p?

If the value of "price" is less than 1, the formula will return 10p

What I want to do it say that any price less than £1 is X, between £1.01 and
£5.00 is Y and above £5.00 is Z.

You may have to adjust the break points a bit in the table. And you will need
to decide what you want to do with your undefined numbers. You don't state
what you want if price is equal to £1, for example.

Thanks

If you *try* the formula, I believe it will do exactly what you describe. You
should also look at HELP for VLOOKUP. You may want to change the 0 to 0.01


--ron