So here is what I am looking to do
I have a column of dates then a calculated number 1 2 or 3
I have test data and results,
I want to calculate the total for the year with an equation but I need the test date to be the latest one so here, 2/10/18 uses 3 then 6/2/18 uses 8
Equation is like this but I need to index somewhere
=90/60/60*(SUMIFS($B:$B,$A:$A,"<"&$C$2)*(D2*0.22+9.6*0.78)+SUMIFS($B:$B,$A:$A,">"&$C$3)*(D3*0.22+9.6*0.78))
Can anyone please help me so I can index this. The thing is for an entire year so there will be a cell somewhere that has 2018. Then I need the equation to work for all of column A for 2018 then I want the same equation to work for 2019 so I don't have to change any references. Please help if you can. Sorry the data is there 2 times, I coundt delete the 2nd set.
[TABLE="width: 326"]
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="64" style="width: 48pt;" span="3"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 111, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]date[/TD]
[TD="bgcolor: transparent"]calc[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]test[/TD]
[TD="bgcolor: transparent"]result[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/10/2018 6:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6/8/2017[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/2/2018 18:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5/9/2018[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 22:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7/12/2019[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 23:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 0:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 1:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 326"]
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"><colgroup><col width="64" style="width: 48pt;" span="3"><colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"><colgroup><col width="64" style="width: 48pt;"><tbody>[TR]
[TD="width: 111, bgcolor: transparent"]date[/TD]
[TD="width: 64, bgcolor: transparent"]calc[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"]test[/TD]
[TD="width: 64, bgcolor: transparent"]result[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/10/2018 6:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6/8/2017[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/2/2018 18:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5/9/2018[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 22:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7/12/2019[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 23:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 0:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 1:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
I have a column of dates then a calculated number 1 2 or 3
I have test data and results,
I want to calculate the total for the year with an equation but I need the test date to be the latest one so here, 2/10/18 uses 3 then 6/2/18 uses 8
Equation is like this but I need to index somewhere
=90/60/60*(SUMIFS($B:$B,$A:$A,"<"&$C$2)*(D2*0.22+9.6*0.78)+SUMIFS($B:$B,$A:$A,">"&$C$3)*(D3*0.22+9.6*0.78))
Can anyone please help me so I can index this. The thing is for an entire year so there will be a cell somewhere that has 2018. Then I need the equation to work for all of column A for 2018 then I want the same equation to work for 2019 so I don't have to change any references. Please help if you can. Sorry the data is there 2 times, I coundt delete the 2nd set.
[TABLE="width: 326"]
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="64" style="width: 48pt;" span="3"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 111, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]date[/TD]
[TD="bgcolor: transparent"]calc[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]test[/TD]
[TD="bgcolor: transparent"]result[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/10/2018 6:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6/8/2017[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/2/2018 18:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5/9/2018[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 22:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7/12/2019[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 23:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 0:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 1:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 326"]
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"><colgroup><col width="64" style="width: 48pt;" span="3"><colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"><colgroup><col width="64" style="width: 48pt;"><tbody>[TR]
[TD="width: 111, bgcolor: transparent"]date[/TD]
[TD="width: 64, bgcolor: transparent"]calc[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"]test[/TD]
[TD="width: 64, bgcolor: transparent"]result[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/10/2018 6:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6/8/2017[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/2/2018 18:00[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5/9/2018[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 22:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7/12/2019[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/2018 23:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 0:00[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2019 1:00[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]