Returning Desired Results

[Wavmaster]

I need to set a maximum amount on several input values in a formula.

=(MIN(1))+IF(E11>C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5>=H11,H11+F15+G15-500)

This formula suits my purpose until the value of C11 exceeds the value of
E11/5 after C11 exceeds 100.

The Maximum value for the target cell is 500 plus F15 plus G15

example: when C11=496 and E11=2483 and F15=2 and G15=8 the result equals 510
(this works when C11= any whole number from 101 up unless it is greater than
E11/5)
When the value of C11= 497 or greater and the value of E11, F15 and G15
remain constant the result is 2945 which is the sum of E11, F15 x 2 and G15.
Once the value of E11 exceeds 5 x C11 the result returns to the desired result

How can I elimiate this occurrance when C11 exceeds E11/5?

 

[Sean Timmons]

What value is in H11? That's your IF statement where E11>C11*5 (Also known as
C11 less than E11/5)

 

[wavmaster]

H11 reflects the allowed useable value for C11*5 at a given time. it is
always a multiple of 5. It relates to E11 in as much as E11 can be any
number, H11 can be multiples of 5 up to 500. Formula is
=(MIN(C11,100)*5)+IF(C11*5>500,500-500)

 

[wavmaster]

H11 reflects the allowed useable value for C11*5 at a given time. it is
always a multiple of 5. It relates to E11 in as much as E11 can be any
number, H11 can be multiples of 5 up to 500. Formula is
=(MIN(C11,100)*5)+IF(C11*5>500,500-500)

 

[Sean Timmons]

so, in the example you describe at bottom, C11 is greater than 100, thus the
formula here will return 500.

Not sure why the 500-500, as this is the same as just putting 0 in there...

Not sure about MIN(1)? That can just be 1...

Are you saying you merely want the result of the formula to max out at
500+F15+G15?

=MIN(500+F15+G15,(1+IF(E11>C11*5,H11-1)+IF(E11<C11*5+1,E11-1+F15)+IF(C11*5>=H11,H11+F15+G15-500)))

 

[wavmaster]

That works when C11 is greater than 100 but not when C11 is less than 100