MONTH TO DATE ACROSS MULTIPLE COLUMNS & ROWS

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

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

Hi Steve,
thanks for the help, but I cant work out how this formulae would work.
Column B contains 3 headings per month - N2 contains the month number from
April to March - % in column N is the % total that is the target year to date
for that month.
if N2=1, I need the totals for each of the rows in April only (3 different
rows) columns C to E. Target should be the matching % in column N which for
month 1 is 11%.
if N2 = 6, I need the totals for April to Sept incl Columns C to E (totals
of row 4,7,10,13,16,19; and totals of row 5,8,11,14,17,20; and totals of rows
6,9,12,15,18,21) Target should be the matching % in column N which for month
6 is 63%.
Does that help at all?
Is there a way to attach an example spreadsheet to this thread??
--
any help gratefully received
thanks
carrach


"Steve Dunn" wrote:

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

.

Hi Carrach,

I think the confusion lies in my reading of the example data you provided,
it looked like your first column of figures was B, not C. Try the formula
in column C, with all references to B changed.

If you still can't get it to work, send your workbook to me, remembering to
mention what totals you expect to get from the formulae:



"Carrach" wrote in message
...
Hi Steve,
thanks for the help, but I cant work out how this formulae would work.
Column B contains 3 headings per month - N2 contains the month number from
April to March - % in column N is the % total that is the target year to
date
for that month.
if N2=1, I need the totals for each of the rows in April only (3 different
rows) columns C to E. Target should be the matching % in column N which
for
month 1 is 11%.
if N2 = 6, I need the totals for April to Sept incl Columns C to E (totals
of row 4,7,10,13,16,19; and totals of row 5,8,11,14,17,20; and totals of
rows
6,9,12,15,18,21) Target should be the matching % in column N which for
month
6 is 63%.
Does that help at all?
Is there a way to attach an example spreadsheet to this thread??
--
any help gratefully received
thanks
carrach


"Steve Dunn" wrote:

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

.

Hi Carrach,

In E42:

=E48*INDEX($T$3:$T$14,MATCH($T$2,$R$3:$R$14,0))

In E43 (just needed a little bit of tweaking for the location of your data):

=SUMPRODUCT(OFFSET(E4:E18,,,$T$2*3)*
(INT((ROW(OFFSET(E4:E18,,,$T$2*3))-ROW(E4)+3)/3)=
(ROW(OFFSET(E4:E18,,,$T$2*3))-ROW(E4)+3)/3))

copied to E44

Copy E42:E44 along F42:K44

Steve D.


"Steve Dunn" wrote in message
...
Hi Carrach,

I think the confusion lies in my reading of the example data you provided,
it looked like your first column of figures was B, not C. Try the formula
in column C, with all references to B changed.

If you still can't get it to work, send your workbook to me, remembering
to mention what totals you expect to get from the formulae:



"Carrach" wrote in message
...
Hi Steve,
thanks for the help, but I cant work out how this formulae would work.
Column B contains 3 headings per month - N2 contains the month number
from
April to March - % in column N is the % total that is the target year to
date
for that month.
if N2=1, I need the totals for each of the rows in April only (3
different
rows) columns C to E. Target should be the matching % in column N which
for
month 1 is 11%.
if N2 = 6, I need the totals for April to Sept incl Columns C to E
(totals
of row 4,7,10,13,16,19; and totals of row 5,8,11,14,17,20; and totals of
rows
6,9,12,15,18,21) Target should be the matching % in column N which for
month
6 is 63%.
Does that help at all?
Is there a way to attach an example spreadsheet to this thread??
--
any help gratefully received
thanks
carrach


"Steve Dunn" wrote:

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

.

WOW; thank you so so so much steve
Forever in your debt
thanks
carrach


"Steve Dunn" wrote:

Hi Carrach,

In E42:

=E48*INDEX($T$3:$T$14,MATCH($T$2,$R$3:$R$14,0))

In E43 (just needed a little bit of tweaking for the location of your data):

=SUMPRODUCT(OFFSET(E4:E18,,,$T$2*3)*
(INT((ROW(OFFSET(E4:E18,,,$T$2*3))-ROW(E4)+3)/3)=
(ROW(OFFSET(E4:E18,,,$T$2*3))-ROW(E4)+3)/3))

copied to E44

Copy E42:E44 along F42:K44

Steve D.


"Steve Dunn" wrote in message
...
Hi Carrach,

I think the confusion lies in my reading of the example data you provided,
it looked like your first column of figures was B, not C. Try the formula
in column C, with all references to B changed.

If you still can't get it to work, send your workbook to me, remembering
to mention what totals you expect to get from the formulae:



"Carrach" wrote in message
...
Hi Steve,
thanks for the help, but I cant work out how this formulae would work.
Column B contains 3 headings per month - N2 contains the month number
from
April to March - % in column N is the % total that is the target year to
date
for that month.
if N2=1, I need the totals for each of the rows in April only (3
different
rows) columns C to E. Target should be the matching % in column N which
for
month 1 is 11%.
if N2 = 6, I need the totals for April to Sept incl Columns C to E
(totals
of row 4,7,10,13,16,19; and totals of row 5,8,11,14,17,20; and totals of
rows
6,9,12,15,18,21) Target should be the matching % in column N which for
month
6 is 63%.
Does that help at all?
Is there a way to attach an example spreadsheet to this thread??
--
any help gratefully received
thanks
carrach


"Steve Dunn" wrote:

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

.

You're welcome, but forever is a very long time! Just a century would have
done...


"Carrach" wrote in message
...
WOW; thank you so so so much steve
Forever in your debt
thanks
carrach


"Steve Dunn" wrote:

Hi Carrach,

In E42:

=E48*INDEX($T$3:$T$14,MATCH($T$2,$R$3:$R$14,0))

In E43 (just needed a little bit of tweaking for the location of your
data):

=SUMPRODUCT(OFFSET(E4:E18,,,$T$2*3)*
(INT((ROW(OFFSET(E4:E18,,,$T$2*3))-ROW(E4)+3)/3)=
(ROW(OFFSET(E4:E18,,,$T$2*3))-ROW(E4)+3)/3))

copied to E44

Copy E42:E44 along F42:K44

Steve D.


"Steve Dunn" wrote in message
...
Hi Carrach,

I think the confusion lies in my reading of the example data you
provided,
it looked like your first column of figures was B, not C. Try the
formula
in column C, with all references to B changed.

If you still can't get it to work, send your workbook to me,
remembering
to mention what totals you expect to get from the formulae:




"Carrach" wrote in message
...
Hi Steve,
thanks for the help, but I cant work out how this formulae would work.
Column B contains 3 headings per month - N2 contains the month number
from
April to March - % in column N is the % total that is the target year
to
date
for that month.
if N2=1, I need the totals for each of the rows in April only (3
different
rows) columns C to E. Target should be the matching % in column N
which
for
month 1 is 11%.
if N2 = 6, I need the totals for April to Sept incl Columns C to E
(totals
of row 4,7,10,13,16,19; and totals of row 5,8,11,14,17,20; and totals
of
rows
6,9,12,15,18,21) Target should be the matching % in column N which for
month
6 is 63%.
Does that help at all?
Is there a way to attach an example spreadsheet to this thread??
--
any help gratefully received
thanks
carrach


"Steve Dunn" wrote:

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

.