Shorter Formula

[Amit Punia]

I'm having a formula which is bit lengthy. It is based on time period and percentages.

Need a shorter formula or a more robust formula for the attached file. Point to remember - in the formula, sum(C5) is moving for only 4 quarters. If you check the formula in cell G6, it will move to SUM(D5:G5). Just to keep this point in mind and suggest a shorter formula.

 

[bosco_yip]

Try,

C6, copied across

=IF((C3<=$B$12)*(MOD(C4,12)=0),SUM(OFFSET(C5,,,,-4))*$B$11,0)

Regards
Bosco

 

[Amit Punia]

Thanks @bosco_yip , the formula is working absolutely fine. Though I didn't understand what is the use of -4 for width in offset function. I evaluated the formula using F9 key and it is showing the sum of last 4 cells. It is working absolutely fine but still wondering how this happened with width. Thanks a lot once again

 

[rahulshewale1]

Hii @Amit Punia ,

Try below formala ,

=IF(AND(C3<=$B$12,OR(C4={12,24,36,48})),SUM($C$5:C5)*$B$11,0)

http://www.contextures.com/exceloffsetfunction.html

http://www.excel-easy.com/examples/offset.html



Regard
Rahul shewale

 

[Amit Punia]

@rahulshewale1 - Understood the whole syntax of offset now. Thank you so much