Calculating a Formula Subject to a Maximum and Minimum Value

[Josh Thompson]

I am having trouble coming up with a correct formula and I am hoping that someone may be able to help. I am trying to multiply two numbers in two different cells, but have the product subject two a minimum amount that is located in another cell and a maximum amount located in another cell so that the formula returns either the actual product, or the minimum value if the actual product is less than the minimum or the maximum value if the actual product is greater than the maximum. Here is an example.

A B
1 Rate 20%
2 Maximum 75
3 Minimum 40
4
5 Gross Net
6 100 20
7 200 40
8 300 60
9 400 80

Column A rows 6 - 9 are being multiplied by cell B1 - the product is shown in cells B6-B9. I want the values in cells B6-B9 to return the product of the numbers that are being multiplied except if the product is less than the minimum (40 shown in cell B3), then the minimum value of 40 should be returned or if the product is greater than the maximum value (75 shown in cell B2), then the maximum value of 75 should be returned. The results should look like this:

A B
1 Rate 20%
2 Maximum 75
3 Minimum 40
4
5 Gross Net
6 100 40
7 200 40
8 300 60
9 400 75

Can anyone help with this? Thanks in advance!






EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[Luke M]

Formula in B6:

=MIN(B$2,MAX(B$3,A6*B$1))

 

[Josh Thompson]

Great! Thanks a bunch! One other question... What if I needed to make the returned value subject to the min or max only if the product is greater than 0? For example, if cell A10 contained a 0, then when A10 is multiplied by B2 the product would be 0. Under the current formula the minimum is returned. Can I have the formula set up so that the returned value is 0? This would need to happen only if the product is 0 - If the product is greater than 0 but less than the minimum, the minimum would need to be returned.

Also, I am not sure where to click yes - If you could point me in the right direction, I would be happy to do so.



Posted as a reply to:

RE: Calculating a Formula Subject to a Maximum and Minimum Value

Formula in B6

=MIN(B$2,MAX(B$3,A6*B$1)
--
Best Regards

Luke
*Remember to click "yes" if this post helped you!

:

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx