Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging range across sheets after transforming text to value
I have a number of sheets, where each sheet will be "owned" by a person who
will enter data in that sheet. I've used data validation and lists wherever possible to make it easy to pull summary information together. Unfortunately, I have one area where the source list is text and I need to pull summary data. Although the text has to be part of the list, I prefixed the text with a numeric representation as follows: 1. high school 2. BA/BS 3. MA/MS 4. Ph.D./JD Now I need to pull together a representation of what was entered in each sheet, so I try to grab the leftmost char (1 to 4) and average them. However, this gives me an error, presumably because it is always likely that at least one sheet will be blank, so it can't grab the leftmost char to average it. For example, if sheet1 has "2. BA/BS" and sheet2 has "3. MA/MS", and sheet3 doesn't have anything selected (empty cell) I want my formula to return a value of 2.5 my current formula is: (entered as regular or array, neither works) =AVERAGE(VALUE(LEFT(Sheet1:Sheet3!D27,1))) I also tried: (regular and array) =AVERAGE(IF(LEN(Kevin:Catherine!D27)0,VALUE(LEFT( Kevin:Catherine!D27,1)),0)) but not only does it not work, I think that averaging the last zero is misrepresenting- I don't want to stick a zero value in where no entry was made, I just want to average the entries that were made. I appreciate any help you can give me! Thanks, Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging range across sheets after transforming text to value
One way to avoid including 0 values in average calculations is to combine
SUMIF and COUNTIF. Since average is defined as the sum of the items in a set divided by the count of the items in the set, doing something like =SUM(A1:A10)/COUNTIF(A1:A10,0) will sum the range and divide by the number of items greater than 0. Dave -- Brevity is the soul of wit. "Keith" wrote: I have a number of sheets, where each sheet will be "owned" by a person who will enter data in that sheet. I've used data validation and lists wherever possible to make it easy to pull summary information together. Unfortunately, I have one area where the source list is text and I need to pull summary data. Although the text has to be part of the list, I prefixed the text with a numeric representation as follows: 1. high school 2. BA/BS 3. MA/MS 4. Ph.D./JD Now I need to pull together a representation of what was entered in each sheet, so I try to grab the leftmost char (1 to 4) and average them. However, this gives me an error, presumably because it is always likely that at least one sheet will be blank, so it can't grab the leftmost char to average it. For example, if sheet1 has "2. BA/BS" and sheet2 has "3. MA/MS", and sheet3 doesn't have anything selected (empty cell) I want my formula to return a value of 2.5 my current formula is: (entered as regular or array, neither works) =AVERAGE(VALUE(LEFT(Sheet1:Sheet3!D27,1))) I also tried: (regular and array) =AVERAGE(IF(LEN(Kevin:Catherine!D27)0,VALUE(LEFT( Kevin:Catherine!D27,1)),0)) but not only does it not work, I think that averaging the last zero is misrepresenting- I don't want to stick a zero value in where no entry was made, I just want to average the entries that were made. I appreciate any help you can give me! Thanks, Keith |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging range across sheets after transforming text to value
Sweet- thanks!
Keith "Dave F" wrote in message ... One way to avoid including 0 values in average calculations is to combine SUMIF and COUNTIF. Since average is defined as the sum of the items in a set divided by the count of the items in the set, doing something like =SUM(A1:A10)/COUNTIF(A1:A10,0) will sum the range and divide by the number of items greater than 0. Dave -- Brevity is the soul of wit. "Keith" wrote: I have a number of sheets, where each sheet will be "owned" by a person who will enter data in that sheet. I've used data validation and lists wherever possible to make it easy to pull summary information together. Unfortunately, I have one area where the source list is text and I need to pull summary data. Although the text has to be part of the list, I prefixed the text with a numeric representation as follows: 1. high school 2. BA/BS 3. MA/MS 4. Ph.D./JD Now I need to pull together a representation of what was entered in each sheet, so I try to grab the leftmost char (1 to 4) and average them. However, this gives me an error, presumably because it is always likely that at least one sheet will be blank, so it can't grab the leftmost char to average it. For example, if sheet1 has "2. BA/BS" and sheet2 has "3. MA/MS", and sheet3 doesn't have anything selected (empty cell) I want my formula to return a value of 2.5 my current formula is: (entered as regular or array, neither works) =AVERAGE(VALUE(LEFT(Sheet1:Sheet3!D27,1))) I also tried: (regular and array) =AVERAGE(IF(LEN(Kevin:Catherine!D27)0,VALUE(LEFT( Kevin:Catherine!D27,1)),0)) but not only does it not work, I think that averaging the last zero is misrepresenting- I don't want to stick a zero value in where no entry was made, I just want to average the entries that were made. I appreciate any help you can give me! Thanks, Keith |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging range across sheets after transforming text to value - didn't quite work
I guess I don't have it yet.... :(
This is very close: =SUM(VALUE(LEFT(Sheet1:Sheet3!D27,1)))/COUNTA(Sheet1:Sheet3!D27) but it the first part of the formula is not evaluating properly, e.g. =SUM(VALUE(LEFT(Sheet1!D27,1))) returns the value of 3 (as expected) but =SUM(VALUE(LEFT(Sheet1:Sheet3!D27,1))) still returns a #REF, because the blank cells (currently in sheet2 and sheet3) can't return a left character, which must return an error instead of a "non-value" that could be ignored by the value/sum statement I've tried putting in an if statement, which I can then expand to include the formula above (limiting it to only cells with contents) =SUM(IF(LEN(sheet1:sheet3!E27)0,1,0)) but it gives a #REF error. Interestingly, using the same formula with only one sheet (with or without a value) returns either 0 or 1 as expected =SUM(IF(LEN(sheet1!E27)0,1,0)) =1 ("3. MA/MS") =SUM(IF(LEN(sheet3!E27)0,1,0)) =0 (blank cell) but it won't sum these values when I try to use the range reference across the three sheets [=SUM(IF(LEN(sheet1:sheet3!E27)0,1,0))], either as a regular or array formula. Is there a better way for me to approach this? Many thanks, Keith "Dave F" wrote in message ... One way to avoid including 0 values in average calculations is to combine SUMIF and COUNTIF. Since average is defined as the sum of the items in a set divided by the count of the items in the set, doing something like =SUM(A1:A10)/COUNTIF(A1:A10,0) will sum the range and divide by the number of items greater than 0. Dave -- Brevity is the soul of wit. "Keith" wrote: I have a number of sheets, where each sheet will be "owned" by a person who will enter data in that sheet. I've used data validation and lists wherever possible to make it easy to pull summary information together. Unfortunately, I have one area where the source list is text and I need to pull summary data. Although the text has to be part of the list, I prefixed the text with a numeric representation as follows: 1. high school 2. BA/BS 3. MA/MS 4. Ph.D./JD Now I need to pull together a representation of what was entered in each sheet, so I try to grab the leftmost char (1 to 4) and average them. However, this gives me an error, presumably because it is always likely that at least one sheet will be blank, so it can't grab the leftmost char to average it. For example, if sheet1 has "2. BA/BS" and sheet2 has "3. MA/MS", and sheet3 doesn't have anything selected (empty cell) I want my formula to return a value of 2.5 my current formula is: (entered as regular or array, neither works) =AVERAGE(VALUE(LEFT(Sheet1:Sheet3!D27,1))) I also tried: (regular and array) =AVERAGE(IF(LEN(Kevin:Catherine!D27)0,VALUE(LEFT( Kevin:Catherine!D27,1)),0)) but not only does it not work, I think that averaging the last zero is misrepresenting- I don't want to stick a zero value in where no entry was made, I just want to average the entries that were made. I appreciate any help you can give me! Thanks, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
Using text for the range in AVERAGE function | Excel Worksheet Functions | |||
number range returning a text value | New Users to Excel | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
25 days previous to today? | Excel Discussion (Misc queries) |