Need to bring back Average using either Sumproduct or CSE formula

[Jim May]

I have lines of data with 1 field ColB such as 01017340 (text) and
i have 50 lines all with 01017340 but in ColM 28.53%, an other 26.11%
and so on. The First two characters XX017340 and Compnay numbers
I have 25 companies and need the Average of each.
What formula could I use to acheive this?
TIA,
Jim

[Jim May]

correction,
I don't have 50 lines of 01017340, but rather 50 lines beginning with "01",
meaning 01XXXXXX, then I have 40 or 50 with 02XXXXXX.. Hope that helps
clarify.
Jim

"Jim May" wrote:

> I have lines of data with 1 field ColB such as 01017340 (text) and
> i have 50 lines all with 01017340 but in ColM 28.53%, an other 26.11%
> and so on. The First two characters XX017340 and Compnay numbers
> I have 25 companies and need the Average of each.
> What formula could I use to acheive this?
> TIA,
> Jim

[Bob Phillips]

Jim, Is this what you want

=AVERAGE(IF(RIGHT(B2:B50,6)="017340",M2:M50))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" <[email protected]> wrote in message
news:[email protected]...
> correction,
> I don't have 50 lines of 01017340, but rather 50 lines beginning with
"01",
> meaning 01XXXXXX, then I have 40 or 50 with 02XXXXXX.. Hope that helps
> clarify.
> Jim
>
> "Jim May" wrote:
>
> > I have lines of data with 1 field ColB such as 01017340 (text) and
> > i have 50 lines all with 01017340 but in ColM 28.53%, an other 26.11%
> > and so on. The First two characters XX017340 and Compnay numbers
> > I have 25 companies and need the Average of each.
> > What formula could I use to acheive this?
> > TIA,
> > Jim

[Jim May]

as modified:

=AVERAGE(IF(LEFT(CurrYrBase!$B$12:$B$996,2)=B7,CurrYrBase!$O$12:$O$996))

works perfect;
Tks Bob
Jim

"Bob Phillips" wrote:

> Jim, Is this what you want
>
> =AVERAGE(IF(RIGHT(B2:B50,6)="017340",M2:M50))
>
> which is an array formula, so commit with Ctrl-Shift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jim May" <[email protected]> wrote in message
> news:[email protected]...
> > correction,
> > I don't have 50 lines of 01017340, but rather 50 lines beginning with
> "01",
> > meaning 01XXXXXX, then I have 40 or 50 with 02XXXXXX.. Hope that helps
> > clarify.
> > Jim
> >
> > "Jim May" wrote:
> >
> > > I have lines of data with 1 field ColB such as 01017340 (text) and
> > > i have 50 lines all with 01017340 but in ColM 28.53%, an other 26.11%
> > > and so on. The First two characters XX017340 and Compnay numbers
> > > I have 25 companies and need the Average of each.
> > > What formula could I use to acheive this?
> > > TIA,
> > > Jim
>
>
>