I have a complex formula where I need to build in a MAX function

[Nicole Schmidt]

See this formula, the > function is not working in the spreadsheet. Another
alternative would be to insert a max function - how do I do this?

=SUM((C14-C13)/(L13-J13)*(L12-J12)+J12)*OR(IF(C14<0.6,0,(SUM(((C14-C13)/L13-J13))*(L12-J12)+J12)*(L12-J12)+J12))*OR(IF(C14>0.9,0.9,(SUM(((C14-C13)/L13-J13))*(L12-J12)+J12)*(L12-J12)+J12))

Help!

 

[Bob Phillips]

What is that formula meant to be doing, it looks hugely over-complicated.

 

[Bernie Deitrick]

Nicole,

Your formula uses SUM without needing to. This

SUM((C14-C13)/(L13-J13)*(L12-J12)+J12)
is the same as
((C14-C13)/(L13-J13)*(L12-J12)+J12)

and

SUM(((C14-C13)/L13-J13))*(L12-J12)+J12)*(L12-J12)+J12))
is the same as

(((C14-C13)/L13-J13))*(L12-J12)+J12)*(L12-J12)+J12))


Also, the first time you use

(C14-C13)/(L13-J13)

and then later you use

((C14-C13)/L13-J13)

which will not return the same results - which you actually need should be
determined by you.

OR functions return TRUE or FALSE, so you are not multiplying by a number
but by a Boolean.

It would be better to describe what you _need_ rather than what you _have_
since it appears that what you _have_ is ....

HTH,
Bernie
MS Excel MVP

 

[Joe User]

See this formula, the > function is not working in the spreadsheet.
Another alternative would be to insert a max function - how do I do this?
=SUM((C14-C13)/(L13-J13)*(L12-J12)+J12)
*OR(IF(C14<0.6,0,(SUM(((C14-C13)/L13-J13))*(L12-J12)+J12)*(L12-J12)+J12))
*OR(IF(C14>0.9,0.9,(SUM(((C14-C13)/L13-J13))*(L12-J12)+J12)*(L12-J12)+J12))

Before we explore MAX v. ">", I suspect your formula is simply wrong for
whatever it is you are trying to compute. At the very least, your use of
OR() is nonsensical, and your use of SUM() is superfluous.

At least in part, noting that the "false value" is the same for both IF()
expressions, I suspect you are trying to write:

IF(C14<0.6, 0, IF(C14>0.9, 0.9,
( (C14-C13)/(L13-J13)*(L12-J12)+J12 )*(L12-J12) + J12 ))

Note: I added parentheses around L13-J13. I suspect that is your
intention, based on the structure of the first SUM() expression. If my
suspicion is wrong (only you would know), change (C14-C13)/(L13-J13) to
((C14-C13)/L13-J13) to match your original second SUM() expression.

Putting that together with the first SUM() expression, you might write:

=( (C14-C13)/(L13-J13)*(L12-J12) + J12 )
*IF(C14<0.6, 0, IF(C14>0.9, 0.9,
( (C14-C13)/(L13-J13)*(L12-J12)+J12 )*(L12-J12) + J12 ))

But I am suspicious of the correctness of the formula, in part because of
the similarities, yet slight differences between some of the subexpressions.
I cannot really say, of course, because I have no idea what this formula is
trying to compute. If the formula is still not working for you and you want
some help, I suggest that you provide an English and/or algebraic explanation
of your intention.

If the formula does indeed compute what you intend it to, I would make the
following ordering change for clarity:

=( (L12-J12)*(C14-C13)/(L13-J13) + J12 )
*IF(C14<0.6, 0, IF(C14>0.9, 0.9,
(L12-J12)*( (L12-J12)*(C14-C13)/(L13-J13) + J12 ) + J12 ))

But that reordering might also help you under my suspicions and perhaps to
see any errors in your expression of the computation. To that end, the
following "unfurling" of the expressions might also help. The above is
equivalent to:

=IF(C14<0.6, 0,
IF(C14>0.9, 0.9 * ( (L12-J12)*(C14-C13)/(L13-J13) + J12 ),
( (L12-J12)*(C14-C13)/(L13-J13) + J12 )
* ( (L12-J12) * ( (L12-J12)*(C14-C13)/(L13-J13) + J12 ) + J12 ) ))

If that formula does what you intended, I am not suggesting that you use the
"unfurled" version. It is just a tool to help you see if the behavior of the
formula is what you intended.

As for MAX v. ">", as written, I see no opportunity to use MAX. But I
cannot be sure without knowing the relative values C3, L12:L13 and J12:J13
when C14<0.6 and C14>0.9. I would leave things as-is until you are sure the
formula is computing what you intend it to.

HTH. Please post a follow-up to let us know if we are helping you get any
closer to solution that behaves as you intended.

 

[Bernd P]

Hello Nickie,

Is it by any chance sort of an interpolation you intend to achieve?

Something like
http://sulprobil.com/html/minimax_interpolation.html
?

Happy Easter,
Bernd