IF Statement help needed

nfroio,

How about if J = I ?

Regards,

Patrick

That does not seem to do it,

with your formula in the above, lets say I put it in Col. K

for Row 1 I get: -$98,907.08 when I want to see $2,126.08
for Row 2 I get: -$13,140.69, the desired result

Basically, with the above, it gives Col. J as the result, regardless of the argument??

actually, wait, its doing what it should, I am just a dumas as it were, there is a flaw in how we are looking at the data, not in the formula, I will get back to this shortly, may need a clarification question... thanks matthewpatrick

Hi nfroio,
If you want the minimum absolute value, then try an array formula like:
=MIN(IF(I2:J2^2=MIN(I2:J2^2),I2:J2,""))        Array formula, so hold the Control + Shift keys down while pressing Enter. Excel should respond by adding curly braces { } surrounding the formula.
Brad

OK, this formula is basically doing what I need it to do, with the exception of TRUE statement, somehow, I need the TRUE statement give the correct SUM, up to 100,000.

Possible? Willing to raise points if this is getting difficult

=IF(I25<100000,SUM(I25+H25),100000-H26)

nfroio,
=IF(I25<100000,MIN(I25+H25,100000),100000-H26)
In the TRUE case, this formula returns the lesser of I25+H25 or 100,000.

Brad

Brad,

Thank you, will give that a go and let you know........

Brad, I am raising the point value, as I know I am getting close, but, keep seeing issues that are affecting my answer, and want to keep this fair for your work.

I need to look at this project a wee closer to back into explaining what I need better before I respond.. will be back shortly

Brad,

OK, the formula is almost there (he says with hopeful abandon).

The only problem that I have, and I hope its something that can be resolved, if the answer is cant be done, well, there you have it, but, here goes:

I have amended the formula a wee, as I asked the wrong way to get to what I need, here is what I have:

=IF(I24<100000,I24,100000-H24)

Basically, this is all good, however, I need to limit the result to 100,000, which means somehow, I need to have the result checked against Column J + Column H, if the result is less than 100,000 then return the value, if the result from the formula is over 100,000 to limit the returned value to 100,000.

I have lotsa points, and can raise this to the max allowable value if needed, this is just making me bounce my head off of my desk..

nfroio,

As an observer to the proceedings - Getting the solution does not really depend on you increasing the points - although no doubt they will be appreciated - it appears to be more about being sure you define what you are really needing.

Why not try to put into words, once again, exactly what you are needing.

Patrick

nfroio,
=MIN(H24+J24,100000)             Returns J24+J24 if it is less than 100,000. Returns 100,000 otherwise.
=MIN(IF(I24<100000,I24,100000-H24),100000)          Returns the IF formula if it is less than 100,000. Returns 100,000 otherwise.

Like patrickab said, getting a complete description of how you want the formula to work is the key to success in this problem. The points on offer are quite generous. I'm thinking if you show some sample numbers and the desired results that would do the trick. You'll want to show all the different possibilities (under and over 100,000 for sure; other possibilities if you think they affect the required formula).

Brad

OK, here's the gist: more examples

           G                 H                     I                    J

1  $3,219.00       $1,092.92        $2,126.08       $2,126.08 OK
2  $75,910.00      $48,510.52      $27,399.48     $27,399.48 OK  
3  $84,211.00      $50,261.60      $33,949.40     $33,949.40 OK
4  $101,456.00      $76,958.90      $24,497.10     $24,497.10 BAD
5  $153,247.00      $85,766.29      $67,480.71     $67,480.71 BAD
6  $400,000.00    $86,859.31      $313,820.69   $$13,140.69 OK

Basically,

Column

G = The Total Amount on Balance (both real and outstanding)
H = The Total Amount of Actual Money Received
I = Outstanding Balance

However, no more than $100,000.00 can actually be paid by contract, regardless of Total Balance (Col. G)

So, for Rows 1, 2, 3, & 6, the amount shown in Column J make sense, as Row's 1,2, & 3 have Balances under $100K amount, so, realisitically, the total between what they paid is known, so, at this point in time, the most they could owe would be the outstanding balance, column I. The formula bears this out well.

For Row 6, the formula also works, as the Outstanding Balance is over $100,000, the most that needs to be paid is the difference between what has actually been paid, and $100,000.

Rows 4 & 5 however, we know that row 5 only has to pay $100,000 of the $153,247 amount on balance, to date, $85,766.29 has been paid (column H), the outstanding balance shows $67,480.71, however, to get to $100,000, only $14,233.71 needs to be paid to fulfill the contract, this is the number that I need to show for this example in Column J

Same basic problem exists for Row 5.

Clear as a Guniess on St. Patricks huh?

HOT DANG!!!!!!!!!!!!

Thank you Brad, I think that did it, let me test a few things, and if it works, will be back to accept, thank you.

Excellent Smithers, Excellent..

Thank you Brad.