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]

Found an error, it isn't counting just the "E" & "P"! Trying to fix it now.

 

[gaz_chops]

Fixed.

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

 

[dunmore83]

Thanks for the last file. Looks to be correct this time!!! Thanks for your patience.

One more request on this one... is there any possible way we can achieve the same outcome without columns A:D? It's just that it adds quite a bit of junk to the worksheet & also if other tech names are introduced it adds a degree of complexity to restructure the worksheet.

Just thought I would ask!!!

 

[gaz_chops]

I can't see a way without columns A:D, but I will investigate further.

You can hide them, to add a tech just insert a column on the left and copy and paste the formulas, then reference that range with the formulas in Straws Used, # In-Calf & # Empty.

 

[dunmore83]

I can hide them if you cannot find another way. No problems.

I am just transferring the formulas into my file.

Sorry to rehash it again!! The results in straws used cells V8:V10 are correct. However the cells in AB8:AB10 looking to be summing the total number of straws used if the cow is pregnant. This needs to only count the E once for each tech regardless of the number of straws used.

 

[gaz_chops]

AB8:AB10 counts the number of insems if Col Y = "E" or "P", then it deducts 1 if the name in Col D matches "-IF($D23=A$20,1,0),0),0)".
So e.g. Tag no. 436 is "P" it has 2 insems, 1 is counted against "P" for Farm West and 1 against David Sier as an "E".

 

[dunmore83]

I think this should do the trick

=SUMIFS(B23:B224,Y23:Y224,"E")

 

[gaz_chops]

Sorry I don't follow!
With your formula, If a cow is "P" and has had 3 insems, 2 "E" & 1 "P", your formula will ignore the 2 "E", mine doesn't!

 

[dunmore83]

I seem to be confusing myself now!!!

Not sure it needs the last modification now. Will leave it as it was in your last uploaded file.

Very happy with it. Thanks!!

 

[gaz_chops]

I followed your logic from your example, if a cows last insem is "P" and has 2 previous insems, then that = 1 "P" & 2 "E".

[TABLE="width: 277"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]# Of Straws[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Empty[/TD]
[TD]Preg[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David Sier[/TD]
[TD]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Farm West[/TD]
[TD]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Farm West[/TD]
[TD]David Sier[/TD]
[TD]David Sier[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08-May-15[/TD]
[TD]02-Jun-15[/TD]
[TD]14-Jul-15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Farm West[/TD]
[TD]David Sier[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Jun-15[/TD]
[TD]10-Jul-15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]