Can I accurately calculate these costs in the data provided

[Hwatsonnn]

[TABLE="width: 1176"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Feed Cost/Day Average[/TD]
[TD="colspan: 3"]Yearly Average Per Day[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD][/TD]
[TD]ON Date[/TD]
[TD][/TD]
[TD]OFF Date[/TD]
[TD][/TD]
[TD]Days ON[/TD]
[TD][/TD]
[TD]Feed Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD] € 1.10[/TD]
[TD][/TD]
[TD][/TD]
[TD] € 1.36[/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]
[TD][/TD]
[TD]Feb[/TD]
[TD] € 1.24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]10/01/2018[/TD]
[TD][/TD]
[TD="align: right"]20/07/2018[/TD]
[TD][/TD]
[TD="align: right"]191[/TD]
[TD][/TD]
[TD] € 259.76[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD] € 1.32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]10/01/2018[/TD]
[TD][/TD]
[TD="align: right"]20/07/2018[/TD]
[TD][/TD]
[TD="align: right"]191[/TD]
[TD][/TD]
[TD] € 259.76[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD] € 1.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]12/02/2018[/TD]
[TD][/TD]
[TD="align: right"]14/08/2018[/TD]
[TD][/TD]
[TD="align: right"]183[/TD]
[TD][/TD]
[TD] € 248.88[/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD] € 1.56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]12/02/2018[/TD]
[TD][/TD]
[TD="align: right"]07/08/2018[/TD]
[TD][/TD]
[TD="align: right"]176[/TD]
[TD][/TD]
[TD] € 239.36[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD] € 1.25[/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]
[TD][/TD]
[TD]Jul[/TD]
[TD] € 1.32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]05/04/2018[/TD]
[TD][/TD]
[TD="align: right"]14/11/2018[/TD]
[TD][/TD]
[TD="align: right"]223[/TD]
[TD][/TD]
[TD] € 303.28[/TD]
[TD][/TD]
[TD][/TD]
[TD]Aug[/TD]
[TD] € 1.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]10/04/2018[/TD]
[TD][/TD]
[TD="align: right"]20/11/2018[/TD]
[TD][/TD]
[TD="align: right"]224[/TD]
[TD][/TD]
[TD] € 304.64[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sep[/TD]
[TD] € 1.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]06/08/2018[/TD]
[TD][/TD]
[TD="align: right"]25/12/2018[/TD]
[TD][/TD]
[TD="align: right"]141[/TD]
[TD][/TD]
[TD] € 191.76[/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD] € 1.30[/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]
[TD][/TD]
[TD]Nov[/TD]
[TD] € 1.27[/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]
[TD][/TD]
[TD]Dec[/TD]
[TD] € 1.68[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Currently my feed cost is calculated by multiplying days by the yearly average.
I was wondering whether I could accurately work out feed costs by only accounting for the feed costs in which the animal was consuming when on site.
E.g ID 3 Consumption between February and August.

 

[pedro-egoli]

Not sure if I am missing something however, if the "Feed cost " is an accurate one then this figure multiplied by no of days would give true "Feed cost " you seek i.e. ID3 =SUM183*1.56 gives $285.48

I assume the "yearly average per day" has been obtained by dividing total cost previous 12 months by 365 .

 

[Hwatsonnn]

Not sure if I am missing something however, if the "Feed cost " is an accurate one then this figure multiplied by no of days would give true "Feed cost " you seek i.e. ID3 =SUM183*1.56 gives $285.48

I assume the "yearly average per day" has been obtained by dividing total cost previous 12 months by 365 .

My Yearly Average is the sum of each months' average divided by 12.
I explained poorly, lets say for ID 1, it consumed feed between January and July. The true average for cost over those months would be $1.32. By adding those 7 month averages up, and dividing them by 7. If I used the yearly average I would be putting $1.36 a day as the cost. I was just wondering if this could be put into a formula to make my programme more accurate.
Thank you for replying.

 

[pedro-egoli]

How did you arrive at the "True Cost" of $1.32 ?
If this is the actual cost why use a yearly average to calculate "Feed cost".

 

[TheRobRush]

here you go man, see formulas below and attached screen shot



DAYS ON FORMULA

=IF(A2="","",DATEDIF(B2,C2,"d")+1)

FEED COST FORMULA

=IF(A2="","",SUM(H2*$G$2,I2*$G$3,J2*$G$4,K2*$G$5,L2*$G$6,M2*$G$7,N2*$G$8,O2*$G$9,P2*$G$10,Q2*$G$11,R2*$G$12,S2*$G$13))

DAYS PER MONTH FORMULA (FOR 3 CELLS SO YOU CAN SEE WHAT CHANGES WHEN IT MOVES)


CELL H2

=IF(A2="","",SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&IF(C2="",TODAY(),C2))),"mmm")=H$1)))

CELL H3

=IF(A3="","",SUMPRODUCT(--(TEXT(ROW(INDIRECT(B3&":"&IF(C3="",TODAY(),C3))),"mmm")=H$1)))

CELL I2

=IF(A2="","",SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&IF(C2="",TODAY(),C2))),"mmm")=I$1)))

SCREENSHOT

https://consulerellc-my.sharepoint....BKuGwj7uc5H3MBIrhy3xgNBYjdWaCXyNd9vA?e=mlX6FK

 

[TheRobRush]

Also, went ahead and uploaded that example file I made for you. I will leave it there until Friday in case you would like to download it and use as a template.

https://consulerellc-my.sharepoint....ZKhrhC4EvrGTsBtJj4KAMOVpcX5-arHMJpsQ?e=AvZBwa

 

[Hwatsonnn]

Also, went ahead and uploaded that example file I made for you. I will leave it there until Friday in case you would like to download it and use as a template.

https://consulerellc-my.sharepoint....ZKhrhC4EvrGTsBtJj4KAMOVpcX5-arHMJpsQ?e=AvZBwa

Thank you very much that is exactly what I was after! I have saved your template copy to my computer. Why is it that you have +1 on the Days On, Does DATEDIF exclude the start date?


Thanks again, very helpful.

 

[TheRobRush]

Why is it that you have +1 on the Days On, Does DATEDIF exclude the start date?

Yes Start is excluded