Hello,
I have a data set as following
<colgroup><col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706; width:56pt" span="12" width="74"> </colgroup><tbody>
</tbody>
Automatic YTD formula ???
Lets assume in A1 cell I have a dropdown of numbers from 1-12, if 7 is selected, the formula will take the the sum of only from Jan/12 to Jul/12 or if 4 is selected formula will only take the sum of Jan/12 to Apr/12 and so on...
Rolling 12 months ???
Same principle, if 7 is selected in cell A1, the formula will take the sum Jul/12 back to Jun/11, in other words sum of previous 12 rows starting from the Jul/12 and so on....
I thought offset could work, but apparaently I make something wrong in the formula!
=SUM(OFFSET(B1,COUNTIF(B1:B12,A1)-12,0,12,1))
I will be very happy if someone could help me out!
thank you
Aude
I have a data set as following
Jan/11 | Feb/11 | Mar/11 | Apr/11 | May/11 | Jun/11 | Jul/11 | Aug/11 | Sep/11 | Oct/11 | Nov/11 | Dec/11 | Jan/12 | Feb/12 | Mar/12 | Apr/12 | May/12 | Jun/12 | Jul/12 | Aug/12 | Sep/12 | Oct/12 | Nov/12 | Dec/12 |
14,360 | 17,584 | 18,839 | 15,593 | 15,766 | 21,620 | 27,760 | 17,016 | 19,940 | 24,472 | 22,312 | 23,360 | 19,963 | 22,308 | 21,132 | 25,586 | 25,628 | 21,440 | 0 | 0 | 0 | 0 | 0 | 0 |
<colgroup><col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706;mso-outline-level: 1;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2706; width:56pt" span="12" width="74"> </colgroup><tbody>
</tbody>
Automatic YTD formula ???
Lets assume in A1 cell I have a dropdown of numbers from 1-12, if 7 is selected, the formula will take the the sum of only from Jan/12 to Jul/12 or if 4 is selected formula will only take the sum of Jan/12 to Apr/12 and so on...
Rolling 12 months ???
Same principle, if 7 is selected in cell A1, the formula will take the sum Jul/12 back to Jun/11, in other words sum of previous 12 rows starting from the Jul/12 and so on....
I thought offset could work, but apparaently I make something wrong in the formula!
=SUM(OFFSET(B1,COUNTIF(B1:B12,A1)-12,0,12,1))
I will be very happy if someone could help me out!
thank you
Aude