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]

Oops that was an oversight on my behalf! Obviously hadn't updated cell AX7 in the source data sheet. Are you able to make the heifers by age worksheet only show data for the current month (& of course the previous months but not future months)?

Just looking at cell AW118 in the reconc heifers worksheet. It closes on -65 for 2014/15 however it doesn't seem to be carrying over the -65 in cell AW103 in the same worksheet in the 2015/16 file?

 

[gaz_chops]

Just so I understand, are you changing the date in AX7 to 1/7/15? Do you want the current month to show from day 1 of the current month?

I'll need to get my head around what we did on this spreadsheet, i'll get back to you.

 

[dunmore83]

Yes I have rolled the file over to 2015/16 so AX7 will be 1/7/15.

Yes please from day 1 of the current month & any previous months.. but not future months. Thanks

 

[gaz_chops]

OK.

I have made quite a few changes to the formulas this morning, I didn't realise at the start that your data is likely to grow vertically! I have created a number of Dynamic Named Ranges and incorporated them in the formulas, this means that as you add new data the Ranges will automatically include it, otherwise you would have to adjust all of the formula ranges whenever you added new data!!

e.g. In RED
=IF($D17>TODAY(),0,(IFERROR(COUNTIFS(OFFSET('Stock-Source-Data'!$AX$10,0,MATCH(EOMONTH($D17,0),Month,0)-1,DataCount,),">="&J$8*30,OFFSET('Stock-Source-Data'!$AX$10,0,MATCH(EOMONTH($D17,0),Month,0)-1,DataCount,),"<"&P$8*30,Sex,J$11,Owner,J$14),0)))

I adjusted so it only returns data for current month and earlier.

https://www.dropbox.com/s/gi461eyvsa2etqx/2015-16-Stock-ReconV2.xlsx?dl=0

 

[dunmore83]

Thanks for making those adjustments. Certainly would be a headache changing the formulas every time new data was added!

Sorry if I am missing something (as I have done a few times so far!!) however the carry-over lines in the reconc-heifers & reconc-bulls don't appear to be pulling the data from pre July 2015

The deaths formulas for the heifers appears to be working but not the preg & calved formulas.

For the bulls a few don't seem to be working;
- deaths showed -14 for the 1st group & -45 for the 2nd group at the end of 2014/15
- culls showed -31 for the 1st group & -28 for the 2nd group at the end of 2014/15
- sold showed -54 for the 2nd group at the end of 2014/15

I realize that last file you sent doesn't have the formulas in these cells for 2015/16 however I am not sure exactly how to write them??!!

 

[gaz_chops]

I have amended all the formulas for "By Age" & "Reconc Heifers" adding the named ranges into them. I have left "Reconc Bulls" for you to do

I have saved it as 2014-2015, it is easy now to just change the date in "Source Data" AX7 to 1/7/15 to update all the sheets.

https://www.dropbox.com/s/366b8vo57j98kab/2014-15-Stock-ReconV2.xlsx?dl=0

Upload the file when you have done "Bulls" and I can take a look at your last questions.

 

[gaz_chops]

Should have added, you should be able to copy the Heifers sheet to re-create the Bulls, some columns are different (e.g. Cull) I will need to create the named ranges for them once you've done what you can.

 

[dunmore83]

I have updated those formulas in the heifers worksheet for sold, preg & calved.

Appears to be an issue with where the formulas are pulling their data from.

If we take Calved Heifers group #1 01-Mar-15 to 30-Jun-15 as an example it is showing -84 in cell AW. This seems to be that the formula is looking at column AU in the source data sheet & saying that anything that has calved from 01-Mar-15 forwards should be counted.

However the methodology (for deaths, sold, preg also) is;
- 1st search the birth date range for dates that qualify (i.e. 01-Mar-15 to 30-Jun-15)
- then search the sex (heifer or bull)
- then search the owner (Dun or Sier)
- then search anything that has occurred in the deaths, culled, sold, preg columns that has occurred prior to 01-July-15 (or in the original workbook 01-July-14)

https://www.dropbox.com/s/mkzb0tpazobgexv/2015-16-Stock-Recon-Returned-1.xlsx?dl=0

 

[gaz_chops]

But -84 is correct, there are 84 between 1/3/15 & 30/6/15!
It checks if the date in V2 is < than D20, if it is, it uses V1 & V2 as the "between dates", if not it uses V1 & D20 as the "between dates".

What did you change on the Heifers sheet? It was just the Bulls sheet that needed amending, with the name ranges!

Did you find the spreadsheet had become very slow? The named ranges appear to slow mine dramatically!! I made need to change them from a Dynamic range to simply be a high('ish) number of rows.
How big (number of rows) do you think it may grow to?

 

[gaz_chops]

I have added the formulas (with named ranges), to the #1 Bulls sheet, copy to the other #'s and adjust the Date Ranges from V1/V2 to the appropriate dates.

https://www.dropbox.com/s/gi461eyvsa2etqx/2015-16-Stock-ReconV2.xlsx?dl=0