Date-intervals in =if(and()) function

Hallo everybody

I am trying to use an =if(and()) function to age-distribute a lot of data into different categories depending on the age of the specific data.
But in trying to do so I get the "Not trying to type a formula"-error.

I have tried to &"<="& in order to overcome the problem, but that doesnt help; I get the same mistake.

The formula is as follows:

=IF(AND(F5>=$B$8;F5<=$C$8;$A$8);IF(AND(F5>=$B$9;F5<=$C$9;$A$9;IF(AND(F5>=$B$10;F5<=$C$10;$A$10;IF(AND(F5>=$B$11;F5<=$C$11;$A$11;IF(AND(F5>=$B$12;F5<=$C$12;$A$12;IF(AND(F5>=$B$13;F5<=$C$13;$A$13;IF(F5<=$B$14;$A$14;"Error"))))))))))))

What I on the other hand CAN write is:

=IF(AND(F5>=$B$8;F5<=$C$8;$A$8);IF(AND(F5>=$B$9;F5<=$C$9;$A$9;"Error");"Error")) (Here I get an False, which is fine)

Anyone who knows whats up with that?

You seem to have the closed bracket for your AND functions in the wrong place. For instance, the formula should start with:

=IF(AND(F5>=$B$8;F5<=$C$8);$A$8;IF(AND(F5>=$B$9;F5<=$C$9);$A$9;IF(AND(F5>=$B$10; … and so on.

Hope this helps.

Pete

Pete is correct, however I suspect if the ages are start and end ages
0-9
10-20
21-30

etc the formula can be simplified as an if statement as many of the terms are redundant as the ifs are nested


or a simple vlookup will suffice

https://exceljet.net/formula/group-numbers-with-vlookup

Hey Pete

Thanks for the answer!

It helped a bit, and thank you for that, unforturnaly its not completed.

I can write the following, where I delete the bracket in the first if(and())-function, but keep the brackets in the remaing if(and())-functions:

=IF(AND(F5>=$B$8;F5<=$C$8;$A$8);IF(AND(F5>=$B$9;F5<=$C$9);$A$9;IF(AND(F5>=$B$10;F5<=$C$10);$A$10;IF(AND(F5>=$B$11;F5<=$C$11);$A$11;IF(AND(F5>=$B$12;F5<=$C$12);$A$12;IF(AND(F5>=$B$13;F5<=$C$13);$A$13;IF(F5<=$B$14;$A$14;"Fejl"))))))) = "FALSE".

But actually its supposed to say "5+ years" instead of "False", because the date in F5 is <=$B$14 ("5+ years" is the category that the date is suppose to be distrubuted to according to the formula).

Any idea why I get "False" instead of "5+ year"?

Sorry, didnt see the rest of you fellas answers. I am gonna try that.

Thank you all for the answers.
I went with Pete_UK's original answer, and it worked very well!