| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MONTH TO DATE ACROSS MULTIPLE COLUMNS & ROWS
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 . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MONTH TO DATE ACROSS MULTIPLE COLUMNS & ROWS
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 . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MONTH TO DATE ACROSS MULTIPLE COLUMNS & ROWS
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 . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MONTH TO DATE ACROSS MULTIPLE COLUMNS & ROWS
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 1 Column 'Date' to 3 Seperate Columns 'Month, Day, Year' How?? | Excel Discussion (Misc queries) | |||
| Separating dates from one column to Month, Date, Year columns | Excel Discussion (Misc queries) | |||
| Excel Convert Multiple Rows into Multiple Columns | Excel Worksheet Functions | |||
| Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
| Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Linear Mode
