| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to put a cap on an amount?
Hi Everyone
The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
Try this:
=MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
Thanks, Biff. As has happened several times in the past, you have provided
the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
Why MIN and LOOKUP?
LOOKUP is doing the exact same thing as the nested IFs but is a few keystrokes shorter. Adding the +18 to every result of the nested IFs is redundant since you just need to add it once. You wanted to cap the result at 500. Suppose B5 contained 65000. 65000 is 6000 so: 65000*0.009+18 = 603 So we use MIN to cap the result at 500: =MIN(500,603) = 500 If B5 contained 50000: 50000 is 6000 so: 50000*0.009+18 = 468 =MIN(500,468) = 468 -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks, Biff. As has happened several times in the past, you have provided the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
Thanks! That was a very good explanation. Hopefully, I'll be able to work
other solutions with it in the future. -- smither fan "T. Valko" wrote: Why MIN and LOOKUP? LOOKUP is doing the exact same thing as the nested IFs but is a few keystrokes shorter. Adding the +18 to every result of the nested IFs is redundant since you just need to add it once. You wanted to cap the result at 500. Suppose B5 contained 65000. 65000 is 6000 so: 65000*0.009+18 = 603 So we use MIN to cap the result at 500: =MIN(500,603) = 500 If B5 contained 50000: 50000 is 6000 so: 50000*0.009+18 = 468 =MIN(500,468) = 468 -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks, Biff. As has happened several times in the past, you have provided the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks! That was a very good explanation. Hopefully, I'll be able to work other solutions with it in the future. -- smither fan "T. Valko" wrote: Why MIN and LOOKUP? LOOKUP is doing the exact same thing as the nested IFs but is a few keystrokes shorter. Adding the +18 to every result of the nested IFs is redundant since you just need to add it once. You wanted to cap the result at 500. Suppose B5 contained 65000. 65000 is 6000 so: 65000*0.009+18 = 603 So we use MIN to cap the result at 500: =MIN(500,603) = 500 If B5 contained 50000: 50000 is 6000 so: 50000*0.009+18 = 468 =MIN(500,468) = 468 -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks, Biff. As has happened several times in the past, you have provided the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
Hi again, Biff
Sorry to bother again so soon, but I was wondering if you could tell me how to exclude the $18 from just the "6,000 or less" part, as it seems I misunderstood the requirements? Thanks again -- smither fan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks! That was a very good explanation. Hopefully, I'll be able to work other solutions with it in the future. -- smither fan "T. Valko" wrote: Why MIN and LOOKUP? LOOKUP is doing the exact same thing as the nested IFs but is a few keystrokes shorter. Adding the +18 to every result of the nested IFs is redundant since you just need to add it once. You wanted to cap the result at 500. Suppose B5 contained 65000. 65000 is 6000 so: 65000*0.009+18 = 603 So we use MIN to cap the result at 500: =MIN(500,603) = 500 If B5 contained 50000: 50000 is 6000 so: 50000*0.009+18 = 468 =MIN(500,468) = 468 -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks, Biff. As has happened several times in the past, you have provided the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
Ok, let's go back to the nested IFs:
=MIN(500,IF(B5<0.01,0,IF(B5<=6000,B5*0.012,B5*0.00 9+18))) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi again, Biff Sorry to bother again so soon, but I was wondering if you could tell me how to exclude the $18 from just the "6,000 or less" part, as it seems I misunderstood the requirements? Thanks again -- smither fan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks! That was a very good explanation. Hopefully, I'll be able to work other solutions with it in the future. -- smither fan "T. Valko" wrote: Why MIN and LOOKUP? LOOKUP is doing the exact same thing as the nested IFs but is a few keystrokes shorter. Adding the +18 to every result of the nested IFs is redundant since you just need to add it once. You wanted to cap the result at 500. Suppose B5 contained 65000. 65000 is 6000 so: 65000*0.009+18 = 603 So we use MIN to cap the result at 500: =MIN(500,603) = 500 If B5 contained 50000: 50000 is 6000 so: 50000*0.009+18 = 468 =MIN(500,468) = 468 -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks, Biff. As has happened several times in the past, you have provided the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
That works great! Thanks again.
Have a good week... -- smither fan "T. Valko" wrote: Ok, let's go back to the nested IFs: =MIN(500,IF(B5<0.01,0,IF(B5<=6000,B5*0.012,B5*0.00 9+18))) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi again, Biff Sorry to bother again so soon, but I was wondering if you could tell me how to exclude the $18 from just the "6,000 or less" part, as it seems I misunderstood the requirements? Thanks again -- smither fan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks! That was a very good explanation. Hopefully, I'll be able to work other solutions with it in the future. -- smither fan "T. Valko" wrote: Why MIN and LOOKUP? LOOKUP is doing the exact same thing as the nested IFs but is a few keystrokes shorter. Adding the +18 to every result of the nested IFs is redundant since you just need to add it once. You wanted to cap the result at 500. Suppose B5 contained 65000. 65000 is 6000 so: 65000*0.009+18 = 603 So we use MIN to cap the result at 500: =MIN(500,603) = 500 If B5 contained 50000: 50000 is 6000 so: 50000*0.009+18 = 468 =MIN(500,468) = 468 -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks, Biff. As has happened several times in the past, you have provided the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to put a cap on an amount?
You're welcome!
-- Biff Microsoft Excel MVP "Ross" wrote in message ... That works great! Thanks again. Have a good week... -- smither fan "T. Valko" wrote: Ok, let's go back to the nested IFs: =MIN(500,IF(B5<0.01,0,IF(B5<=6000,B5*0.012,B5*0.00 9+18))) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi again, Biff Sorry to bother again so soon, but I was wondering if you could tell me how to exclude the $18 from just the "6,000 or less" part, as it seems I misunderstood the requirements? Thanks again -- smither fan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks! That was a very good explanation. Hopefully, I'll be able to work other solutions with it in the future. -- smither fan "T. Valko" wrote: Why MIN and LOOKUP? LOOKUP is doing the exact same thing as the nested IFs but is a few keystrokes shorter. Adding the +18 to every result of the nested IFs is redundant since you just need to add it once. You wanted to cap the result at 500. Suppose B5 contained 65000. 65000 is 6000 so: 65000*0.009+18 = 603 So we use MIN to cap the result at 500: =MIN(500,603) = 500 If B5 contained 50000: 50000 is 6000 so: 50000*0.009+18 = 468 =MIN(500,468) = 468 -- Biff Microsoft Excel MVP "Ross" wrote in message ... Thanks, Biff. As has happened several times in the past, you have provided the perfect solution! Could I prevail on you to explain it for me? Why MIN and LOOKUP? Have a good weekend. -- smither fan "T. Valko" wrote: Try this: =MIN(500,IF(B5<0.01,0,B5*LOOKUP(B5,{0;6001},{0.012 ;0.009})+18)) -- Biff Microsoft Excel MVP "Ross" wrote in message ... Hi Everyone The formula that I already have is: =IF(B5<0.01,0,IF(B5<6001,(B5*0.012)+18,IF(B56000, (B5*0.009)+18,0))) I need to add something to cap the result at 500. Can someone help? Thanks... -- smither fan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to change amount in figure to amount in words? | Excel Worksheet Functions | |||
| how to convert the amount in currency into the amount in words? | Excel Discussion (Misc queries) | |||
| Formula for amount owing subtract amount paid | Excel Worksheet Functions | |||
| How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
| How do I calculate Amount of Sales Tax from Total Amount? | Excel Worksheet Functions | |||
Linear Mode
