software

Problem
I have the set of data,

ValueFee
A B C

MinMax
?0.00?4.99?0.45
?5.00?9.99?0.85
?10.00?99.99?0.09
?100.00?250.00?8.75I 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: www.excelforum.com/attachment.php?postid=4615 |
-------------------------------------------------------------------

--
tropezfn
------------------------------------------------------------------------
tropezfn's Profile: www.excelforum.com/member.php...oamp;userid=33351
View this thread: www.excelforum.com/showthread...hreadid=531845Do you mean

=IF(B12=0,0,IF(VLOOKUP(IF(B12gt;250,B12/250,B12),$A$3:$C$7,3)=$C$5,$C$5*IF(B12
gt;250,B12/250,B12),VLOOKUP(IF(B12gt;250,B12/250,B12),$A$3:$C$6,3)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;tropezfnquot; gt; wrote in
message ...
gt;
gt; Problem
gt; I have the set of data,
gt;
gt; Value Fee
gt; A B C
gt;
gt; Min Max
gt; ?0.00 ?4.99 ?0.45
gt; ?5.00 ?9.99 ?0.85
gt; ?10.00 ?99.99 ?0.09
gt; ?100.00 ?250.00 ?8.75
gt;
gt;
gt; I have used Vlookup to build the function.
gt; =IF(VLOOKUP(B12,$A$3:$C$7,3)=$C$5,$C$5*B12,VLOOKUP (B12,$A$3:$C$6,3)).
gt; What I am looking for is that when the value is over 250, it should
gt; divide it by 250 and return the rate appropriately. If it is nil it
gt; should return 0
gt;
gt; Can someone help?
gt; Please find attached
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: TropezfnPO Management.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4615 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; tropezfn
gt; ------------------------------------------------------------------------
gt; tropezfn's Profile:
www.excelforum.com/member.php...oamp;userid=33351
gt; View this thread: www.excelforum.com/showthread...hreadid=531845
gt;
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(B12gt;250,B12/250,LOOKUP($B$12,{0,5,10,100,250;0,0.045,0.85,0.09 ,8.75}))
--
Don Guillett
SalesAid Software

quot;tropezfnquot; gt; wrote in
message ...
gt;
gt; Problem
gt; I have the set of data,
gt;
gt; Value Fee
gt; A B C
gt;
gt; Min Max
gt; ?0.00 ?4.99 ?0.45
gt; ?5.00 ?9.99 ?0.85
gt; ?10.00 ?99.99 ?0.09
gt; ?100.00 ?250.00 ?8.75
gt;
gt;
gt; I have used Vlookup to build the function.
gt; =IF(VLOOKUP(B12,$A$3:$C$7,3)=$C$5,$C$5*B12,VLOOKUP (B12,$A$3:$C$6,3)).
gt; What I am looking for is that when the value is over 250, it should
gt; divide it by 250 and return the rate appropriately. If it is nil it
gt; should return 0
gt;
gt; Can someone help?
gt; Please find attached
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: TropezfnPO Management.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4615 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; tropezfn
gt; ------------------------------------------------------------------------
gt; tropezfn's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33351
gt; View this thread: www.excelforum.com/showthread...hreadid=531845
gt;

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 applied
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)))

gt; A B C
gt;
gt; Min Max
gt; ?0.00 ?4.99 ?0.45
gt; ?5.00 ?9.99 ?0.85
gt; ?10.00 ?99.99 8.75%
gt; ?100.00 ?250.00 ?8.75Thanks

