If Statements

[tropezfn]

Problem
I have the set of data,

Value Fee
A B C

Min Max
£0.00 £4.99 £0.45
£5.00 £9.99 £0.85
£10.00 £99.99 £0.09
£100.00 £250.00 £8.75


I have used Vlookup to build the function.
=IF(VLOOKUP(B12,$A$3:$C$7,3)=$C$5,$C$5*B12,VLOOKUP(B12,$A$3:$C$6,3)).
What I am looking for is that when the value is over 250, it should
divide it by 250 and return the rate appropriately. If it is nil it
should return 0

Can someone help?
Please find attached


+-------------------------------------------------------------------+
|Filename: TropezfnPO Management.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4615 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

Do you mean

=IF(B12=0,0,IF(VLOOKUP(IF(B12>250,B12/250,B12),$A$3:$C$7,3)=$C$5,$C$5*IF(B12

250,B12/250,B12),VLOOKUP(IF(B12>250,B12/250,B12),$A$3:$C$6,3)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Don Guillett]

look in help index for LOOKUP and try this idea.

=LOOKUP($B$12,{0,5,10,100,250;0,0.045,0.85,0.09,8.75})

=IF(B12>250,B12/250,LOOKUP($B$12,{0,5,10,100,250;0,0.045,0.85,0.09,8.75}))

 

[tropezfn]

Phil,

A Friend has helped and this is what I was I looking for

I wanted to capp it at £250 and so if you have £524,
it will divide it by £250 = 2, capp rate, and the remainder £24 applie
the relevant rate.

Answer:
=IF((B15/250)=J15,($C$6*J15),(J15*$C$6)+IF(VLOOKUP((B15-(J15*250)),$A$3:$C$7,3)=$C$5,$C$5*(B15-(J15*250)),VLOOKUP((B15-(J15*250)),$A$3:$C$6,3)))

A B C

Min Max
£0.00 £4.99 £0.45
£5.00 £9.99 £0.85
£10.00 £99.99 8.75%
£100.00 £250.00 £8.75

Thanks

For your help

 

[tropezfn]

Bob,

Can you also assist with this?

I want to create a spreadsheet which will have name range.
The range should be based on the following

Passport Nigeria £13, kid Rates £6
Revalidation Passport £25
Passport India £15 £13, kid Rates £6.

As you type, it should give you the option to select .
and you should give the qty.

Is this possible?

Regards

 

[tropezfn]

Bob,

Can you also assist with this?

I want to create a spreadsheet which will have name range.
The range should be based on the following

Passport Nigeria £13, kid Rates £6
Revalidation Passport £25
Passport India £15 £13, kid Rates £6.

As you type, it should give you the option to select .
and you should give the qty.

Is this possible?

Regards

 

[Bob Phillips]

I would suggest that you use Data Validation to hold all of the options, and
a table of options and prices, and use VLOOKUP to pull back the appropriate
rate. You can find some details on data Validation at See
http://www.contextures.com/xlDataVal01.html, and VLOOKUP at
http://www.contextures.com/xlFunctions02.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)