Excel Code Will Not Perform The Task I Require

[walky82]

Hi People,

I posted this yesterday and still can not get the code to work.

Basically what I need I think is quiet simple. I think!

I need excel to look if one of the two statement are true and if there not then it will equal what ever is in cell D17. The statements however will never be true together it can only be one or the other.

Here is the code I am using. The code only works if I remove the second statement.

1st Statement,

=IF(AND((D17=6),((YEAR(NOW())-YEAR(K5))<25),(N13>=3),(F11+H11+J11+L11+N11)=0),5,

2nd Statement,

IF(AND((D17=6),((YEAR(NOW())-YEAR(K5))<25),(N13>=5),(F11+H11+J11+L11+N11)=0),3,

and if not,

D17))

Thanks Heaps in ADVANCE.

 

[walky82]

Hi Again people I think I might have diagnosed the problem... I have just left N13 as greater than. However can Excel still handle 2 true statements?

 

[pgc01]

Hi walky82

If I understand correctly you have a flaw in your logic and the formula makes no sense.

Let's assume that common conditions

AND((D17=6),((YEAR(NOW())-YEAR(K5))<25),(F11+H11+J11+L11+N11)=0)

are all True.

You get

=IF(N13>=3,5,IF(N13>=5,3,D17))

You can see that the formula has 2 errors:

1 - if a number is bigger than 5 it's also bigger than 3, so you never meet the condition in the second IF().

Ex:
N13=8
Since N13 is bigger than 3 you meet the condition in the first IF() and so the result is 5, you never get to the second IF().

2 - you don't have a return value for the first IF() in case of False. It should be D17 as in the second IF()

Ex: if the formula's logic made sense:

=IF(N13>=3,5,IF(N13>=5,3,D17), D17)

with also a return value for the first IF in case of False.


I don't understand what you want and so I cannot suggest a formula that solves your problem. I hope that these notes help you to correct the formula.

If you need further assistance please explain what the formula should accomplish.

 

[walky82]

Thanks heaps for your help I have worked it out! It was very simple as I thought I just needed to have a less than if the first statement as well.

Thanks for taking the time!