WRONG RESULT

[Guest]

I'm still struggling with the below formula, the problem
is that the value of I125 is greater than K27+L31-J27 and
therefore the result is coming out to 0, what I want for
that portion of the formulas is if the result is greater
than I125, give me I125's value not 0, if the result is
less than I125, give me that result, can this be done?

=IF(AND(K16>J16,K27+L31>J27,K27+L31-J27<=I125),MAX(MIN
(K27+L31-J27,K16-K27+L31),0),0)

 

[Don Guillett]

a simplified version
=if(formula>i125,i125,formula)

 

[Laura C]

Hi there, not entirely sure if I'm with you, cos I don't
understand why you have MAX , but I take it you want
whichever is the minimum of either K27+L31-J27 or K16-
K27+L31?? Unless the result of K27+L31-J27 was higher than
I125. If so does this work?

=IF(AND(K16>J16,K27+L31>J27,K27+L31-J27<=I125),(MIN
(K27+L31-J27,K16-K27+L31)),I125)

Cheers
Laura C

 

[Guest]

Thanks for your response Don, it's very much appreciated,
not sure it will work, but will give it a try.

 

[Guest]

Ms. Laura

You're a genius! Pretty sad I don't know what I want and
you do Got one other question, if you could assist
again.

I have 15 worksheets for this formula, if I get to the max
of the value of I125, the result in the rest of the
worksheets should be zero. So if I use the following
formula in the next worksheet, do you think it will work?
FYI, k17 is the value of i125, so in each subsequent
formula, I would want to keep deducting from i125,
correct? I I lost you, it's not you, believe me, it's
me!

=IF(AND(O16>N16,O27+P31>N27,O27+P31-N27<=I125),(MIN
(O27+P31-N27,O16-O27+P31)),K17-I125)

 

[Laura C]

Hi again

I'm not sure without knowing more, because when do you
know that you have reached the max value? Where has K17
come from? Is it the result that you just calculated?

And also do you mean worksheets? As in sheet1, sheet2 ??
If so I'm supposing that those references are in your
formulas? Or do you just mean the formula is repeated for
15 sets of data in one sheet?

If you can have another formula referencing all 15 i.e.
Say for example you are placing the value of the formula
in cell E1 then in another cell put

=IF(E1=MAX($E$2,$G$2,$I$2,$K$2),E1,0)

put within the brackets all 15 cells which contain the
previously calculated values and copy this formula 15
times and this will only display the max from the
calculated values else it will display a zero. The interim
calculated value can then be hidden. This may work?

 

[Guest]

Hi Laura
What would I do without you, you understand my sick
brain In answer to your first question, yes, K17 is
the result of my (your) first formula which is:

IF(AND(K16>J16,K27+L31>J27,K27+L31-J27<=I125),(MINthe result of this formula and it is MAXED at this point
so I want all other 14 results(i.e.O17,S17, etc.)

In answer to your second question, I lost it when I said
worksheets, I meant what you said, the formula is repeated
for 15 sets of data in one sheet, thus the second formula
would be WITHOUT YOUR NEW ADVICE: PLEASE NOTE, this
forumla sits in cell 017:

=IF(AND(O16>N16,O27+P31>N27,O27+P31-N27<=I125),(MIN
(O27+P31-N27,O16-O27+P31)),I125)

If I apply your new advice, which is:
=IF(E1=MAX($E$2,$G$2,$I$2,$K$2),E1,0)

I'm not sure what the second formula would be, what I'm
trying to do is if K17 is the max of I125, then 0,
otherwise

=IF(AND(O16>N16,O27+P31>N27,O27+P31-N27<=I125),(MIN
(O27+P31-N27,O16-O27+P31)),I125)

I think I've maxed myself out on this, and I don't want to
do the same to you. Thank you so much for all of your
assistance, maybe I'm trying to achieve the impossible?

 

[Laura C]

Okay it's long winded but it might work, it's the only
thing I can think of without having to use programming.

In cell K17 put
=IF(AND(K16>J16,K27+L31>J27,K27+L31-J27<=I125),(MIN
(K27+L31-J27,K16-K27+L31)),I125)

In cell O17 put
=IF(K17=I125,0,IF(AND(O16>N16,O27+P31>N27,O27+P31-
N27<=I125),(MIN(O27+P31-N27,O16-O27+P31)),I125))
(this is saying if K17 is equal to I125 (i.e. the max has
been found) then put zero, else use the formula)

In cell S17 (I'm guessing at the sequence) put
=IF(OR(K17=I125,O17=I125),0,IF(AND
(S16>R16,S27+T31>R27,S27+T31-R27<=I125),(MIN(S27+T31-
R27,S16-S27+T31)),I125))
(this is saying if K17 or O17 (i.e. the max has been
found) is equal to I125 put zero, else use the formula to
find a value)

and so on for the 15 but adding the next cell to the OR
sequence i.e after O17=I125 you would put <,S17=I125> and
so on as you move along the cells.

Hope this helps and makes sense

Laura

 

[Guest]

Laura,

Sorry for the long delay in respondig, I was off Friday.
I don't know how to thank you, not only does it make
sense, it works and it works well. I truly don't know how
to thank you but want you to know that I am MOST
grateful Thank you!

 

[Guest]

I thought it was ok, but when I get to the third sheet--
w17, put the forumula in with changes, it gives me the
result of W27+X31-V27---PROBLEM is that now, k17+017+s17+
W17 total more than I125 and I125 is the max! I'm
thinking enough of this, you've spent more than enough
time, I'll give it a shot, but once again, I think you
have me 3/4 of the way there and I Appreciate it.