please help, result should meet two conditions

**IvicaOS** · 12-10-2009, 04:37 AM

Hi Guys,
I guess for most of you this problem of mine will be a breeze, but it’s impenetrable for me!

Please find attached the Excel 2003 table, everything should stay the same, one should only enter values in cells C4, C5 and C6.

I need formula for the “RESULT”, formula for the C10 cell.

Basically it should be the result from the E9 cell, but that result needs to be modified to meet two conditions, if they occur:

1. The RESULT can never be greater than the SUM in C7 (rounded to a greater hundred, like in E9)

2. The RESULT can never be greater than 30.000,00 (thirty thousand)

Please note how the value in the “Bonus” cells change everything.

I’ve tried many “IF” variations, but they seem to work only in some cases; in some other they return wrong value, value that does not meet the two conditions above.

Can you help me?
Thanks!

**darkyam** · 12-10-2009, 06:07 AM

How about: =MIN(SUM(C8:E8),CEILING(C7,100),30000)

**IvicaOS** · 12-10-2009, 06:17 AM

Originally Posted by darkyam

How about: =MIN(SUM(C8:E8),CEILING(C7,100),30000)

Nope!

It'doesn't work, thanks for the try though!

**darkyam** · 12-10-2009, 06:30 AM

You have to be more specific. My formula meets your stated conditions:
1. Sums C8:E8 (A small change to the formula: =MIN(E9,CEILING(C7,100),30000) will now use the rounded up value of E9, should have done that at first)
2. Won't be more than 30,000
3. Won't be more than C7 rounded up.

Please give an example where my formula does not meet your expectations and what you think the results should be.

**darkyam** · 12-10-2009, 06:30 AM

*Edit: Ignore this post. Connection issues caused double-post.

**IvicaOS** · 12-10-2009, 06:39 AM

Originally Posted by darkyam

You have to be more specific. My formula meets your stated conditions:
1. Sums C8:E8 (A small change to the formula: =MIN(E9,CEILING(C7,100),30000) will now use the rounded up value of E9, should have done that at first)
2. Won't be more than 30,000
3. Won't be more than C7 rounded up.

Please give an example where my formula does not meet your expectations and what you think the results should be.

It seems that it's working now, but I will test it some more and let you know!

First formula that you gave didn't round the result, unless result was 30000, and I've had to change "," to ";".

I'll go and play now and will let you know!

Thanks man!

**IvicaOS** · 12-10-2009, 07:09 AM

YES!
THAT'S IT! IT WORKS!

My problem was that I didn't know about MIN function, trying hard to combine multiple IF's, going mad already!

Thanks darkyam, you are truly a Valued Forum Contributor!