Very Complicated Formula - I need Your help Excel Geniuses!!!!

[pervis2k]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Would any of you Excel wizards be able to write a formula to carry out this very complicated scenario please :<o></o>
<o></o>
When cell A3 does not equal "NVQ" :<o></o>

• If the month and year value in cell F3 is less than that in the date value in cell C3 or is greater than the date value in cell D3 return the value "No Payment Expected".<o></o>
• If the month and year value in cell F3 is greater than that in the date value within C3 but equal to or less than the month within cell D3 divide the value in cell B3 by 1+cell E3 (for instance if the cell value in E3 = 5 then I need the formula to divide by 6).<o></o>
• If the month and year value in cell F3 is equal to the month and year in the date within cell C3 divide the value in cell B3 by 1+cell E3 and then multiply by 2.<o></o>

When cell A3 equals "NVQ" :<o></o>

• If the month and year value in cell F3 is less than that in the date value in cell C3 or is greater than the date value in cell D3 return the value "No Payment Expected".<o></o>
• If the month and year value in cell F3 is greater than that in the date value within cell C3 but is less than the month in the date within cell D3 divide 75% of the value in cell B3 by 1+cell E3.<o></o>
• If the month and year value in cell F3 is equal to that in the date value within C3 divide 75% of the value in cell B3 by 1+cell E3 and then multiply by 2.<o></o>
• If the month and year value in F3 is equal to that in the date value within D3 divide 75% of the value in cell B3 by 1+cell E3 and then add on 25% of the value of cell B3.<o></o>

To me this is extremely complicated and if anybody could create a solution for me you deserve a medal and I would be very impressed and grateful.<o></o>
<o></o>
Thank you in advance for your help.<o></o>
<o></o>
Pervis2k<o></o>

 

[Gerald Higgins]

What version of Excel are you using ? If you're using 2003 or earlier, you might have a problem because of the limit on 7 IF statements, but I think this limit does not apply in 2007 and later.

 

[pervis2k]

Hi,

Yes I am using Excel 2003.

Thanks

Pervis2k

 

[konew1]

You dont say what happens if this is false
If the month and year value in cell F3 is equal to the month and year in the date within cell C3 divide the value in cell B3 by 1+cell E3 and then multiply by 2

 

[Andrew Poulsom]

Can F3 be equal to both C3 and D3? If so which takes precedence?

 

[pervis2k]

No F3 will never be equal to both C3 and D3 at the same time. C3 is a start date and D3 is the end date.

I hope that makes this easier.

 

[Peter_SSs]

If I have understood correctly, I *think* the formula in G3 does what you want. If you would allow a few helper cells like I have in I5:K5, then the formula shortens quite a bit to what I have in G5.

If this does not produce the correct results in all circumstances, then please give a few examples of ...

1. What is in cells A3:F3
2. What result my formula produces for those values
3. What result you expect for those values (and any explanation you can add as to why that is the result)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
2
3	NVQ	12	16/04/2007	25/09/2007	5	28/06/2007	1.5
4
5	NVQ	12	16/04/2007	25/09/2007	5	28/06/2007	1.5		200704	200709	200706
6
pervis2k