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..
[TABLE="width: 1255"]
<TBODY>[TR]
[TD][/TD]
[TD="align: left"]=TODAY()</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name</SPAN>[/TD]
[TD]DOB</SPAN>[/TD]
[TD]AGE</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Child1</SPAN>[/TD]
[TD="align: left"]39569</SPAN>[/TD]
[TD="align: left"]=($B$1-B3)/365</SPAN>[/TD]
[TD="align: left"]=IF(C3<6," ",IF(C3<8,"Beaver",IF(C3<10,"Cubs",IF(C3<13,"Scouts"," "))))</SPAN>[/TD]
[/TR]
[TR]
[TD="align: left"]Child2</SPAN>[/TD]
[TD="align: left"]39017</SPAN>[/TD]
[TD="align: left"]=($B$1-B4)/365</SPAN>[/TD]
[TD="align: left"]=IF(C4<6," ",IF(C4<8,"Beaver",IF(C4<10,"Cubs",IF(C4<13,"Scouts"," "))))</SPAN>[/TD]
[/TR]
[TR]
[TD="align: left"]Child3</SPAN>[/TD]
[TD="align: left"]38024</SPAN>[/TD]
[TD="align: left"]=($B$1-B5)/365</SPAN>[/TD]
[TD="align: left"]=IF(C5<6," ",IF(C5<8,"Beaver",IF(C5<10,"Cubs",IF(C5<13,"Scouts"," "))))</SPAN>[/TD]
[/TR]
[TR]
[TD="align: left"]Child4</SPAN>[/TD]
[TD="align: left"]37640</SPAN>[/TD]
[TD="align: left"]=($B$1-B6)/365</SPAN>[/TD]
[TD="align: left"]=IF(C6<6," ",IF(C6<8,"Beaver",IF(C6<10,"Cubs",IF(C6<13,"Scouts"," "))))</SPAN>[/TD]
[/TR]
[TR]
[TD="align: left"]Child5</SPAN>[/TD]
[TD="align: left"]36800</SPAN>[/TD]
[TD="align: left"]=($B$1-B7)/365</SPAN>[/TD]
[TD="align: left"]=IF(C7<6," ",IF(C7<8,"Beaver",IF(C7<10,"Cubs",IF(C7<13,"Scouts"," "))))</SPAN>[/TD]
[/TR]
[TR]
[TD="align: left"]Child6</SPAN>[/TD]
[TD="align: left"]40330</SPAN>[/TD]
[TD="align: left"]=($B$1-B8)/365</SPAN>[/TD]
[TD="align: left"]=IF(C8<6," ",IF(C8<8,"Beaver",IF(C8<10,"Cubs",IF(C8<13,"Scouts"," "))))</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]