samdk
New member
Hello everyone,
I'm having troubles finding an answer for this spécific case. So here is an extraction from my database :
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.:confused2:
Thanks in advance for your time !
ray2:
Cheers, :thumb:
Sam
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% |
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.:confused2:
Thanks in advance for your time !
ray2:Cheers, :thumb:
Sam
