Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM of a 3D calculation
Hi,
I have a book with Sheet1 through Sheet10. On Sheet2 through Sheet10 there are numbers in cell "A1", "A2" and "A3". What I'd like to do is to calculate directly on Sheet1 the SUM of the single calculations on Sheet2 through Sheet10, which is "A1/A2*A3". It would be the result of SUM(Sheet2!A1/Sheet2!A2*Sheet2!A3, ...., Sheet10!A1/Sheet10!A2*Sheet10! A3). Yes, Ican calculate the formula on each sheet and use the SUM formula with a 3D reference, but this is not the solution I'm looking for. Is there an array formula, which can do that? Thanks. Excel XP SP3 WIN XP SP3 Regards Werner |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM of a 3D calculation
Hi,
I'm not sure you can easilly do the calculation on sheet 1 but you can do this =SUM(Sheet2:Sheet10!A4) Where A4 on each sheet contains the formula =A1/A2*A3 You don't need to use A4 it can be a cell out of the way somewhere. Mike "Werner Rohrmoser" wrote: Hi, I have a book with Sheet1 through Sheet10. On Sheet2 through Sheet10 there are numbers in cell "A1", "A2" and "A3". What I'd like to do is to calculate directly on Sheet1 the SUM of the single calculations on Sheet2 through Sheet10, which is "A1/A2*A3". It would be the result of SUM(Sheet2!A1/Sheet2!A2*Sheet2!A3, ...., Sheet10!A1/Sheet10!A2*Sheet10! A3). Yes, Ican calculate the formula on each sheet and use the SUM formula with a 3D reference, but this is not the solution I'm looking for. Is there an array formula, which can do that? Thanks. Excel XP SP3 WIN XP SP3 Regards Werner |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM of a 3D calculation
=SUM(Sheet2:Sheet10!A1:A3)
If the sheets are named differently, use the shift key {after =sum( } to group the sheets (Click Sheet2 press Shift - Click sheet10) and highlight cells A1:A3. They must be consecutive though. Eg: if you insert a sheet between Sheet2 & Sheet10 - and populate A1:A3 with a number, it will be included in the formula. Bam "Mike H" wrote: Hi, I'm not sure you can easilly do the calculation on sheet 1 but you can do this =SUM(Sheet2:Sheet10!A4) Where A4 on each sheet contains the formula =A1/A2*A3 You don't need to use A4 it can be a cell out of the way somewhere. Mike "Werner Rohrmoser" wrote: Hi, I have a book with Sheet1 through Sheet10. On Sheet2 through Sheet10 there are numbers in cell "A1", "A2" and "A3". What I'd like to do is to calculate directly on Sheet1 the SUM of the single calculations on Sheet2 through Sheet10, which is "A1/A2*A3". It would be the result of SUM(Sheet2!A1/Sheet2!A2*Sheet2!A3, ...., Sheet10!A1/Sheet10!A2*Sheet10! A3). Yes, Ican calculate the formula on each sheet and use the SUM formula with a 3D reference, but this is not the solution I'm looking for. Is there an array formula, which can do that? Thanks. Excel XP SP3 WIN XP SP3 Regards Werner |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM of a 3D calculation
Maybe try this:
=SUMPRODUCT(N(INDIRECT("sheet"&ROW(2:9)&"!a1"))/SUBTOTAL(6,INDIRECT("sheet"&ROW(2:9)&"!a2:a3"))) "Werner Rohrmoser" wrote: Hi, I have a book with Sheet1 through Sheet10. On Sheet2 through Sheet10 there are numbers in cell "A1", "A2" and "A3". What I'd like to do is to calculate directly on Sheet1 the SUM of the single calculations on Sheet2 through Sheet10, which is "A1/A2*A3". It would be the result of SUM(Sheet2!A1/Sheet2!A2*Sheet2!A3, ...., Sheet10!A1/Sheet10!A2*Sheet10! A3). Yes, Ican calculate the formula on each sheet and use the SUM formula with a 3D reference, but this is not the solution I'm looking for. Is there an array formula, which can do that? Thanks. Excel XP SP3 WIN XP SP3 Regards Werner |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM of a 3D calculation
Hi Lori,
I modified your approach a little bit, so that I've got now what I need. "Blaetter" is a named range, where I have stored all sheets which have to be processed. This is the sum of a multiplication of all D24 cells with all B81 cells devided by all B96 cells, let's say an operation with three arrays across the sheets contained in "Blaetter" and then totaled. =SUM(N(INDIRECT("'"&Blaetter&"'!D24"))*N(INDIRECT( "'"&Blaetter&"'! B81"))/N(INDIRECT("'"&Blaetter&"'!B96"))). Thank you to get me started. Werner |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM of a 3D calculation
You could also try Data / Consolidate.
Do a search in help for Consolidate and it shows you what's available. Excel2003. Bam. "Werner Rohrmoser" wrote: Hi Lori, I modified your approach a little bit, so that I've got now what I need. "Blaetter" is a named range, where I have stored all sheets which have to be processed. This is the sum of a multiplication of all D24 cells with all B81 cells devided by all B96 cells, let's say an operation with three arrays across the sheets contained in "Blaetter" and then totaled. =SUM(N(INDIRECT("'"&Blaetter&"'!D24"))*N(INDIRECT( "'"&Blaetter&"'! B81"))/N(INDIRECT("'"&Blaetter&"'!B96"))). Thank you to get me started. Werner |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM of a 3D calculation
Bam,
thanks for your contribution. Regards Werner |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation | Excel Discussion (Misc queries) | |||
Age Calculation | Excel Worksheet Functions | |||
calculation | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |