Formula???

[Chris Negron]

I need a formula to be based on the numbers below. Here is what I'm trying
to achieve: If x is >= 400 and <500, and <50%, then return the value 25.
How would I word my formula, and also that was the first line, I need to
reflect all ten lines at one time. I use a this type of format to create
bonuses. HELP!!!!!
400 50% 25
500 50% 50
500 30% 60
600 50% 70
600 30% 75
700 50% 80
700 30% 85
800 50% 90
800 30% 95
900 50% 100

 

[Nastech]

=IF(AND(B5>400,B5<500,C5<50),25,"")

 

[stew]

Dear Chris

This works up 700 30%. It is long winded and I am sure somebody out
there can improve on it. What mistifys me is why I cannot extend it passed
700 30%

=IF(AND(B170>=400,B170<500,C170<=50),25,IF(AND(B170>=500,B170<600,C170>=50),50,IF(AND(B170>=500,B170<600,C170<=30),60,IF(AND(B170>=600,B170<700,C170=50%),70,IF(AND(B170>=600,B170<700,C170<=30%),75,IF(AND(B170>=700,B170<800,C170>=50%),80,IF(AND(B170>=700,B170<800,C170>=30%),85,"")))))))

 

[Sandy Mann]

The reason that you con't extend your formula is that you are hitting the 7
nested function limit in all versions of XL before 2007.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

With 800 in say G1 and 30% in H1 and the table that you gave in your post in
J1:L10 then

=INDEX(L1:L10,MATCH(1,(J1:J10=G1)*(K1:K10=H1),0))

will return 95 when array entered by pressing and holding Ctrl & Shift while
you press Enter.

At work I used to write array formulas as:

=INDEX(L1:L10,MATCH(1,(J1:J10=G1)*(K1:K10=H1),0))+N("Array enter this
formula")

To remind people to array enter them and stop them coming back saying that
*MY* formula had stopped working.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[stew]

HI again Sandy

Thanks. I now understand. However if chris has got Excel 2007 would the
extended formula work to cover his chart?

best

Stew

 

[stew]

Dear Sandy

1.Will this alternative pick o.ut the fact that the % is Variable

2. How would you write this in an array Formula

Best

Stewart

 

[Sandy Mann]

Thanks. I now understand. However if chris has got Excel 2007 would the
extended formula work to cover his chart?

Yes it would. I believe that the limit in XL2007 is 64 nested functions but
the thought of 64 *nested* functions in one formula fill me with dread.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Dear Sandy

1.Will this alternative pick o.ut the fact that the % is Variable

2. How would you write this in an array Formula

I see what you mean - I did not read the Op's post carefully enough and
justlooked at the table.

However, re-reading the post I do not understand the Op's requirements. If
the % figure is variable then what should be the return for
500 & 35% ?

Does the:

500 50% 50
500 30% 60

Mean over 50% and under 30% or between 30 and 50% ? And what happens outside
of these ranges?

Perhaps the OP will come back and tell us.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk