Results 1 to 2 of 2

Thread: Calculate the target value with constraints

  1. #1
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    11
    Articles
    0
    Excel Version
    2013

    Question Calculate the target value with constraints



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Thanks in advance for your time !

    Cheers,
    Sam

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,124
    Articles
    0
    Excel Version
    365
    try, in cell D3:
    =MAX(MIN((B3-C3)/B3,0.09),0.03)
    or
    =MAX(MIN(1-C3/B3,0.09),0.03)
    Last edited by p45cal; 2021-12-21 at 07:28 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •