sum 2 values up to a maximum value

[greegan]

=SUM(IF(B5*D23>2217.6,2217.6,B5*D23)+(IF(B5*D24<786.76,786.76,B5*D24)))

is what I have so far.
I would hope there is a better way to determine the final answer with fewer... functions?
Just to be clear
B5 and D23 are multiplied
if the result is less than 2217.60 then use the result
otherwise max the result at 2217.60

The same with B5 * D23 but the max is 786.76

Thank you as always,

-- g

 

[JackDanIce]

Try:

=MAX(B5*D23,2217.6)+MAX(B5*D24,786.76)

 

[schielrn]

Try:

=min(b5*d23,2217.6)

and

=min(b5*d23,786.76)

Hope that helps.

 

[Gerald Higgins]

Maybe this ?

=MIN(2217.6,B5*D23)+MAX(786.76,B5*D24)

Seems to produce the same results...

 

[Gerald Higgins]

greegan - the three suggestions posted so far all look similar, but they are all subtly different and will, I think, produce different results.

 

[greegan]

greegan - the three suggestions posted so far all look similar, but they are all subtly different and will, I think, produce different results.

The min formulas seem to give me consistant results.
Thank you for the help

-- g

 

[greegan]

This is the final formula in the code I'm trying to write based on what you have given me above.

K2 = (0.15 * (Min(P * C, 2217.6))) + (0.15 * (Min(P * EI, 786.76)))

K2 is the final result
P is 26
C and EI are 31.15 and 13.60 respectively
I seem to be making a mistake using the Min worksheet function within this code as this is giving me a compile error...

"Sub or Function not defined."

C, EI and K2 are all Public currency
P is Public double


I must be missing something very simple?

-- g

 

[greegan]

This is the final formula in the code I'm trying to write based on what you have given me above.

K2 = (0.15 * (Min(P * C, 2217.6))) + (0.15 * (Min(P * EI, 786.76)))

K2 is the final result
P is 26
C and EI are 31.15 and 13.60 respectively
I seem to be making a mistake using the Min worksheet function within this code as this is giving me a compile error...

"Sub or Function not defined."

C, EI and K2 are all Public currency
P is Public double


I must be missing something very simple?

-- g

should i be using the if...then statements for this?

 

[schielrn]

You never said this will be in code, but maybe try:

K2 = (0.15 * (application.Min(P * C, 2217.6))) + (0.15 * (application.Min(P * EI, 786.76)))

or:

K2 = (0.15 * (worksheetfunction.Min(P * C, 2217.6))) + (0.15 * (worksheetfunction.Min(P * EI, 786.76)))

Hope that helps.

 

[greegan]

You never said this will be in code, but maybe try:

K2 = (0.15 * (application.Min(P * C, 2217.6))) + (0.15 * (application.Min(P * EI, 786.76)))

or:

K2 = (0.15 * (worksheetfunction.Min(P * C, 2217.6))) + (0.15 * (worksheetfunction.Min(P * EI, 786.76)))

Hope that helps.

both work great
thank you

-- g