Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using on one list derive from another
I need to write a formula to operate on numeric values from column B in the
contiguous range of all rows where column A has a specific string value. Column A is sorted on that string value, ascending. I'm using Excel from Office Pro 2000. I'm beginning to think that I need to write some kind of private functions to find the first and last rows where column A has a specific value. Before I perused this forum this morning, I wasn't even aware of private functions. -- Mike Scott |
#2
|
|||
|
|||
Hi
if you want for example sum column B try: =SUMIF(A:A,"your string",B:B) You may explain WHAT you want to do with the values in column B exactly -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... I need to write a formula to operate on numeric values from column B in the contiguous range of all rows where column A has a specific string value. Column A is sorted on that string value, ascending. I'm using Excel from Office Pro 2000. I'm beginning to think that I need to write some kind of private functions to find the first and last rows where column A has a specific value. Before I perused this forum this morning, I wasn't even aware of private functions. -- Mike Scott |
#3
|
|||
|
|||
Thanks, Frank, but I did peruse the list of functions and I was aware of
SUMIF--I don't need to sum values from column B. I need to do some simple statistical calculations on those ranges: AVERAGE( ) (which I _could_ get using SUMIF( )), MIN( ), MAX( ), STDDEV( ). If possible, it'd be nice if I just had the range to work with in general. -- Mike Scott "Frank Kabel" wrote: Hi if you want for example sum column B try: =SUMIF(A:A,"your string",B:B) You may explain WHAT you want to do with the values in column B exactly -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... I need to write a formula to operate on numeric values from column B in the contiguous range of all rows where column A has a specific string value. Column A is sorted on that string value, ascending. I'm using Excel from Office Pro 2000. I'm beginning to think that I need to write some kind of private functions to find the first and last rows where column A has a specific value. Before I perused this forum this morning, I wasn't even aware of private functions. -- Mike Scott |
#4
|
|||
|
|||
Hi
use for example the following array formula (entered with CTRL+SHIFT+ENTER): =AVERAGE(IF(A1:A100="your text",B1:B100)) -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... Thanks, Frank, but I did peruse the list of functions and I was aware of SUMIF--I don't need to sum values from column B. I need to do some simple statistical calculations on those ranges: AVERAGE( ) (which I _could_ get using SUMIF( )), MIN( ), MAX( ), STDDEV( ). If possible, it'd be nice if I just had the range to work with in general. -- Mike Scott "Frank Kabel" wrote: Hi if you want for example sum column B try: =SUMIF(A:A,"your string",B:B) You may explain WHAT you want to do with the values in column B exactly -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... I need to write a formula to operate on numeric values from column B in the contiguous range of all rows where column A has a specific string value. Column A is sorted on that string value, ascending. I'm using Excel from Office Pro 2000. I'm beginning to think that I need to write some kind of private functions to find the first and last rows where column A has a specific value. Before I perused this forum this morning, I wasn't even aware of private functions. -- Mike Scott |
#5
|
|||
|
|||
Thanks. That worked as far as it goes.
Can you further tell me how I could get, for the array returned by "IF(A1:A100="your text",B1:B100)" an array of those same values which are <= some other scalar? The IF() function returns an array, right? How can I produce an array which is a subset of that array, <= to some value? (I'm a software engineer of 25 years experience; it irks me to be reduced to helplessness when trying to make this tool some little thing. The help file and such documentation as I can find online are horrible. Oh well--you can't be an expert in everything). "Frank Kabel" wrote: Hi use for example the following array formula (entered with CTRL+SHIFT+ENTER): =AVERAGE(IF(A1:A100="your text",B1:B100)) -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... Thanks, Frank, but I did peruse the list of functions and I was aware of SUMIF--I don't need to sum values from column B. I need to do some simple statistical calculations on those ranges: AVERAGE( ) (which I _could_ get using SUMIF( )), MIN( ), MAX( ), STDDEV( ). If possible, it'd be nice if I just had the range to work with in general. -- Mike Scott "Frank Kabel" wrote: Hi if you want for example sum column B try: =SUMIF(A:A,"your string",B:B) You may explain WHAT you want to do with the values in column B exactly -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... I need to write a formula to operate on numeric values from column B in the contiguous range of all rows where column A has a specific string value. Column A is sorted on that string value, ascending. I'm using Excel from Office Pro 2000. I'm beginning to think that I need to write some kind of private functions to find the first and last rows where column A has a specific value. Before I perused this forum this morning, I wasn't even aware of private functions. -- Mike Scott |
#6
|
|||
|
|||
Hi
do you mean =AVERAGE(IF((A1:A100="your text")*(B1:B100<=100),B1:B100)) Also see: http://www.dicks-blog.com/archives/2...las/trackback/ -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... Thanks. That worked as far as it goes. Can you further tell me how I could get, for the array returned by "IF(A1:A100="your text",B1:B100)" an array of those same values which are <= some other scalar? The IF() function returns an array, right? How can I produce an array which is a subset of that array, <= to some value? (I'm a software engineer of 25 years experience; it irks me to be reduced to helplessness when trying to make this tool some little thing. The help file and such documentation as I can find online are horrible. Oh well--you can't be an expert in everything). "Frank Kabel" wrote: Hi use for example the following array formula (entered with CTRL+SHIFT+ENTER): =AVERAGE(IF(A1:A100="your text",B1:B100)) -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... Thanks, Frank, but I did peruse the list of functions and I was aware of SUMIF--I don't need to sum values from column B. I need to do some simple statistical calculations on those ranges: AVERAGE( ) (which I _could_ get using SUMIF( )), MIN( ), MAX( ), STDDEV( ). If possible, it'd be nice if I just had the range to work with in general. -- Mike Scott "Frank Kabel" wrote: Hi if you want for example sum column B try: =SUMIF(A:A,"your string",B:B) You may explain WHAT you want to do with the values in column B exactly -- Regards Frank Kabel Frankfurt, Germany "mikeyts" schrieb im Newsbeitrag ... I need to write a formula to operate on numeric values from column B in the contiguous range of all rows where column A has a specific string value. Column A is sorted on that string value, ascending. I'm using Excel from Office Pro 2000. I'm beginning to think that I need to write some kind of private functions to find the first and last rows where column A has a specific value. Before I perused this forum this morning, I wasn't even aware of private functions. -- Mike Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging two lists | Excel Discussion (Misc queries) | |||
Auto scroll down data validation list | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |