| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statements
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 | +-------------------------------------------------------------------+ -- tropezfn ------------------------------------------------------------------------ tropezfn's Profile: http://www.excelforum.com/member.php...o&userid=33351 View this thread: http://www.excelforum.com/showthread...hreadid=531845 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statements
Do you mean
=IF(B12=0,0,IF(VLOOKUP(IF(B12250,B12/250,B12),$A$3:$C$7,3)=$C$5,$C$5*IF(B12 250,B12/250,B12),VLOOKUP(IF(B12250,B12/250,B12),$A$3:$C$6,3))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "tropezfn" wrote in message ... 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 | +-------------------------------------------------------------------+ -- tropezfn ------------------------------------------------------------------------ tropezfn's Profile: http://www.excelforum.com/member.php...o&userid=33351 View this thread: http://www.excelforum.com/showthread...hreadid=531845 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statements
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))) 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  Bob Phillips Wrote: Do you mean =IF(B12=0,0,IF(VLOOKUP(IF(B12250,B12/250,B12),$A$3:$C$7,3)=$C$5,$C$5*IF(B12 250,B12/250,B12),VLOOKUP(IF(B12250,B12/250,B12),$A$3:$C$6,3))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "tropezfn" wrote in message ... Problem I have the set of data, Value Fee 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 | +-------------------------------------------------------------------+ -- tropezfn ------------------------------------------------------------------------ tropezfn's Profile: http://www.excelforum.com/member.php...o&userid=33351 View this thread: http://www.excelforum.com/showthread...hreadid=531845 -- tropezfn ------------------------------------------------------------------------ tropezfn's Profile: http://www.excelforum.com/member.php...o&userid=33351 View this thread: http://www.excelforum.com/showthread...hreadid=531845 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statements
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 Wrote: Bob, 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))) 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 ------------------------------------------------------------------------ tropezfn's Profile: http://www.excelforum.com/member.php...o&userid=33351 View this thread: http://www.excelforum.com/showthread...hreadid=531845 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statements
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: http://www.excelforum.com/member.php...o&userid=33351 View this thread: http://www.excelforum.com/showthread...hreadid=531845 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statements
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) "tropezfn" wrote in message ... 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 Wrote: Bob, 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))) 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 ------------------------------------------------------------------------ tropezfn's Profile: http://www.excelforum.com/member.php...o&userid=33351 View this thread: http://www.excelforum.com/showthread...hreadid=531845 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating my own user defined function help statements | Excel Worksheet Functions | |||
| UDFunctions and nested If-the-else statements | Excel Worksheet Functions | |||
| Linking two IF statements together | Excel Discussion (Misc queries) | |||
| Logical ELSE statements | Excel Discussion (Misc queries) | |||
| Nested IF statements | Excel Worksheet Functions | |||
Linear Mode
