# Thread: Calculate the target value with constraints

1. ## Calculate the target value with constraints

Hello everyone,

I'm having troubles finding an answer for this spécific case. So here is an extraction from my database :
 A B C D E F Site name Income fixed Charges % variable charges Variable charges Result only values between 3 and 9 =B*D =B-C-E Site 1 25 000 22 000 Site 2 17 000 16 000 Site 3 55 000 55 000

 betwen 3% and 9%
Here's the tricky part, if F (result) is <0, the value in D should be by default 3%, but if F is >0, then the D value can grow up to 9% with the condition that F stays positive or at least =0.

Examples:
Site 1:
25 000 - 22 000 = 3 000; assuming that I can apply 9%, 25 000*9% = 2 250 ; the result would be 750. That's OK, keeping the 9% in D.
Site 2:
17 000 - 16 000 = 1 000; assuming that I cand apply 9%, 17 000*9% = 1 530 ; the result would be -530, means the % is too high. But the maximum allowed % would be around 5,88% so my result would get closer to 0 but not negative.
Site 3:
55 000 - 55 000 = 0; there's no choice the minimum 3% should be applied even if the result is negative.

My list contains thousands rows, didn't succeed to do anything with the Solver. Yes, I could do manually a few examples with the help of Goal seeker, but it's impossible to do that for the thousands rows. Also, all the formulas that I've tried are with circular references.

I'm pretty sure this thing can be done in Excel, I just can't figure out how.

Cheers,
Sam

2. try, in cell D3:
=MAX(MIN((B3-C3)/B3,0.09),0.03)
or
=MAX(MIN(1-C3/B3,0.09),0.03)