Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Age
I have a column (E:E) for Dates of Birth, and the next column (F:F) has a
formula to calculate the age as follows: =(TODAY()-E:E)/365 Cells of column F:F are formatted as "Number" with decimal places "1". I also have a conditional formatting to highlight cells in that column that have an age between 0 and 3. the condition reads as follows: Cell value is BETWEEN 0 AND 3 I also have a cell at the bottom of column F:F that counts the TOTAL number of children under 3 years of age using the formula: =COUNTIF(F12:F18, "<3") So far all sound logical. However, something very weird is happening: Considering TODAY() is 23/05/07, when I enter a date of birth 02/06/04, the age then appears as 3.0, this age cell gets highlighted accroding to the Conditional Formatting, and the child gets counted in the TOTAL even though the formula in the TOTAL cell counts those <3 not those <=3 And if I enter a date of birth 15/05/04, the age cell calculates the age as 3.0, however, the cell doesn't get highlighted according to the Conditional Formatting and that child doesn't get counted in the TOTAL cell! How come the values of the age cells are treated differently even though they are both equal 3.0? Your assistance will be greatly appreciated. Tendresse |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Age
Tendresse
I think the issue you are having is the fact that even though you limit the cell value to 1 decimel point, the calculations use that actual numbers. If you extend the values of column F, the first one is 2.969 and the second one is 3.019. Therefore the first one would get counted and the second one would not. Hope this helps. -- TG "Tendresse" wrote: I have a column (E:E) for Dates of Birth, and the next column (F:F) has a formula to calculate the age as follows: =(TODAY()-E:E)/365 Cells of column F:F are formatted as "Number" with decimal places "1". I also have a conditional formatting to highlight cells in that column that have an age between 0 and 3. the condition reads as follows: Cell value is BETWEEN 0 AND 3 I also have a cell at the bottom of column F:F that counts the TOTAL number of children under 3 years of age using the formula: =COUNTIF(F12:F18, "<3") So far all sound logical. However, something very weird is happening: Considering TODAY() is 23/05/07, when I enter a date of birth 02/06/04, the age then appears as 3.0, this age cell gets highlighted accroding to the Conditional Formatting, and the child gets counted in the TOTAL even though the formula in the TOTAL cell counts those <3 not those <=3 And if I enter a date of birth 15/05/04, the age cell calculates the age as 3.0, however, the cell doesn't get highlighted according to the Conditional Formatting and that child doesn't get counted in the TOTAL cell! How come the values of the age cells are treated differently even though they are both equal 3.0? Your assistance will be greatly appreciated. Tendresse |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Age
I should have also said that you can go to
tools---options--calculation---check Precision as displayed box. -- TG "Tendresse" wrote: I have a column (E:E) for Dates of Birth, and the next column (F:F) has a formula to calculate the age as follows: =(TODAY()-E:E)/365 Cells of column F:F are formatted as "Number" with decimal places "1". I also have a conditional formatting to highlight cells in that column that have an age between 0 and 3. the condition reads as follows: Cell value is BETWEEN 0 AND 3 I also have a cell at the bottom of column F:F that counts the TOTAL number of children under 3 years of age using the formula: =COUNTIF(F12:F18, "<3") So far all sound logical. However, something very weird is happening: Considering TODAY() is 23/05/07, when I enter a date of birth 02/06/04, the age then appears as 3.0, this age cell gets highlighted accroding to the Conditional Formatting, and the child gets counted in the TOTAL even though the formula in the TOTAL cell counts those <3 not those <=3 And if I enter a date of birth 15/05/04, the age cell calculates the age as 3.0, however, the cell doesn't get highlighted according to the Conditional Formatting and that child doesn't get counted in the TOTAL cell! How come the values of the age cells are treated differently even though they are both equal 3.0? Your assistance will be greatly appreciated. Tendresse |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Age
Thank you very much, Gerigto. That makes sense. Well, I suppose the way to go
around it is to format the age cell to 2 decimal points rather than 1. Thanks again for your reply. That was really helpful. Tendresse "Gerigto" wrote: Tendresse I think the issue you are having is the fact that even though you limit the cell value to 1 decimel point, the calculations use that actual numbers. If you extend the values of column F, the first one is 2.969 and the second one is 3.019. Therefore the first one would get counted and the second one would not. Hope this helps. -- TG "Tendresse" wrote: I have a column (E:E) for Dates of Birth, and the next column (F:F) has a formula to calculate the age as follows: =(TODAY()-E:E)/365 Cells of column F:F are formatted as "Number" with decimal places "1". I also have a conditional formatting to highlight cells in that column that have an age between 0 and 3. the condition reads as follows: Cell value is BETWEEN 0 AND 3 I also have a cell at the bottom of column F:F that counts the TOTAL number of children under 3 years of age using the formula: =COUNTIF(F12:F18, "<3") So far all sound logical. However, something very weird is happening: Considering TODAY() is 23/05/07, when I enter a date of birth 02/06/04, the age then appears as 3.0, this age cell gets highlighted accroding to the Conditional Formatting, and the child gets counted in the TOTAL even though the formula in the TOTAL cell counts those <3 not those <=3 And if I enter a date of birth 15/05/04, the age cell calculates the age as 3.0, however, the cell doesn't get highlighted according to the Conditional Formatting and that child doesn't get counted in the TOTAL cell! How come the values of the age cells are treated differently even though they are both equal 3.0? Your assistance will be greatly appreciated. Tendresse |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Age
Gerigto,
That last tip was very handy. Thanks a million. Tendresse "Gerigto" wrote: I should have also said that you can go to tools---options--calculation---check Precision as displayed box. -- TG "Tendresse" wrote: I have a column (E:E) for Dates of Birth, and the next column (F:F) has a formula to calculate the age as follows: =(TODAY()-E:E)/365 Cells of column F:F are formatted as "Number" with decimal places "1". I also have a conditional formatting to highlight cells in that column that have an age between 0 and 3. the condition reads as follows: Cell value is BETWEEN 0 AND 3 I also have a cell at the bottom of column F:F that counts the TOTAL number of children under 3 years of age using the formula: =COUNTIF(F12:F18, "<3") So far all sound logical. However, something very weird is happening: Considering TODAY() is 23/05/07, when I enter a date of birth 02/06/04, the age then appears as 3.0, this age cell gets highlighted accroding to the Conditional Formatting, and the child gets counted in the TOTAL even though the formula in the TOTAL cell counts those <3 not those <=3 And if I enter a date of birth 15/05/04, the age cell calculates the age as 3.0, however, the cell doesn't get highlighted according to the Conditional Formatting and that child doesn't get counted in the TOTAL cell! How come the values of the age cells are treated differently even though they are both equal 3.0? Your assistance will be greatly appreciated. Tendresse |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate in an IF | Excel Worksheet Functions | |||
How to calculate an age? | Excel Worksheet Functions | |||
Calculate | Excel Worksheet Functions | |||
calculate APR on ARM | Excel Worksheet Functions | |||
Calculate | Excel Discussion (Misc queries) |