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

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

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

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

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

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