Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF STATEMENT
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF STATEMENT
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF STATEMENT
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" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex IF STATEMENT
Yes. Try it and see.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CR3" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with complex If statement | Excel Worksheet Functions | |||
Yet another extremely complex IF statement | Excel Worksheet Functions | |||
complex IF(OR(...AND())) statement | Excel Discussion (Misc queries) | |||
Help with complex average/if statement | Excel Worksheet Functions | |||
rather complex logic statement | Excel Worksheet Functions |