If formula not going through all if statements to return correct value

[Wanna be excel wiz]

Hi Guys,

I'm trying to run a multiple if statement that checks for the following 3 criteria

Column N contains a date. If this date is less than todays date ($T$1) should return cancel. If column N has a value of "check" it means it hasn't matched a date from a separate formula and should return "No Match" so the user knows it didn't match to other spreadsheet.

Column Q also contains a date, but this is when a payment on an account was last made, i'm expecting all these payments to be made during 2012

Column R is the payee, and should only be "ok" if its "Maxxia"

=IF(N93<$T$1,"Cancel",IF(N93="Check","No Match",IF(--(YEAR(Q93)=2012),"ok",IF(R93="Maxxia","Ok","cancel"))))

However, my formula is returning the comment "OK", When the date in column N is greater than today (ok) and column R is NOT "Maxxia". This result should infact be "Cancel" as the final section of the formula says if value is false.

Any help?

Thanks

[:) Sixthsense :)]

Your formula seems to be ok.... I believe you might be doing something wrong with the input value

[Wanna be excel wiz]

I just found another error

It has put "Ok" when the last payment wasn't made in 2012...it was made in 2011

Sorry i'm not sure i follow with input value?

[:) Sixthsense :)]

It's better can you please attach a sample workbook for better understanding?

Input Value is meant for the way you enter the data in cell

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Wanna be excel wiz]

It's better can you please attach a sample workbook for better understanding?

Input Value is meant for the way you enter the data in cell

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

I have attached a small sample of the data with the errors highlighted

Thank you for your help

[:) Sixthsense :)]

The formula is doing the job perfectly

D8 cell formula
=IF(A8<$D$1,"Cancel",IF(A8="Check","No Match",IF((YEAR(B8)=2012),"ok",IF(C8="Maxxia","Ok","cancel"))))

B8 cell value
05-Dec-12

Yes B8 year is 2012 and its returing ok as answer like this the formula is working fine for all the remaining cells and getting the right answers

[Wanna be excel wiz]

The formula is doing the job perfectly

D8 cell formula
=IF(A8<$D$1,"Cancel",IF(A8="Check","No Match",IF((YEAR(B8)=2012),"ok",IF(C8="Maxxia","Ok","cancel"))))

B8 cell value
05-Dec-12

Yes B8 year is 2012 and its returing ok as answer like this the formula is working fine for all the remaining cells and getting the right answers

Unfortunately no, as the payee is not "Maxxia" it is "Allianz", and should therefore be "cancel". Likewise with row 23, the last payment was in 2011 (therefore no 2012 payment received)

I think it has something to do with trying to enter the value if false, if i try and enter it at the end it says i have too many arguments, so where do i enter the false value responses?

[:) Sixthsense :)]

Then it is not the formula error its the error in the way the formula was written.

Please explain your requirement for giving a right formula, because posting your formula and asking us to go through it and fix the error is something leads to lengthy discussion like this.

[Wanna be excel wiz]

Then it is not the formula error its the error in the way the formula was written.

Please explain your requirement for giving a right formula, because posting your formula and asking us to go through it and fix the error is something leads to lengthy discussion like this.

Apologies I'm new at this and just trying to troubleshoot why my formula isn't working...

In my original post I did explain;
However, my formula is returning the comment "OK", When the date in column N is greater than today (ok) and column R is NOT "Maxxia". This result should infact be "Cancel" as the final section of the formula says if value is false.

I appreciate you taking the time to review my problem

[:) Sixthsense :)]

Can you please explain it as per the file you attached for better understanding and easy workout?

[Wanna be excel wiz]

Can you please explain it as per the file you attached for better understanding and easy workout?

Ok, I've attached the file again, and filtered on the incorrect results, with a note next to it on what the answer should be...

It is quite possible that I have written my formula incorrectly. For example, if the year isn't 2012, I want it to say "no Payment" perhaps, but if i try and enter that it says i have too many arguments

=IF(A8<$D$1,"Cancel",IF(A8="Check","No Match",IF((YEAR(B8)=2012),"ok",IF(C8="Maxxia","Ok","cancel"))))

I hope i'm making sense

[:) Sixthsense :)]

May be this...

In F8 cell

Please Login or Register  to view this content.

Drag it down and above....

[Wanna be excel wiz]

WOOHOOO

Thank you so much that has done the job perfectly!!!

Thank you for your patience and fantastic excel mind!!

[:) Sixthsense :)]

Glad your problem is solved and thanks for the feedback

One more suggestion from my end, if the 2012 is a stable value then keep it as it is in your formula otherwise refer it to a cell so that editing is not needed in formula.