Getting Average from formulated fields

[JockVSJock]

Don't do much work in Excel, until now.

I have three fields: Coverting a 8/17/73 field into a person's years and months and then taking the two and combining them into one field:

In those fields, I am doing a formula to format the output:
The years field: Datedif(L3,NOW(),"Y&quot
The month field: Datedif(LE,NOW(),"YM&quot

And it throws the output into the field to the right of it.

This is nice, but I can't do a complete average on it.

I keep getting #DIV/0!

What do I need to get the average to come out right?

thanks

 

[SkipVought]

jvj,

What are you trying to average?

What is Datedif function?

Please post some specific data.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Mikeb1970]

Hi,

We would need more info if you want help offered

Mike

 

[KenWright]

DATEDIF function:-

http://cpearson.com/excel/datedif.htm

Same as Skip though, what are you trying to average. If it's months then just use

Datedif(L3,NOW(),"M&quot in a single cell for each record and average all those. Why are you converting to years as well, or do you need want it in years?

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[JockVSJock]

I can't show a birthday on this report, like this, 8/17/1973.
I am told it is against the law.


But I can show it like so 3005.

So in column a is the birthday field 8/17/1973

In column b is a formual that takes the yr and converts it

=dateif(b3,now(),"Y&quot

In column c is a formual that takes the months and converts it

=dateif(c3,now(),"YM&quot

In column d it takes the output from both b and c and puts it together.

=b3&"0"&c3


BUT, for some reason if I want to get an average age from this column, I keep getting this:

#DIV/0!

Can I not do a simple average because of all of the formuals formatting the output?

What do I need to do to fix the output?

 

[TonyJollans]

Hi JockVSJock,

The most likely reason you get #DIV/0 is because you have no numeric fields to average. The result of your =b3&"0"&c3 formula is a string.

But you have a few more troubles than that, I think. If your date of birth was 2/17/1973 instead of 8/17/1973, then you would end up with 30011 instead of the desired 3011. Also the average of, say, 3005 and 2905 is 2955, not 2911.

Firstly, you might want to change the formula to, perhaps, =VALUE(B3&RIGHT(0&C3,2)).

And then, whether or not you do that, instead of trying to average the results of your formulae, why not average the actual birthdates and use the formulae on the result to get it in the format you want.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SkipVought]

J

Two issues.

1. the "birthdate" that you cannot display on the report -- You could change the FORMAT for that column to something that does not display the day. The DATE is still there.

2. average issue -- define a range name for the dates using Insert/Name/Define (I used rDate) and in column A the RefersTo is

=OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)

Then the Average formula is

=AVERAGE(rDate)

FORMATTED with a date format.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884