Complicated Excel Formula Help Needed

[RachaelRay]

Hi - I hope that someone can help me with this spreadsheet for my scouting groups waiting list.

This is what i want to do.

If i have a child's DOB in a cell, another cell (say A3) then calculates their age at today's date.

This is what i want to do in another cell.

If cell A3 is less than 6 or greater than 13 then leave blank
If cell A3 is greater than 6 but less than 8 (or between 6 & 8 if that works better) display Beavers
If cell A3 is greater than 8 but less than 10 (or between 8 & 10) display Cubs
If cell A3 is greater than 10 but less than 13 (or between 10 & 13) display Scouts

Please can someone help with a formula to do this and return what i need to make my huge waiting list more managable.

Many thanks

Rach

 

[Joe4]

Welcome to the Board!

Use a Nested IF statement, working your way up the ladder, like this:

=IF(A3<6,"",IF(A3<8,"Beavers",IF(A3<10,"Cubs",IF(A3<13,"Scouts",""))))

Note, depending on whether or not you want to include that upper age limit, you may need to change the < to <=.
(For example, its not clear if someone is exactly 8, if they should be a Beaver or Cub).

 

[WaterGypsy]

you can try:

=IF(OR(A3<6,A3>13),"",IF(A3<8,"Beavers",IF(A3<10,"Cubs","Scouts")))

 

[Asala42]

I recommend putting in two formula columns:

Age

=YEAR(TODAY())-YEAR(A3)-1*(TODAY()>(DATE(YEAR(TODAY()),MONTH(A3),DAY(A3))))

Scout title with your if logic

=IF(OR(B3<6,B3>13),"",IF(B3<8,"Beavers",IF(B3<10,"Cubs","Scouts")))

where A3 is a DOB. B3 is the calculated age.

 

[RachaelRay]

you can try:

=IF(OR(A3<6,A3>13),"",IF(A3<8,"Beavers",IF(A3<10,"Cubs","Scouts")))

Thanks - this worked brilliantly!!!

 

[tjcarr]

Try this..
DOB field is 'date' of course..

	=TODAY()</SPAN>
Name</SPAN>	DOB</SPAN>	AGE</SPAN>
Child1</SPAN>	39569</SPAN>	=($B$1-B3)/365</SPAN>	=IF(C3<6," ",IF(C3<8,"Beaver",IF(C3<10,"Cubs",IF(C3<13,"Scouts"," "))))</SPAN>
Child2</SPAN>	39017</SPAN>	=($B$1-B4)/365</SPAN>	=IF(C4<6," ",IF(C4<8,"Beaver",IF(C4<10,"Cubs",IF(C4<13,"Scouts"," "))))</SPAN>
Child3</SPAN>	38024</SPAN>	=($B$1-B5)/365</SPAN>	=IF(C5<6," ",IF(C5<8,"Beaver",IF(C5<10,"Cubs",IF(C5<13,"Scouts"," "))))</SPAN>
Child4</SPAN>	37640</SPAN>	=($B$1-B6)/365</SPAN>	=IF(C6<6," ",IF(C6<8,"Beaver",IF(C6<10,"Cubs",IF(C6<13,"Scouts"," "))))</SPAN>
Child5</SPAN>	36800</SPAN>	=($B$1-B7)/365</SPAN>	=IF(C7<6," ",IF(C7<8,"Beaver",IF(C7<10,"Cubs",IF(C7<13,"Scouts"," "))))</SPAN>
Child6</SPAN>	40330</SPAN>	=($B$1-B8)/365</SPAN>	=IF(C8<6," ",IF(C8<8,"Beaver",IF(C8<10,"Cubs",IF(C8<13,"Scouts"," "))))</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>