Formula to VBV - weeks of cover

[brazill23]

HI All,

Thank you for your help in advance.Im really struggle to translate one formula to the code. Below formula is calculating weeks of stock cover based on deliveries and demand. Unforgettably it have plenty of "if" slowing down the report. Im looking to translate this to VBA code. Is anyone who can support ?

=IFERROR(IF(K10>K11,K11/K10,IF(K10+L10>K11,1+(K11-K10)/L10,IF(K10+L10+M10>K11,2+(K11-K10-L10)/M10,IF(K10+L10+M10+N10>K11,3+(K11-K10-L10-M10)/N10,IF(K10+L10+M10+N10+O10>K11,4+(K11-K10-L10-M10-N10)/O10,IF(K10+L10+M10+N10+O10+P10>K11,5+(K11-K10-L10-M10-N10-O10)/P10,IF(K10+L10+M10+N10+O10+P10+Q10>K11,6+(K11-K10-L10-M10-N10-O10-P10)/Q10,IF(K10+L10+M10+N10+O10+P10+Q10+R10>K11,7+(K11-K10-L10-M10-N10-O10-P10-Q10)/R10,"8+")))))))),0)

[TABLE="width: 300"]
<tbody>[TR]
[TD]forecast[/TD]
[TD]0[/TD]
[TD]724[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]Open stock[/TD]
[TD]1443[/TD]
[TD]1443[/TD]
[TD]719[/TD]
[TD]1719[/TD]
[TD]1719[/TD]
[/TR]
[TR]
[TD]delivery[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]weeks of cover[/TD]
[TD]3.8[/TD]
[TD]2.8[/TD]
[TD]7.1[/TD]
[TD]6.1[/TD]
[TD]5.1[/TD]
[/TR]
</tbody>[/TABLE]


In weeks of cover I have above calcualtion. I was think to move to VBA code, but its above my coding skills

 

[Legacy 98055]

Hi.
Typically, as far as I understand, VBA is slower than the built in functions. Maybe a good place to start would be to break up your formula into smaller segments in separate ranges/cells. Please download the HTML helper or something and post a more complete example.