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

pervis2k

New Member
Joined
Aug 13, 2010
Messages
12
<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Would any of you Excel wizards be able to write a formula to carry out this very complicated scenario please :<o:p></o:p>
<o:p></o:p>
When cell A3 does not equal "NVQ" :<o:p></o:p>
  • 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:p></o:p>
  • 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:p></o:p>
  • 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:p></o:p>
When cell A3 equals "NVQ" :<o:p></o:p>
  • 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:p></o:p>
  • 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:p></o:p>
  • 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:p></o:p>
  • 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:p></o:p>
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:p></o:p>
<o:p></o:p>
Thank you in advance for your help.<o:p></o:p>
<o:p></o:p>
Pervis2k<o:p></o:p>
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
 

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

pervis2k

New Member
Joined
Aug 13, 2010
Messages
12
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
2
3NVQ1216/04/200725/09/2007528/06/20071.5
4
5NVQ1216/04/200725/09/2007528/06/20071.5200704200709200706
6
pervis2k
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top