| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum per row and Average per column from a second sheet.
OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum per row and Average per column from a second sheet.
Create column 5 in sheet one with the formula
IF(A1="",0,Sum(A2:A4)) Then copy that formula down To get the average write the formula in a cell =Sum(E1:E4)/Countif(E1:E4,"0") I'm getting kind of mixed up of how you have the numbers as your column headings and letters as rows. But E should be the fifth column. "Gregory Day" wrote: OK. I have 4 colums in my table on sheet 2. In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum per row and Average per column from a second sheet.
I appologise, I got my row and column identifiers reversed.
Your plan DID work, thank you. Is there anyway I can doe the whole operation in a singe cell? Just for my own curiosity. - Thank you! "akphidelt" wrote: Create column 5 in sheet one with the formula IF(A1="",0,Sum(A2:A4)) Then copy that formula down To get the average write the formula in a cell =Sum(E1:E4)/Countif(E1:E4,"0") I'm getting kind of mixed up of how you have the numbers as your column headings and letters as rows. But E should be the fifth column. "Gregory Day" wrote: OK. I have 4 colums in my table on sheet 2. In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum per row and Average per column from a second sheet.
Try this:
=SUMPRODUCT((A1:A4<"")*B1:D4)/COUNTA(A1:A4) -- Biff Microsoft Excel MVP "Gregory Day" wrote in message ... I appologise, I got my row and column identifiers reversed. Your plan DID work, thank you. Is there anyway I can doe the whole operation in a singe cell? Just for my own curiosity. - Thank you! "akphidelt" wrote: Create column 5 in sheet one with the formula IF(A1="",0,Sum(A2:A4)) Then copy that formula down To get the average write the formula in a cell =Sum(E1:E4)/Countif(E1:E4,"0") I'm getting kind of mixed up of how you have the numbers as your column headings and letters as rows. But E should be the fifth column. "Gregory Day" wrote: OK. I have 4 colums in my table on sheet 2. In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum per row and Average per column from a second sheet.
hahaha, there are things you can do... but some are more complicated then
just doing it the way you have it now. But here is a fairly complicated formula that would do what you are asking for with the data you presented =SUM(IF(LEN(A1:A4)0,B1:D4))/SUM(IF(LEN(A1:A4)0,1,0)) If you put the formula in to a cell... You HAVE to enter it by Holding down CTRL+SHIFT+ENTER Otherwise you will get an error. Let me know if that works. "Gregory Day" wrote: I appologise, I got my row and column identifiers reversed. Your plan DID work, thank you. Is there anyway I can doe the whole operation in a singe cell? Just for my own curiosity. - Thank you! "akphidelt" wrote: Create column 5 in sheet one with the formula IF(A1="",0,Sum(A2:A4)) Then copy that formula down To get the average write the formula in a cell =Sum(E1:E4)/Countif(E1:E4,"0") I'm getting kind of mixed up of how you have the numbers as your column headings and letters as rows. But E should be the fifth column. "Gregory Day" wrote: OK. I have 4 colums in my table on sheet 2. In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum per row and Average per column from a second sheet.
LOL. You win. My Brain just exploded.
I'll spend the rest of the year breaking that up and reverse enginerring it to wrap my head around it. ROFL. "akphidelt" wrote: hahaha, there are things you can do... but some are more complicated then just doing it the way you have it now. But here is a fairly complicated formula that would do what you are asking for with the data you presented =SUM(IF(LEN(A1:A4)0,B1:D4))/SUM(IF(LEN(A1:A4)0,1,0)) If you put the formula in to a cell... You HAVE to enter it by Holding down CTRL+SHIFT+ENTER Otherwise you will get an error. Let me know if that works. "Gregory Day" wrote: I appologise, I got my row and column identifiers reversed. Your plan DID work, thank you. Is there anyway I can doe the whole operation in a singe cell? Just for my own curiosity. - Thank you! "akphidelt" wrote: Create column 5 in sheet one with the formula IF(A1="",0,Sum(A2:A4)) Then copy that formula down To get the average write the formula in a cell =Sum(E1:E4)/Countif(E1:E4,"0") I'm getting kind of mixed up of how you have the numbers as your column headings and letters as rows. But E should be the fifth column. "Gregory Day" wrote: OK. I have 4 colums in my table on sheet 2. In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum per row and Average per column from a second sheet.
haha, yea that was quite a mess. If you look at T Valko's equation, that is a
much more user friendly equation that is much more reliable then mine, haha. Good luck! "Gregory Day" wrote: LOL. You win. My Brain just exploded. I'll spend the rest of the year breaking that up and reverse enginerring it to wrap my head around it. ROFL. "akphidelt" wrote: hahaha, there are things you can do... but some are more complicated then just doing it the way you have it now. But here is a fairly complicated formula that would do what you are asking for with the data you presented =SUM(IF(LEN(A1:A4)0,B1:D4))/SUM(IF(LEN(A1:A4)0,1,0)) If you put the formula in to a cell... You HAVE to enter it by Holding down CTRL+SHIFT+ENTER Otherwise you will get an error. Let me know if that works. "Gregory Day" wrote: I appologise, I got my row and column identifiers reversed. Your plan DID work, thank you. Is there anyway I can doe the whole operation in a singe cell? Just for my own curiosity. - Thank you! "akphidelt" wrote: Create column 5 in sheet one with the formula IF(A1="",0,Sum(A2:A4)) Then copy that formula down To get the average write the formula in a cell =Sum(E1:E4)/Countif(E1:E4,"0") I'm getting kind of mixed up of how you have the numbers as your column headings and letters as rows. But E should be the fifth column. "Gregory Day" wrote: OK. I have 4 colums in my table on sheet 2. In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
| Sum/average numbers in column A dependant on value in column B | Excel Worksheet Functions | |||
| in the average sum etc at bottom of sheet add a custom tab | Excel Worksheet Functions | |||
| Column chart with additional "average" column | Charts and Charting in Excel | |||
| I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions | |||
Hybrid Mode
