How Do I

[Alan Bernardo]

Okay. I'm just now trying to learn a little more Excel. I'm trying to
figure how best to do the following. I know it might seem easy to some but
that's why I'm asking.

Here's essentially what I want to do.

Based on finding an exact match in one cell, I want to subtract two numbers
in cells from the same row that the exact match is found.

I then want to average out the number, based on how many times the exact
match is found and its corresponding sums.

For example:

Cell A1: 200
Cell B1: D1
Cell C1: 100

D1 is the exact match found, then 200-100=100, with the average of 100.

If the exact match is not found then of course no calculations are done.

To run this another round, I'd have

Cell A2: 500
Cell B2 : D1
Cell C2: 250

This would be D1 500-250 = 250

Add this to the first round and I'd have 2 occurrences of "D1" and 350 total
which would average out to 175.

I tried a bunch of things but my experience is limited so I'm looking for
some help.

Thanks,

Alanb

[Bob Phillips]

=AVERAGE(IF(B1:B10=D1,A1:A10+C1:C10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Alan Bernardo" <[email protected]> wrote in message
news:[email protected]...
> Okay. I'm just now trying to learn a little more Excel. I'm trying to
> figure how best to do the following. I know it might seem easy to some
but
> that's why I'm asking.
>
> Here's essentially what I want to do.
>
> Based on finding an exact match in one cell, I want to subtract two
numbers
> in cells from the same row that the exact match is found.
>
> I then want to average out the number, based on how many times the exact
> match is found and its corresponding sums.
>
> For example:
>
> Cell A1: 200
> Cell B1: D1
> Cell C1: 100
>
> D1 is the exact match found, then 200-100=100, with the average of 100.
>
> If the exact match is not found then of course no calculations are done.
>
> To run this another round, I'd have
>
> Cell A2: 500
> Cell B2 : D1
> Cell C2: 250
>
> This would be D1 500-250 = 250
>
> Add this to the first round and I'd have 2 occurrences of "D1" and 350
total
> which would average out to 175.
>
> I tried a bunch of things but my experience is limited so I'm looking for
> some help.
>
> Thanks,
>
> Alanb
>
>

[Alan Bernardo]

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
| =AVERAGE(IF(B1:B10=D1,A1:A10+C1:C10))
|
| which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
| just Enter.
|
| --
|
| HTH
|
| Bob Phillips
|
| (remove nothere from the email address if mailing direct)
|

Thanks, Bob. Now I know. It worked perfectly.

Alanb

| "Alan Bernardo" <[email protected]> wrote in message
| news:[email protected]...
| > Okay. I'm just now trying to learn a little more Excel. I'm trying to
| > figure how best to do the following. I know it might seem easy to some
| but
| > that's why I'm asking.
| >
| > Here's essentially what I want to do.
| >
| > Based on finding an exact match in one cell, I want to subtract two
| numbers
| > in cells from the same row that the exact match is found.
| >
| > I then want to average out the number, based on how many times the exact
| > match is found and its corresponding sums.
| >
| > For example:
| >
| > Cell A1: 200
| > Cell B1: D1
| > Cell C1: 100
| >
| > D1 is the exact match found, then 200-100=100, with the average of 100.
| >
| > If the exact match is not found then of course no calculations are done.
| >
| > To run this another round, I'd have
| >
| > Cell A2: 500
| > Cell B2 : D1
| > Cell C2: 250
| >
| > This would be D1 500-250 = 250
| >
| > Add this to the first round and I'd have 2 occurrences of "D1" and 350
| total
| > which would average out to 175.
| >
| > I tried a bunch of things but my experience is limited so I'm looking
for
| > some help.
| >
| > Thanks,
| >
| > Alanb
| >
| >
|
|