How to create a formula with multiple conditions and limits

[Alex_m]

Hey,
So the question states: "... as Director of Operations, you are entitled to a bonus of 2% of any positive year-end
balance to a maximum of $10,000 as long as the hotel’s balance is positive in all months."

Essentially I need a formula that will multiply .02 by say (A1) whose product has a maximum limit of 10,000 given that the value of (A1) is greater than zero and that ALL the balances of 12 months are also positive.

Naturally this is a pretty insane function and the internet has brought me no help so far.
Thanks for any help you can give!

Link: http://www.excelforum.com/excel-form...ml#post3997797

[FDibbins]

Hi, welcome to trhe forum

Naturally this is a pretty insane function

Actually, not so, it involves the use of MIN and probably COUNTIF(), but Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Once changed PM me and I will give you the rest of what you want

[FDibbins]

Thanks

Try this...

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2		10000	20000	30000	40000	50000	60000	70000	80000	90000	100000	110000	120000
3
4
5	10000

A5=IF(COUNTIF(B2:M2,">0")=12,MIN(10000,SUM(B2:M2)*0.02),"")

You didnt say which way your data went, so adjust as needed