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

 

[gaz_chops]

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?!!

Well i thought i followed! But in your last example, how does David Sier have 1??? They both say Farm West!

I think the last sheet I uploaded is correct, check it and see what you think.

I think you may of misinterpreted my "Greek" comment, remember I know nothing about cows or what your data actually means, hence me learning "Greek".

 

[dunmore83]

Ooops that last one should have read 29-May-15 David Sier... so then the rest of the comment should make sense!!

I understood your greek comment. Just trying to explain things the best I can.

That last file doesn't look correct.. can you please read my post 60 again. Only the formula for straws used needs adjusting. The formulas for in-calf & empty were ok the 1st time around

 

[gaz_chops]

In what way does it look incorrect? I (maybe wrong) but from what you have said, the

In-calf number = the count where the tag has a "P" against it against the latest Tech.
Empty = the count of all the insems (minus the above) against the relevant Tech.
Straws Used = the total of the above. (The difference is Joe has 1 and 1 has no Tech against it).

 

[dunmore83]

In-Calf - correct
Empty - simply counts the number of "E"(s) against the relevant tech
Straws used - simply counts the number of inseminations against the relevant tech (needs to be sourced from AI0-02-Date worksheet as the same ear tag number can have different techs i.e. column G in PD-01-Dates doesn't show the split between the techs if more than 1 insemination) within the given dates in G8 & G9 provided the ear tag # has a P or E in column

 

[gaz_chops]

In-Calf - correctEmpty - simply counts the number of "E"(s) against the relevant techStraws used - simply counts the number of inseminations against the relevant tech (needs to be sourced from AI0-02-Date worksheet as the same ear tag number can have different techs i.e. column G in PD-01-Dates doesn't show the split between the techs if more than 1 insemination) within the given dates in G8 & G9 provided the ear tag # has a P or E in column

I think you are p'd off with my questions! What you just said is exactly what I said! Remember I'm trying to help, but don't have your knowledge of what you want!

 

[dunmore83]

No not p'd off.. just trying to get to the point where we are both on the same page. Sorry if it came across that way.

Will post another file shortly to try to explain it more clearly. Thanks

 

[gaz_chops]

Enter the numbers manually that you expect to see, then I will try and provide the correct formula.

 

[dunmore83]

So I made another sheet (Sheet 1) with a data dump from the PD-01-Dates worksheet.

In cells B1:K10 is the rationale I think you have applied to the formula for "straws used".

In cells U1:X10 is the way I would like it to work. This is pulling 2 examples out of the AI-02-Dates worksheet to show the rationale for ear tag numbers 946 & 980.

Thanks for your ongoing patience. Fingers crossed we can get on the same page with this one shortly!!

https://www.dropbox.com/s/vso4pzdqc0ivvp4/PD-Dates-Returned-3.xlsx?dl=0

 

[dunmore83]

I probably should mention again that your original formulas for E & P were correct. Sheet 1 is only to show how we arrive at the number of "Straws Used" for each tech

 

[gaz_chops]

I probably should mention again that your original formulas for E & P were correct. Sheet 1 is only to show how we arrive at the number of "Straws Used" for each tech

The original formulas where wrong, I was using the Name in Col C to match, which meant that the "E's" would be allocated to the Last Name, so if there where 3 insems and the last Name was Farm West and the previous 2 where David Sier, all 3 would be counted against Farm West.

I have added 2 new columns, for the 2 Tech's, which will count correctly.

Hopefully correct this time.

https://www.dropbox.com/s/98mbu4i0w9m57om/PD-DatesV2.xlsx?dl=0