Index IsNumber Data Anaysis

[dunmore83]

Hoping you might be able to provide some assistance on another part of the same workbook?!! If only you were in Australia I could attempt to send you some fresh milk

Anyways if your willing the 1st part I need you to look at is the formula in column D of the 1st worksheet... the problem here is that I don't want the rows with no data. Is it possible somehow to have only have rows when the formula returns a numerical value ??

https://www.dropbox.com/s/75bv2uwr7nbhtsh/Gaz-AI-Analysis.xlsx?dl=0

 

[dunmore83]

Ok I think we are getting a little confused!!

It is looking back for the same ear tag number... so if it is more than 1 insemination than that ear tag number may have a different tech for its 2nd last, 3rd last, 4th last insemination. Those 2nd last, 3rd last, 4th last inseminations need to be counted individually for that tech.

When an "E" then each individual insemination for the "ear tag" needs to counted individually for the different techs I.e. say cow 900 was E & had 3 insems - one on 1/5/15 for David Sier one on 20/5/15 for Farm West & one on 01/6/15 for David Sier then formula should sum 2 for David Sier & one for Farm West

 

[gaz_chops]

OK look at real data, Tag No. 436 is P and has had 2 insems
9/5/15 - David Sier
4/6/15 - Farm West

That results in 1 each.

If the Tag was E it wouldn't make any difference, unless i'm missing something!

 

[gaz_chops]

I think!! I got it, I hope!

In-Calf = a count of the last insems, where the Tag = "P".
Empty = the sum of all Insems minus the above.
Straws used = sum of above.

[TABLE="width: 278"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Straws Used[/TD]
[TD][/TD]
[TD][/TD]
[TD]# In-Calf[/TD]
[TD][/TD]
[TD][/TD]
[TD]# Empty[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Farm West[/TD]
[TD][/TD]
[TD][/TD]
[TD] 48[/TD]
[TD][/TD]
[TD][/TD]
[TD] 21[/TD]
[TD][/TD]
[TD][/TD]
[TD] 27[/TD]
[/TR]
[TR]
[TD]David Sier[/TD]
[TD][/TD]
[TD][/TD]
[TD] 82[/TD]
[TD][/TD]
[TD][/TD]
[TD] 17[/TD]
[TD][/TD]
[TD][/TD]
[TD] 65[/TD]
[/TR]
</tbody>[/TABLE]

 

[dunmore83]

Yes 1 each in that example

However if it was an E it would still be 1 each.

In its simplest form all we are really doing is counting each insemination individually for each tech (inside the dates) regardless of whether it is a P or E. However there needs to be a P or E in that column for the ear tag to qualify i.e. we are not counting inseminations for ear tags with things such as recheck

 

[dunmore83]

There is no problem with your formulas for in-calf & empty.. they are perfectly ok.

The recent discussion is only regarding the straws used formula

 

[dunmore83]

There is no problem with your formulas for in-calf & empty.. they are perfectly ok.

The recent discussion is only regarding the straws used formula

 

[gaz_chops]

See my last post, i had an epiphany

It's difficult to sometimes follow in text back and forth, especially as you know what you want and i'm learning Greek

Check uploaded file

https://www.dropbox.com/s/54yved1r10wilx5/PD-Dates.xlsx?dl=0

 

[gaz_chops]

There is an error in the references which i've corrected......

https://www.dropbox.com/s/54yved1r10wilx5/PD-Dates.xlsx?dl=0

 

[gaz_chops]

An updated version of Analysis with one sort button, each click will re-sort A-Z/Z-A, also fixed a problem with 1 of the references.

https://www.dropbox.com/s/cgfddedu5zqigeu/Gaz-AI-Analysis-AtoZ.xlsm?dl=0

 

[dunmore83]

Seems we are having some trouble on this one!!!

The formulas in cells V8/V9 & Y8/Y9 did not need adjusting. In fact they are working just the way they need to.

It is only the straws used formula in cells S8/S9 that we need to focus on.

The very 1st ear tag is a good example. Number 7 >

08-May-15 Farm West
29-May-15 Farm West
.
"E" on 13-Jul-15

The formula in cell S8 would need to count 1 for Farm West & 1 for David Sier.

In fact to keep it simple all we need to do is individually count the number of times the tech name appears in column M of the PD-Dates worksheet with the proviso that the ear tag in column G of the AI-02-Dates worksheet has a P or E in column V of the PD-01-Dates worksheet AND that date in column S is within inside the start & finish dates in G8/G9

Is my greek making any sense now?!!