For your helpBob Phillips Wrote:
gt; Do you mean
gt;
gt; =IF(B12=0,0,IF(VLOOKUP(IF(B12gt;250,B12/250,B12),$A$3:$C$7,3)=$C$5,$C$5*IF(B12
gt; gt;250,B12/250,B12),VLOOKUP(IF(B12gt;250,B12/250,B12),$A$3:$C$6,3)))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;tropezfnquot; gt;
gt; wrote in
gt; message ...
gt; gt;
gt; gt; Problem
gt; gt; I have the set of data,
gt; gt;
gt; gt; Value Fee
gt;
gt; gt;
gt; gt;
gt; gt; I have used Vlookup to build the function.
gt; gt;
gt; =IF(VLOOKUP(B12,$A$3:$C$7,3)=$C$5,$C$5*B12,VLOOKUP (B12,$A$3:$C$6,3)).
gt; gt; What I am looking for is that when the value is over 250, it should
gt; gt; divide it by 250 and return the rate appropriately. If it is nil it
gt; gt; should return 0
gt; gt;
gt; gt; Can someone help?
gt; gt; Please find attached
gt; gt;
gt; gt;
gt; gt;
gt; -------------------------------------------------------------------
gt; gt; |Filename: TropezfnPO Management.zip
gt; |
gt; gt; |Download: www.excelforum.com/attachment.php?postid=4615
gt; |
gt; gt;
gt; -------------------------------------------------------------------
gt; gt;
gt; gt; --
gt; gt; tropezfn
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; tropezfn's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33351
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=531845
gt; gt;--
tropezfn
------------------------------------------------------------------------
tropezfn's Profile: www.excelforum.com/member.php...oamp;userid=33351
View this thread: www.excelforum.com/showthread...hreadid=531845
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?

Regardstropezfn Wrote:
gt; Bob,
gt;
gt; A Friend has helped and this is what I was I looking for
gt;
gt; I wanted to capp it at ?250 and so if you have ?524,
gt; it will divide it by ?250 = 2, capp rate, and the remainder ?24 applied
gt; the relevant rate.
gt;
gt; Answer:
gt; =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)))
gt;
gt; gt; A B C
gt; gt;
gt; gt; Min Max
gt; gt; ?0.00 ?4.99 ?0.45
gt; gt; ?5.00 ?9.99 ?0.85
gt; gt; ?10.00 ?99.99 8.75%
gt; gt; ?100.00 ?250.00 ?8.75
gt;
gt;
gt; Thanks
gt;
gt; For your help
gt; --
tropezfn
------------------------------------------------------------------------
tropezfn's Profile: www.excelforum.com/member.php...oamp;userid=33351
View this thread: www.excelforum.com/showthread...hreadid=531845
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
------------------------------------------------------------------------
tropezfn's Profile: www.excelforum.com/member.php...oamp;userid=33351
View this thread: www.excelforum.com/showthread...hreadid=531845I 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
www.contextures.com/xlDataVal01.html, and VLOOKUP at
www.contextures.com/xlFunctions02.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;tropezfnquot; gt; wrote in
message ...
gt;
gt; Bob,
gt;
gt; Can you also assist with this?
gt;
gt; I want to create a spreadsheet which will have name range.
gt; The range should be based on the following
gt;
gt; Passport Nigeria ?13, kid Rates ?6
gt; Revalidation Passport ?25
gt; Passport India ?15 ?13, kid Rates ?6.
gt;
gt; As you type, it should give you the option to select .
gt; and you should give the qty.
gt;
gt; Is this possible?
gt;
gt; Regards
gt;
gt;
gt;
gt;
gt;
gt; tropezfn Wrote:
gt; gt; Bob,
gt; gt;
gt; gt; A Friend has helped and this is what I was I looking for
gt; gt;
gt; gt; I wanted to capp it at ?250 and so if you have ?524,
gt; gt; it will divide it by ?250 = 2, capp rate, and the remainder ?24 applied
gt; gt; the relevant rate.
gt; gt;
gt; gt; Answer:
gt; gt;
=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)))
gt; gt;
gt; gt; gt; A B C
gt; gt; gt;
gt; gt; gt; Min Max
gt; gt; gt; ?0.00 ?4.99 ?0.45
gt; gt; gt; ?5.00 ?9.99 ?0.85
gt; gt; gt; ?10.00 ?99.99 8.75%
gt; gt; gt; ?100.00 ?250.00 ?8.75
gt; gt;
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; For your help
gt; gt;
gt;
gt;
gt; --
gt; tropezfn
gt; ------------------------------------------------------------------------
gt; tropezfn's Profile:
www.excelforum.com/member.php...oamp;userid=33351
gt; View this thread: www.excelforum.com/showthread...hreadid=531845
gt;