REQ: I need help working out this function.

[Matthew Webb]

I have a sheet with sales, stock and wks cover by 2 wks forward average
sales(asuming I don't get any more stock).

N.B. figures are actual and estimated.


Date Sales Stock wks cover
(K) (K)
wk1 200 800 3.5
wk2 220 750 2.9
wk3 240 720 2.4
wk4 280 760 2.2
wk5 310 790 2.1
wk6 390 840 2.5
wk7 350 890 2.9
wk8 320 940 -
wk9 300 900 -

What I need is to take away my sales estimates from my actual current
stock cover to give a better figure because I have big sales peeks.

Is this possible? Thanks.

 

[Katherine Coombs]

Matt,

No disrespect intended but I'm baffled as to what it is that you're trying
to do. I'm guessing that column B (Sales (K)) is your sales estimate and
you're trying to deduct that figure from the relevant Stock (K) amount. In
which case, you could insert a column between Stock (K) and wks cover and
use a simple deduction equation, eg =C1-B1 so forth??

Cheers,
Katherine

 

[J.E. McGimpsey]

If I understand you correctly, one way is:

D2: =IF(B4>0,C2/AVERAGE(B3:B4),"")

and copy down, so that for week 1,

wks cover = 800/230 = 3.5

and for wk7:

wks cover = 890/310 = 2.9

But since those values are what you've already got, I probably
haven't hit your requirements.