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 for uploading the latest file.

I copied the formulas for the opening line down into all 7 groups for the heifers.

It seems only the deaths column is picking up any data. If we take group 1 as an example at the end of 2014-15 there were 15 deaths for this group. However the new formula in the 2015-16 file is counting 4. Any ideas?

If we take heifer group 5 as another example at the end of 2014-15 there were 22 preg & 65 calved. However the new formulas are showing 0 for both at the start of 2015-16.

Also 16 heifers were sold in July 2015. These should show in group #3 as their births were pre 01-Aug-15 & after 03-Jan-14. However cell AQ62 is showing 0.

https://www.dropbox.com/s/f0glfhphswjtvzm/2015-16-Stock-Recon-V3.xlsx?dl=0

 

[gaz_chops]

You need to copy ALL of the formulas from #1 as I amended them, sorry if that wasn't clear. Once you do then 16 will show as sold in July '15.

I will look at the other questions in the morning.

 

[dunmore83]

Do you mean from group #1 heifers dun AK19:AW31 ? If so I think I have already done that haven't I in the last file I uploaded ?

 

[gaz_chops]

Not in the file you uploaded, it needs doing for Heifs & Bulls. All the formulas from #1 to the other #'s.

 

[gaz_chops]

Sorry wasn't by my computer, AB19:AW31

 

[dunmore83]

Ok I got the minus 16 working by carrying down the formulas for the months i.e. rows 20:31

Still can't seem to get the right numbers (most are 0's) for the carry-over lines... as explained in post 161

 

[gaz_chops]

Upload the file so I've got the latest. The carry over figs confuse me! Births are ok, but the others I'm not sure what you're after. Do you want ALL deaths prior to 1/7/15? Against each #?

 

[dunmore83]

https://www.dropbox.com/s/f0glfhphswjtvzm/2015-16-Stock-Recon-V3.xlsx?dl=0

 

[dunmore83]

Carry-over formulas

Group #1 heifers as an example;

1st qualification = birth dates
2nd qualification = owner
3rd qualification = sex
4th qualification = count anything that qualifies from the above in which the date of the death, preg, calving, sold, given away etc. is prior to the start of the financial year i.e. prior to 1-July-2014 or 01-July-15

e.g. birth date 05-Mar-15, dun, heifer, died on 21-Mar-15 then is would be counted as -1 (for the 2015-16 version) as it qualifies for group #1 & the death was prior to the start of the financial year

 

[gaz_chops]

Still looks like you haven't updated them all.

e.g.
AN19 looks like this (which is correct)
=-(IF($V$2<$D20,COUNTIFS(DateCalved,">="&$V$1,DateCalved,"<="&$V$2,Deaths,">="&$V$1,Deaths,"<="&$V$2,Owner,$AH$10,Sex,AH$13),COUNTIFS(DateCalved,">="&$V$1,DateCalved,"<="&$D20,Deaths,">="&$V$1,Deaths,"<="&$D20,Owner,$AH$10,Sex,AH$13)))

AN61 looks like (should be same as above)
=-(IF($P$2<$D62,COUNTIFS(Deaths,">="&$P$1,Deaths,"<="&$P$2,Owner,AH$10,Sex,$AH$13),COUNTIFS(Deaths,">="&$P$1,Deaths,"<="&$D62,Owner,AH$10,Sex,$AH$13)))