MONTH TO DATE ACROSS MULTIPLE COLUMNS & ROWS
Hi Carrach,
try this to total figures in column B :
=SUMPRODUCT(OFFSET(B$3:B$17,,,$N$2*3)*
(INT(ROW(OFFSET(B$3:B$17,,,$N$2*3))/3)=
ROW(OFFSET(B$3:B$17,,,$N$2*3))/3))
copied along to your other columns.
I'm not clear on what you mean by "Also need the targets to match the % in
column N."
Steve D.
"Carrach" wrote in message
...
Please could someone help with the spreadsheet.(example below) in excel
2003
I have removed links so the formulaes have dissapeared but the info is the
same.
I have two sections at the end - one is totals for year - one is year to
date. I need to get the year to date bit to show only the relevant totals
for the months up to the month number showing in N2. i.e if N2=1 then add
only April figures, if N2=6 then add April to Sept figures etc... Also
need
the targets to match the % in column N.
I have removed some info so the totals are not correct.
A B C D E L M N
service name End-Month month no: 1
Fred A George B David C 1 11% 11%
April Prediction £0 £0 £2,000 2 10% 21%
Actual £0 £0 £2,000 3 11% 32%
% correct 0% 0% 100% 4 10% 42%
May Prediction £8,500 £3,000 £0 5 11% 53%
Actual £0 £0 £0 6 10% 63%
% correct 0% 0% 0% 7 11% 74%
June Prediction £0 £1,000 £1,000 8 10% 84%
Actual £0 £0 £0 9 11% 95%
% correct 0% 0% 0% 10 3% 98%
July Prediction £0 £0 £0 11 1% 99%
Actual £0 £0 £0 12 1% 100%
% correct 0% 0% 0% 100% £16,000
March Prediction
Actual £0 £0 £0
% correct 0% 0% 0%
Total for month to date Fred A George B David C
YTD Target £12,000 £8,000 £16,000
Prediction £0 £0 £2,000
Actual £0 £0 £2,000
% correct 0% 0% 67%
Total for year Fred A George B David C
Totals for year Target £12,000 £8,000 £16,000
Prediction £20,500 £5,000 £4,000
Actual £0 £0 £2,000
% correct 0% 0% 50%
--
any help gratefully received
thanks
carrach
|