How to make a formula with a max?

[hjensen]

Hi,

I need help to write a formel where there is a cap at 125% of a value of 2500. It should also show the value of ie 5%, 63%, 87% or 105% etc up to 125%. Anyone who can help me with that? ( hope the info mentioned is enough otherwise let me know and i will try to elaborate more).

Thanks

[rajeshturaha]

Plz elaborate, also post a sample file for better understanding

[hjensen]

Hi RT,

I need the formula to show me what the value will be depending on the percentage. 100% is 2500, so what i need is a formula which calculate the value if it is 5%, 60% or 70% of the 2500, if it was 60% the value would then be 1500 but this is not really the issue. The problem is that there is a maximum of 125% and i don't know how to put this restriction in the formula. Could you help me with this?

Thanks.

[rajeshturaha]

What should be the value if the >100%

[hjensen]

since the cap is 125% the max value would be 3125

[rajeshturaha]

Can u plz post a sample file along with the desired results

[hjensen]

I can't really post a sample as the result i want is the formula which is what i need help for (or maybe I don't understand your question)

[rajeshturaha]

Assuming that ur value is in col a, and cap in col b and percentage in col c. In col c

Please Login or Register  to view this content.

[hjensen]

so if value=2500, cap=125%, percentage of value=60% how would you write the formula?

[rajeshturaha]

Try This

Please Login or Register  to view this content.

For more clarification, plz post a sample file (not the actual file) and your desired result, explain how would u get the desired result manually.

[:) Sixthsense :)]

May be something like this...

In A1 Cell
2500

In A2 Cell
125%

In A3 Cell
="[5%="&A1*5%/A2&"] [60%="&A1*60%/A2&"] [70%="&A1*70%/A2&"]"&"] [90%="&A1*90%/A2&"]"&"] [100%="&A1*100%/A2&"]"

[hjensen]

I tried to use your formula but it says error (=IF(B1*A1>3125,0,A1*C1) --> 3125,0,A1*C1)--> it can't recognize the part i took out of the formula.

My problem is to put a cap in the formula of 125%

[rajeshturaha]

so if value=2500, cap=125%, percentage of value=60% how would you write the formula?

Do u want to put formula in Cap col or percentage of value col.
and can u plz explain how would u manualy calculate it, any example

[hjensen]

I would calculate it like this. Two examples: Example 1: 60% (this number will vary depending on other factors) of 2500 = 1500 - example 2: 150% (this number will vary depending on other factors) of 2500 = 3750 but the cap is 125% so I need this restriction in the formula. The percentage of the value always varies.

[rajeshturaha]

Supposing that a1 = value( say)2500 or 3125 or 3725 etc, and b2= cap, then b2, and your 100%=2500

Please Login or Register  to view this content.

this will restrict your cap to go beyond 125%

[hjensen]

Is that how you would write the formula cause when i type in its shows error? Sorry, im a totally excel noob

[rajeshturaha]

Plz see the attachment

[hjensen]

Hi mate,

Just got back from holidays. The attached file is exactly what I am looking for, thanks a lot. I was wondering if the value column is a percentage, how would the formula then be? I tried to change the number with a percentage but it gave me a value of 0 in the cap column.

[rajeshturaha]

In the given attachment post#17 Plz put the percentage where you want and also the desired result.

It is always better to post a attachment with examples rather than long text explanation, to understand the problem better and fast solution. We are also working people who donot have much time to test your text explanation, test it and give solution, we are only trying to help you.