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]

Some more work needed on the ages if your willing?!

I have introduced a new worksheet "Stock-Heifers-By-Age". This one needs to sum the total number of "live heifers" owned by either "Dun" or "Sier" based on the entries in Row 8. Ideally these age categories (i.e. 0 to 3 months, 3 to 6 months etc.) will be changeable.

To achieve this we need to make some changes in the source data sheet. As I have added the new columns Given, Sold & Calved if a value is found in these cells then a G S or C needs to be entered on the respective date between AX:OX for that point forward so that it is not included in the age calculations. This is the same principle as you did for the deaths column.

I hope this makes some sense!!!

https://www.dropbox.com/s/npaw1zxttk7ap79/Gaz-Ages.xlsx?dl=0

 

[dunmore83]

Should have been an extra column for 3 to 6 months... however this should be easy for me to add once it is structured correctly. Cheers

 

[gaz_chops]

I had to change the format of the cells for Months From & To so you can change them, just change the number and it will recalculate.

Note: The last columns (24 +) have a different formula as there is no need for the Less than part

Check the numbers look correct.

https://www.dropbox.com/s/0lrpewjgkm28njy/Gaz-Ages.xlsx?dl=0

I added the formula for the Given, Sold & Calved, I noticed some have dates for Death & Given/Sold, in these cases the formula will place a "D" in the relevant column & ignore the others.

 

[gaz_chops]

Realised I hadn't added the "C" for calved, now added. It wipes out most of the data with "C's", are you sure that is what you want?

 

[dunmore83]

Thanks again. Your a legend! I will review & get back to you. Cheers

 

[dunmore83]

Hi,

There doesn't seem to be a file link from post 114 ? Can you please repost

Yeah once a calving date appears in Calved ? (column AU) a C needs to be placed in the corresponding date between AX:OX & that animal excluded from any age calculations & number of animals in age group calculations

 

[gaz_chops]

It should be the same link as I updated it. Check if the C's are there?

 

[dunmore83]

Doesn't seem to have the C's on that copy

 

[gaz_chops]

Try this one

https://www.dropbox.com/s/0lrpewjgkm28njy/Gaz-Ages.xlsx?dl=0

 

[dunmore83]

Thanks that's excellent.

It's looking really good now & extremely happy with how it's come together.

One more thing... are you able to pinpoint why the closing balances from the Stock-Reconc.-Heifers worksheet is different to the Stock-Heifers-By-Age worksheet? As shown by the calculations in BC32 & BF32. Ideally I need these to be the same

https://www.dropbox.com/s/kvvu8yjxmw8g0w8/Gaz-Comparison.xlsx?dl=0