Can I accurately calculate these costs in the data provided

[Hwatsonnn]

												Feed Cost/Day Average	Yearly Average Per Day
ID		ON Date		OFF Date		Days ON		Feed Cost			Jan	€ 1.10			€ 1.36
											Feb	€ 1.24
1		10/01/2018		20/07/2018		191		€ 259.76			Mar	€ 1.32
2		10/01/2018		20/07/2018		191		€ 259.76			Apr	€ 1.45
3		12/02/2018		14/08/2018		183		€ 248.88			May	€ 1.56
4		12/02/2018		07/08/2018		176		€ 239.36			Jun	€ 1.25
											Jul	€ 1.32
5		05/04/2018		14/11/2018		223		€ 303.28			Aug	€ 1.45
6		10/04/2018		20/11/2018		224		€ 304.64			Sep	€ 1.38
7		06/08/2018		25/12/2018		141		€ 191.76			Oct	€ 1.30
											Nov	€ 1.27
											Dec	€ 1.68

<tbody>
</tbody>

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