Complex - Multiple Criteria

[dunmore83]

Excel 2012
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Ear Tag	Sex	NLIS		Birth Date				01-Jul-14	02-Jul-14	03-Jul-14	04-Jul-14	05-Jul-14	06-Jul-14	07-Jul-14	08-Jul-14
3
4
5	1658	Heifer	-	04.09.11	04-Sep-11				1,031	1,032	1,033	1,034	1,035	1,036	1,037	1,038
6	2112	Heifer	982123490405654	10.08.12	10-Aug-12				690	691	692	693	694	695	696	697
7	272	Heifer	982123490425668	27.08.12	27-Aug-12				673	674	675	676	677	678	679	680
8	2282	Heifer	982123490425757	28.09.12	28-Sep-12				641	642	643	644	645	646	647	648
9	3007	Heifer	982123490425176	24.01.13	24-Jan-13				523	524	525	526	527	528	529	530
10	3008	Heifer	982123490417932	24.01.13	24-Jan-13				523	524	525	526	527	528	529	530
11	3018	Heifer	982123490425858	29.01.13	29-Jan-13				518	519	520	521	522	523	524	525
12	3026	Heifer	982123498847561	31.01.13	31-Jan-13				516	517	518	519	520	521	522	523
13	3040	Heifer	982123490449063	06.02.13	06-Feb-13				510	511	512	513	514	515	516	517
14	3042	Heifer	982123490417737	06.02.13	06-Feb-13				510	511	512	513	514	515	516	517
15	3053	Heifer	982123490405560	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
16	3052	Heifer	982123490449125	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
17	3051	Heifer	982123489462454	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
18	3055	Heifer	982123490425967	08.02.13	08-Feb-13				508	509	510	511	512	513	514	515
19	3054	Heifer	982123490425848	08.02.13	08-Feb-13				508	509	510	511	512	513	514	515
20	3064	Heifer	982123490417515	11.02.13	11-Feb-13				505	506	507	508	509	510	511	512
21	3061	Heifer	982123490419708	11.02.13	11-Feb-13				505	506	507	508	509	510	511	512
22	3065	Heifer	982123498847685	12.02.13	12-Feb-13				504	505	506	507	508	509	510	511
23	3069	Heifer	982091016308716	14.02.13	14-Feb-13				502	503	504	505	506	507	508	509
24	3071	Heifer	982123490447819	14.02.13	14-Feb-13				502	503	504	505	506	507	508	509
25	3074	Heifer	982123490425039	17.02.13	17-Feb-13				499	500	501	502	503	504	505	506
26	3084	Heifer	982123490226691	26.02.13	26-Feb-13				490	491	492	493	494	495	496	497
27	3088	Heifer	982123490425140	28.02.13	28-Feb-13				488	489	490	491	492	493	494	495
28	3090	Heifer	982123489464042	03.03.13	03-Mar-13				485	486	487	488	489	490	491	492
29	3098	Heifer	982123489459356	11.03.13	11-Mar-13				477	478	479	480	481	482	483	484
30	3104	Heifer	982123489466360	01.04.13	01-Apr-13				456	457	458	459	460	461	462	463
31	3161	Heifer	982123490224041	09.08.13	09-Aug-13				326	327	328	329	330	331	332	333
32	3176	Heifer	982000191763119	15.08.13	15-Aug-13				320	321	322	323	324	325	326	327
33	3179	Heifer	982000191805821	16.08.13	16-Aug-13				319	320	321	322	323	324	325	326
34	3190	Heifer	982000191727512	18.08.13	18-Aug-13				317	318	319	320	321	322	323	324
35	3186	Heifer	982123498851839	18.08.13	18-Aug-13				317	318	319	320	321	322	323	324
36	3191	Heifer	982000191736709	19.08.13	19-Aug-13				316	317	318	319	320	321	322	323
ages-excel-forum

 

[dunmore83]

Thanks.

Looking at the numbers I am not sure they are correct yet... if we take the group 04-Sep-14 to 29-Jan-15 as en example & the month of September then the average age of the 19 for this month is 12.4 days (as the 2 births on the 30-Sep-14 are still 0 days on the 30th). Can we then convert 12.4 days to months using the excel pretext ?

I guess I am really trying to calculate the average of the total number in each group on the last day of each month

 

[gaz_chops]

I'd made a mistake!! Again It uses 30 days to calc months.

https://www.dropbox.com/s/yjf89lbw88ocggp/Stock-Numbers-Excel-Forum.xlsx?dl=0

 

[dunmore83]

Getting very close... however is it more accurate to take the average of the numbers found & divide by 30 ? Rather than the sum of the numbers found, divided by the total number in the group & then divided by 30. Just thinking if 0's are found in the lookup sum range then AVERAGE could be more appropriate as it excludes 0 numbers

 

[dunmore83]

The next formula I need is to modify this one;

=IF($F641=0,0,IF(VLOOKUP($B641,Deaths!$G$82:$G$119,1,0)=$B641,"D",(IF($F641<J$2,(J$2-$F641),0))))

It was in row 641 of the original file I sent. If it this formula is copied up or down I get N/A# so that is the 1st problem!

The 2nd one is needs to only return the D for the day of the death & subsequent future dates.

The 3rd issue is.. will it affect the latest formula we have that calculates the average age if a D is found in the range ?

 

[dunmore83]

Also just noticed there could be an issue with the age formula.

For example the group 16-Mar-13 to 24-Sep-13 is returning 11.19 for each month. However as these are obviously getting older & older the average age should be increasing with each month ?

 

[gaz_chops]

As you posted I was changing to use Average, I need to find out who the age isn't increasing month by month!!!

But take a look see if it looks more accurate?

https://www.dropbox.com/s/yjf89lbw88ocggp/Stock-Numbers-Excel-Forum.xlsx?dl=0

 

[gaz_chops]

Ignore the last 1! This increases month by month, see if it looks accurate?

https://www.dropbox.com/s/yjf89lbw88ocggp/Stock-Numbers-Excel-Forum.xlsx?dl=0

 

[gaz_chops]

Going for a lie down!!!

This I hope works....... Please test.

https://www.dropbox.com/s/yjf89lbw88ocggp/Stock-Numbers-Excel-Forum.xlsx?dl=0

 

[dunmore83]

It works!! Brilliant. Thanks

When you feel like resuming would be great if you could take a look at the other scenario I posted

 

[gaz_chops]

Good, I changed the formula on the "Stock-Source-Data" sheet for the age, to =IF($F5=0,"",(IF($F5<J$2,(J$2-$F5),""))), averageif ignores Blanks but not zeros!

The vlookup you posted earlier doesn't look complete? If it has < signs in it put a space either side, the site cuts it off otherwise.
Let me know what your trying to achieve with it, and where you had the formula entered? I'll take a look tomorrow am.