Complex IF STATEMENT

[CR3]

I have an urgent question about creating a complex IF statement in Excel.

I have the following data:

A Year to Date(YTD) entry field with a percentage of 8% in it. (E30)

A Weight entry field with a percentage of 5% in it. (F30)

I then have Five fields that are used as qualifiers for the Year to Date
field:
The first field has an entry value that reads <0%. (H30)
The second has a value of .1%-2%. (I30)
The third= 2.1%-5% (J30)
The fourth= 5.1%-9% (K30)
and the Fifth= >9.1% (L30)

I need an IF statement that says IF YTD(E30) falls into the criteria of the
first qualifier field(H30), multiply the weight value(F30) by .5. Or, if
YTD(E30) falls into the criteria for the second qualifier field(I30),
multiply the weight(F30) by .75. And the same for the remaining three
qualifying fields. (the corresponding rates to be multiplied by are 1, 1.25,
and 1.5)


Thank you for any help you can give.

 

[Bob Phillips]

Change the data slight to

H30: -100%
I30: 0%
J30: 2%
K30: 5%
L30: 9%

and then use

=F30*LOOKUP(E30,H30:L30,{0.5,0.75,1,1.25,1.5})

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[CR3]

Is this taking into account that I only want the corresponding multiplication
weight if it falls within the interval, i.e. 5.1%-9%

 

[Bob Phillips]

Yes. Try it and see.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)