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 |
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) |