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]

Excellent!!

There needs to be a slight change to the formula in cell S8 & S9 on the PD-01-Dates worksheet.... it is often that the tech name for the straws will be different if there are more than 1 inseminations in column G. I need the formula in cell S8/S9 to only return the straws used by that actual tech name

for example cow 84 had an insemination on 08-May-15 by David Sier & then one on 01-Jun-15 by Farm West... the formula in cell S9 for David Sier should only count 1 of these inseminations.

Any ideas???

I haven't had the chance to look at the other file yet

 

[gaz_chops]

So you only want to count the latest insem for the relevant tech?

 

[dunmore83]

Not quite... all the insems that qualify during the timeframe however 1 individual cow can have insems from different techs if inseminated more than once.

Does that make sense? Did your original formula account for this?

 

[gaz_chops]

The formula sums ALL insems based on the last Tech, regardless of who the previous Tech was.

So if there are 3 insems
2 X David Sier
1 X Farm West

Farm West is the latest, so Farm West shows 3.

 

[dunmore83]

Thanks.

I need the formula to return only 1 for farm west in the above example. The 2 will need to be counted under David Sier.

Can you make this work?

 

[gaz_chops]

Currently the formula only looks for the Tech that did the latest insem, which is what you wanted. So if 3 insems have been done, 1st by Farm West then 2 by David Sier, then Farm West show none & David Sier shows 3, I can change it so Sier shows 2, but not sure about showing Farm West as 1.

 

[dunmore83]

I need it show 2 for David Sier & 1 for Farm West.

There must be a way??!!!!

 

[gaz_chops]

I need it show 2 for David Sier & 1 for Farm West.

There must be a way??!!!!

There is, but i'm confused by you saying "it should be the tech name of the most recent insemination date". You can't have both!

 

[dunmore83]

Ok. Will explain again.

The 2 options it relies on are P & E.

If it is a P then the most recent insemination is counted under the tech for that insemination. The other(s) for that P cow are assigned to whichever tech did them.

If there is an E in the column then each insemination needs to be counted individually for the tech that did them.

Make any sense?!!!

 

[gaz_chops]

So really you just want to return the count to the relevant Tech? The P & E are irrelevant.

e.g.

4 insems

1/6/15 Farm West - E
15/6/15 David Sier - E
30/6/15 Farm West - E
15/7/15 David Sier - P

This would return
David Sier = 2
Farm West = 2

If the last was E it would be the same answer.