Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF Function (I Think!)
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 |
#2
|
|||
|
|||
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/...soft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Andy Roberts" wrote in message ... 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 |
#3
|
|||
|
|||
On Sat, 25 Jun 2005 15:44:08 +0000 (UTC), "Andy Roberts"
wrote: 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 |
#4
|
|||
|
|||
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" wrote in message ... On Sat, 25 Jun 2005 15:44:08 +0000 (UTC), "Andy Roberts" wrote: 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 |
#5
|
|||
|
|||
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" wrote in message ... On Sat, 25 Jun 2005 15:44:08 +0000 (UTC), "Andy Roberts" wrote: 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 |
#6
|
|||
|
|||
On Mon, 27 Jun 2005 10:18:16 +0000 (UTC), "Andy Roberts"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |