Thread: Help on ifsum

As I said before

Re-cut the data like so, to cells M1:R8

Attendance 0.24 0.35 0.49 0.69 1
20%
39% 0.30 0.40
59% 0.40 0.50
79% 0.30 0.40 0.60 0.75
89% 0.50 0.75 1.00 2.00
99% 1.50 2.00 2.50 5.00
100% 3.00 4.00 5.00 10.00


and then in F1 use a formula of

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1


--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> I have a spreadsheet that does the following on a weekly basic,
>
> First Name Surname Total SPH Sales £ Attendance
> %
> Claire Jepson 32.00 0.37 10 £40.00 100
> David Thomas 33.25 0.51 17 £85.00 100
>
>
> What I would like is that where it has pound sign to auto sum from the
> following table, (SECOND TABLE)
>
> SPH
> Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> 20%
> 21-39% £0.30 £0.40
> 41-59% £0.40 £0.50
> 60-79% £0.30 £0.40 £0.60 £0.75
> 80-89% £0.50 £0.75 £1.00 £2.00
> 90-99% £1.50 £2.00 £2.50 £5.00
> 100% £3.00 £4.00 £5.00 £10.00
>
> How can I put a formula in the pound sign that if the following staff at
the
> end of the week have the following
>
> SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> THE SECOND TABLE.
>
> So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
>

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1

can you please let me what columns are the following,

GI/100= IS THATS THE TOTAL ATTENDANCE
*E1= IS THIS THE TOTAL SALES IS THAT RIGTH

THATS

"Bob Phillips" wrote:

> As I said before
>
> Re-cut the data like so, to cells M1:R8
>
> Attendance 0.24 0.35 0.49 0.69 1
> 20%
> 39% 0.30 0.40
> 59% 0.40 0.50
> 79% 0.30 0.40 0.60 0.75
> 89% 0.50 0.75 1.00 2.00
> 99% 1.50 2.00 2.50 5.00
> 100% 3.00 4.00 5.00 10.00
>
>
> and then in F1 use a formula of
>
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "TYE" <TYE@discussions.microsoft.com> wrote in message
> news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > I have a spreadsheet that does the following on a weekly basic,
> >
> > First Name Surname Total SPH Sales £ Attendance
> > %
> > Claire Jepson 32.00 0.37 10 £40.00 100
> > David Thomas 33.25 0.51 17 £85.00 100
> >
> >
> > What I would like is that where it has pound sign to auto sum from the
> > following table, (SECOND TABLE)
> >
> > SPH
> > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > 20%
> > 21-39% £0.30 £0.40
> > 41-59% £0.40 £0.50
> > 60-79% £0.30 £0.40 £0.60 £0.75
> > 80-89% £0.50 £0.75 £1.00 £2.00
> > 90-99% £1.50 £2.00 £2.50 £5.00
> > 100% £3.00 £4.00 £5.00 £10.00
> >
> > How can I put a formula in the pound sign that if the following staff at
> the
> > end of the week have the following
> >
> > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> > THE SECOND TABLE.
> >
> > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> >
>
>
>

That is correct

G1 is attendance (I divide by 100 as you show it as 100 not 100%, if it is a
percentage don't divide by 100)
D1 is the SPH
E1 is the sales figure

--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:3F6AD473-D083-4CF2-B92F-49AB0C57C8A0@microsoft.com...
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
> can you please let me what columns are the following,
>
> GI/100= IS THATS THE TOTAL ATTENDANCE
> *E1= IS THIS THE TOTAL SALES IS THAT RIGTH
>
> THATS
>
> "Bob Phillips" wrote:
>
> > As I said before
> >
> > Re-cut the data like so, to cells M1:R8
> >
> > Attendance 0.24 0.35 0.49 0.69 1
> > 20%
> > 39% 0.30 0.40
> > 59% 0.40 0.50
> > 79% 0.30 0.40 0.60 0.75
> > 89% 0.50 0.75 1.00 2.00
> > 99% 1.50 2.00 2.50 5.00
> > 100% 3.00 4.00 5.00 10.00
> >
> >
> > and then in F1 use a formula of
> >
> > =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "TYE" <TYE@discussions.microsoft.com> wrote in message
> > news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > > I have a spreadsheet that does the following on a weekly basic,
> > >
> > > First Name Surname Total SPH Sales £ Attendance
> > > %
> > > Claire Jepson 32.00 0.37 10 £40.00 100
> > > David Thomas 33.25 0.51 17 £85.00 100
> > >
> > >
> > > What I would like is that where it has pound sign to auto sum from the
> > > following table, (SECOND TABLE)
> > >
> > > SPH
> > > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > > 20%
> > > 21-39% £0.30 £0.40
> > > 41-59% £0.40 £0.50
> > > 60-79% £0.30 £0.40 £0.60 £0.75
> > > 80-89% £0.50 £0.75 £1.00 £2.00
> > > 90-99% £1.50 £2.00 £2.50 £5.00
> > > 100% £3.00 £4.00 £5.00 £10.00
> > >
> > > How can I put a formula in the pound sign that if the following staff
at
> > the
> > > end of the week have the following
> > >
> > > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM
THE
> > > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY
ARE IN
> > > THE SECOND TABLE.
> > >
> > > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> > >
> >
> >
> >

As I said before

Re-cut the data like so, to cells M1:R8

Attendance 0.24 0.35 0.49 0.69 1
20%
39% 0.30 0.40
59% 0.40 0.50
79% 0.30 0.40 0.60 0.75
89% 0.50 0.75 1.00 2.00
99% 1.50 2.00 2.50 5.00
100% 3.00 4.00 5.00 10.00


and then in F1 use a formula of

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1


--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> I have a spreadsheet that does the following on a weekly basic,
>
> First Name Surname Total SPH Sales £ Attendance
> %
> Claire Jepson 32.00 0.37 10 £40.00 100
> David Thomas 33.25 0.51 17 £85.00 100
>
>
> What I would like is that where it has pound sign to auto sum from the
> following table, (SECOND TABLE)
>
> SPH
> Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> 20%
> 21-39% £0.30 £0.40
> 41-59% £0.40 £0.50
> 60-79% £0.30 £0.40 £0.60 £0.75
> 80-89% £0.50 £0.75 £1.00 £2.00
> 90-99% £1.50 £2.00 £2.50 £5.00
> 100% £3.00 £4.00 £5.00 £10.00
>
> How can I put a formula in the pound sign that if the following staff at
the
> end of the week have the following
>
> SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> THE SECOND TABLE.
>
> So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
>

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1

can you please let me what columns are the following,

GI/100= IS THATS THE TOTAL ATTENDANCE
*E1= IS THIS THE TOTAL SALES IS THAT RIGTH

THATS

"Bob Phillips" wrote:

> As I said before
>
> Re-cut the data like so, to cells M1:R8
>
> Attendance 0.24 0.35 0.49 0.69 1
> 20%
> 39% 0.30 0.40
> 59% 0.40 0.50
> 79% 0.30 0.40 0.60 0.75
> 89% 0.50 0.75 1.00 2.00
> 99% 1.50 2.00 2.50 5.00
> 100% 3.00 4.00 5.00 10.00
>
>
> and then in F1 use a formula of
>
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "TYE" <TYE@discussions.microsoft.com> wrote in message
> news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > I have a spreadsheet that does the following on a weekly basic,
> >
> > First Name Surname Total SPH Sales £ Attendance
> > %
> > Claire Jepson 32.00 0.37 10 £40.00 100
> > David Thomas 33.25 0.51 17 £85.00 100
> >
> >
> > What I would like is that where it has pound sign to auto sum from the
> > following table, (SECOND TABLE)
> >
> > SPH
> > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > 20%
> > 21-39% £0.30 £0.40
> > 41-59% £0.40 £0.50
> > 60-79% £0.30 £0.40 £0.60 £0.75
> > 80-89% £0.50 £0.75 £1.00 £2.00
> > 90-99% £1.50 £2.00 £2.50 £5.00
> > 100% £3.00 £4.00 £5.00 £10.00
> >
> > How can I put a formula in the pound sign that if the following staff at
> the
> > end of the week have the following
> >
> > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> > THE SECOND TABLE.
> >
> > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> >
>
>
>

That is correct

G1 is attendance (I divide by 100 as you show it as 100 not 100%, if it is a
percentage don't divide by 100)
D1 is the SPH
E1 is the sales figure

--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:3F6AD473-D083-4CF2-B92F-49AB0C57C8A0@microsoft.com...
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
> can you please let me what columns are the following,
>
> GI/100= IS THATS THE TOTAL ATTENDANCE
> *E1= IS THIS THE TOTAL SALES IS THAT RIGTH
>
> THATS
>
> "Bob Phillips" wrote:
>
> > As I said before
> >
> > Re-cut the data like so, to cells M1:R8
> >
> > Attendance 0.24 0.35 0.49 0.69 1
> > 20%
> > 39% 0.30 0.40
> > 59% 0.40 0.50
> > 79% 0.30 0.40 0.60 0.75
> > 89% 0.50 0.75 1.00 2.00
> > 99% 1.50 2.00 2.50 5.00
> > 100% 3.00 4.00 5.00 10.00
> >
> >
> > and then in F1 use a formula of
> >
> > =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "TYE" <TYE@discussions.microsoft.com> wrote in message
> > news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > > I have a spreadsheet that does the following on a weekly basic,
> > >
> > > First Name Surname Total SPH Sales £ Attendance
> > > %
> > > Claire Jepson 32.00 0.37 10 £40.00 100
> > > David Thomas 33.25 0.51 17 £85.00 100
> > >
> > >
> > > What I would like is that where it has pound sign to auto sum from the
> > > following table, (SECOND TABLE)
> > >
> > > SPH
> > > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > > 20%
> > > 21-39% £0.30 £0.40
> > > 41-59% £0.40 £0.50
> > > 60-79% £0.30 £0.40 £0.60 £0.75
> > > 80-89% £0.50 £0.75 £1.00 £2.00
> > > 90-99% £1.50 £2.00 £2.50 £5.00
> > > 100% £3.00 £4.00 £5.00 £10.00
> > >
> > > How can I put a formula in the pound sign that if the following staff
at
> > the
> > > end of the week have the following
> > >
> > > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM
THE
> > > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY
ARE IN
> > > THE SECOND TABLE.
> > >
> > > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> > >
> >
> >
> >

As I said before

Re-cut the data like so, to cells M1:R8

Attendance 0.24 0.35 0.49 0.69 1
20%
39% 0.30 0.40
59% 0.40 0.50
79% 0.30 0.40 0.60 0.75
89% 0.50 0.75 1.00 2.00
99% 1.50 2.00 2.50 5.00
100% 3.00 4.00 5.00 10.00


and then in F1 use a formula of

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1


--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> I have a spreadsheet that does the following on a weekly basic,
>
> First Name Surname Total SPH Sales £ Attendance
> %
> Claire Jepson 32.00 0.37 10 £40.00 100
> David Thomas 33.25 0.51 17 £85.00 100
>
>
> What I would like is that where it has pound sign to auto sum from the
> following table, (SECOND TABLE)
>
> SPH
> Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> 20%
> 21-39% £0.30 £0.40
> 41-59% £0.40 £0.50
> 60-79% £0.30 £0.40 £0.60 £0.75
> 80-89% £0.50 £0.75 £1.00 £2.00
> 90-99% £1.50 £2.00 £2.50 £5.00
> 100% £3.00 £4.00 £5.00 £10.00
>
> How can I put a formula in the pound sign that if the following staff at
the
> end of the week have the following
>
> SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> THE SECOND TABLE.
>
> So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
>

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1

can you please let me what columns are the following,

GI/100= IS THATS THE TOTAL ATTENDANCE
*E1= IS THIS THE TOTAL SALES IS THAT RIGTH

THATS

"Bob Phillips" wrote:

> As I said before
>
> Re-cut the data like so, to cells M1:R8
>
> Attendance 0.24 0.35 0.49 0.69 1
> 20%
> 39% 0.30 0.40
> 59% 0.40 0.50
> 79% 0.30 0.40 0.60 0.75
> 89% 0.50 0.75 1.00 2.00
> 99% 1.50 2.00 2.50 5.00
> 100% 3.00 4.00 5.00 10.00
>
>
> and then in F1 use a formula of
>
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "TYE" <TYE@discussions.microsoft.com> wrote in message
> news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > I have a spreadsheet that does the following on a weekly basic,
> >
> > First Name Surname Total SPH Sales £ Attendance
> > %
> > Claire Jepson 32.00 0.37 10 £40.00 100
> > David Thomas 33.25 0.51 17 £85.00 100
> >
> >
> > What I would like is that where it has pound sign to auto sum from the
> > following table, (SECOND TABLE)
> >
> > SPH
> > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > 20%
> > 21-39% £0.30 £0.40
> > 41-59% £0.40 £0.50
> > 60-79% £0.30 £0.40 £0.60 £0.75
> > 80-89% £0.50 £0.75 £1.00 £2.00
> > 90-99% £1.50 £2.00 £2.50 £5.00
> > 100% £3.00 £4.00 £5.00 £10.00
> >
> > How can I put a formula in the pound sign that if the following staff at
> the
> > end of the week have the following
> >
> > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> > THE SECOND TABLE.
> >
> > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> >
>
>
>

That is correct

G1 is attendance (I divide by 100 as you show it as 100 not 100%, if it is a
percentage don't divide by 100)
D1 is the SPH
E1 is the sales figure

--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:3F6AD473-D083-4CF2-B92F-49AB0C57C8A0@microsoft.com...
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
> can you please let me what columns are the following,
>
> GI/100= IS THATS THE TOTAL ATTENDANCE
> *E1= IS THIS THE TOTAL SALES IS THAT RIGTH
>
> THATS
>
> "Bob Phillips" wrote:
>
> > As I said before
> >
> > Re-cut the data like so, to cells M1:R8
> >
> > Attendance 0.24 0.35 0.49 0.69 1
> > 20%
> > 39% 0.30 0.40
> > 59% 0.40 0.50
> > 79% 0.30 0.40 0.60 0.75
> > 89% 0.50 0.75 1.00 2.00
> > 99% 1.50 2.00 2.50 5.00
> > 100% 3.00 4.00 5.00 10.00
> >
> >
> > and then in F1 use a formula of
> >
> > =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "TYE" <TYE@discussions.microsoft.com> wrote in message
> > news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > > I have a spreadsheet that does the following on a weekly basic,
> > >
> > > First Name Surname Total SPH Sales £ Attendance
> > > %
> > > Claire Jepson 32.00 0.37 10 £40.00 100
> > > David Thomas 33.25 0.51 17 £85.00 100
> > >
> > >
> > > What I would like is that where it has pound sign to auto sum from the
> > > following table, (SECOND TABLE)
> > >
> > > SPH
> > > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > > 20%
> > > 21-39% £0.30 £0.40
> > > 41-59% £0.40 £0.50
> > > 60-79% £0.30 £0.40 £0.60 £0.75
> > > 80-89% £0.50 £0.75 £1.00 £2.00
> > > 90-99% £1.50 £2.00 £2.50 £5.00
> > > 100% £3.00 £4.00 £5.00 £10.00
> > >
> > > How can I put a formula in the pound sign that if the following staff
at
> > the
> > > end of the week have the following
> > >
> > > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM
THE
> > > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY
ARE IN
> > > THE SECOND TABLE.
> > >
> > > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> > >
> >
> >
> >

As I said before

Re-cut the data like so, to cells M1:R8

Attendance 0.24 0.35 0.49 0.69 1
20%
39% 0.30 0.40
59% 0.40 0.50
79% 0.30 0.40 0.60 0.75
89% 0.50 0.75 1.00 2.00
99% 1.50 2.00 2.50 5.00
100% 3.00 4.00 5.00 10.00


and then in F1 use a formula of

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1


--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> I have a spreadsheet that does the following on a weekly basic,
>
> First Name Surname Total SPH Sales £ Attendance
> %
> Claire Jepson 32.00 0.37 10 £40.00 100
> David Thomas 33.25 0.51 17 £85.00 100
>
>
> What I would like is that where it has pound sign to auto sum from the
> following table, (SECOND TABLE)
>
> SPH
> Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> 20%
> 21-39% £0.30 £0.40
> 41-59% £0.40 £0.50
> 60-79% £0.30 £0.40 £0.60 £0.75
> 80-89% £0.50 £0.75 £1.00 £2.00
> 90-99% £1.50 £2.00 £2.50 £5.00
> 100% £3.00 £4.00 £5.00 £10.00
>
> How can I put a formula in the pound sign that if the following staff at
the
> end of the week have the following
>
> SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> THE SECOND TABLE.
>
> So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
>

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1

can you please let me what columns are the following,

GI/100= IS THATS THE TOTAL ATTENDANCE
*E1= IS THIS THE TOTAL SALES IS THAT RIGTH

THATS

"Bob Phillips" wrote:

> As I said before
>
> Re-cut the data like so, to cells M1:R8
>
> Attendance 0.24 0.35 0.49 0.69 1
> 20%
> 39% 0.30 0.40
> 59% 0.40 0.50
> 79% 0.30 0.40 0.60 0.75
> 89% 0.50 0.75 1.00 2.00
> 99% 1.50 2.00 2.50 5.00
> 100% 3.00 4.00 5.00 10.00
>
>
> and then in F1 use a formula of
>
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "TYE" <TYE@discussions.microsoft.com> wrote in message
> news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > I have a spreadsheet that does the following on a weekly basic,
> >
> > First Name Surname Total SPH Sales £ Attendance
> > %
> > Claire Jepson 32.00 0.37 10 £40.00 100
> > David Thomas 33.25 0.51 17 £85.00 100
> >
> >
> > What I would like is that where it has pound sign to auto sum from the
> > following table, (SECOND TABLE)
> >
> > SPH
> > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > 20%
> > 21-39% £0.30 £0.40
> > 41-59% £0.40 £0.50
> > 60-79% £0.30 £0.40 £0.60 £0.75
> > 80-89% £0.50 £0.75 £1.00 £2.00
> > 90-99% £1.50 £2.00 £2.50 £5.00
> > 100% £3.00 £4.00 £5.00 £10.00
> >
> > How can I put a formula in the pound sign that if the following staff at
> the
> > end of the week have the following
> >
> > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> > THE SECOND TABLE.
> >
> > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> >
>
>
>

That is correct

G1 is attendance (I divide by 100 as you show it as 100 not 100%, if it is a
percentage don't divide by 100)
D1 is the SPH
E1 is the sales figure

--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:3F6AD473-D083-4CF2-B92F-49AB0C57C8A0@microsoft.com...
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
> can you please let me what columns are the following,
>
> GI/100= IS THATS THE TOTAL ATTENDANCE
> *E1= IS THIS THE TOTAL SALES IS THAT RIGTH
>
> THATS
>
> "Bob Phillips" wrote:
>
> > As I said before
> >
> > Re-cut the data like so, to cells M1:R8
> >
> > Attendance 0.24 0.35 0.49 0.69 1
> > 20%
> > 39% 0.30 0.40
> > 59% 0.40 0.50
> > 79% 0.30 0.40 0.60 0.75
> > 89% 0.50 0.75 1.00 2.00
> > 99% 1.50 2.00 2.50 5.00
> > 100% 3.00 4.00 5.00 10.00
> >
> >
> > and then in F1 use a formula of
> >
> > =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "TYE" <TYE@discussions.microsoft.com> wrote in message
> > news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > > I have a spreadsheet that does the following on a weekly basic,
> > >
> > > First Name Surname Total SPH Sales £ Attendance
> > > %
> > > Claire Jepson 32.00 0.37 10 £40.00 100
> > > David Thomas 33.25 0.51 17 £85.00 100
> > >
> > >
> > > What I would like is that where it has pound sign to auto sum from the
> > > following table, (SECOND TABLE)
> > >
> > > SPH
> > > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > > 20%
> > > 21-39% £0.30 £0.40
> > > 41-59% £0.40 £0.50
> > > 60-79% £0.30 £0.40 £0.60 £0.75
> > > 80-89% £0.50 £0.75 £1.00 £2.00
> > > 90-99% £1.50 £2.00 £2.50 £5.00
> > > 100% £3.00 £4.00 £5.00 £10.00
> > >
> > > How can I put a formula in the pound sign that if the following staff
at
> > the
> > > end of the week have the following
> > >
> > > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM
THE
> > > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY
ARE IN
> > > THE SECOND TABLE.
> > >
> > > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> > >
> >
> >
> >

I have a spreadsheet that does the following on a weekly basic,

First Name Surname Total SPH Sales £ Attendance
%
Claire Jepson 32.00 0.37 10 £40.00 100
David Thomas 33.25 0.51 17 £85.00 100


What I would like is that where it has pound sign to auto sum from the
following table, (SECOND TABLE)

SPH
Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
20%
21-39% £0.30 £0.40
41-59% £0.40 £0.50
60-79% £0.30 £0.40 £0.60 £0.75
80-89% £0.50 £0.75 £1.00 £2.00
90-99% £1.50 £2.00 £2.50 £5.00
100% £3.00 £4.00 £5.00 £10.00

How can I put a formula in the pound sign that if the following staff at the
end of the week have the following

SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
THE SECOND TABLE.

So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00

As I said before

Re-cut the data like so, to cells M1:R8

Attendance 0.24 0.35 0.49 0.69 1
20%
39% 0.30 0.40
59% 0.40 0.50
79% 0.30 0.40 0.60 0.75
89% 0.50 0.75 1.00 2.00
99% 1.50 2.00 2.50 5.00
100% 3.00 4.00 5.00 10.00


and then in F1 use a formula of

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1


--

HTH

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


"TYE" <TYE@discussions.microsoft.com> wrote in message
news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> I have a spreadsheet that does the following on a weekly basic,
>
> First Name Surname Total SPH Sales £ Attendance
> %
> Claire Jepson 32.00 0.37 10 £40.00 100
> David Thomas 33.25 0.51 17 £85.00 100
>
>
> What I would like is that where it has pound sign to auto sum from the
> following table, (SECOND TABLE)
>
> SPH
> Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> 20%
> 21-39% £0.30 £0.40
> 41-59% £0.40 £0.50
> 60-79% £0.30 £0.40 £0.60 £0.75
> 80-89% £0.50 £0.75 £1.00 £2.00
> 90-99% £1.50 £2.00 £2.50 £5.00
> 100% £3.00 £4.00 £5.00 £10.00
>
> How can I put a formula in the pound sign that if the following staff at
the
> end of the week have the following
>
> SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> THE SECOND TABLE.
>
> So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
>

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1

can you please let me what columns are the following,

GI/100= IS THATS THE TOTAL ATTENDANCE
*E1= IS THIS THE TOTAL SALES IS THAT RIGTH

THATS

"Bob Phillips" wrote:

> As I said before
>
> Re-cut the data like so, to cells M1:R8
>
> Attendance 0.24 0.35 0.49 0.69 1
> 20%
> 39% 0.30 0.40
> 59% 0.40 0.50
> 79% 0.30 0.40 0.60 0.75
> 89% 0.50 0.75 1.00 2.00
> 99% 1.50 2.00 2.50 5.00
> 100% 3.00 4.00 5.00 10.00
>
>
> and then in F1 use a formula of
>
> =INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "TYE" <TYE@discussions.microsoft.com> wrote in message
> news:AA86E28E-EFEC-4230-AB87-095DACD13D4E@microsoft.com...
> > I have a spreadsheet that does the following on a weekly basic,
> >
> > First Name Surname Total SPH Sales £ Attendance
> > %
> > Claire Jepson 32.00 0.37 10 £40.00 100
> > David Thomas 33.25 0.51 17 £85.00 100
> >
> >
> > What I would like is that where it has pound sign to auto sum from the
> > following table, (SECOND TABLE)
> >
> > SPH
> > Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
> > 20%
> > 21-39% £0.30 £0.40
> > 41-59% £0.40 £0.50
> > 60-79% £0.30 £0.40 £0.60 £0.75
> > 80-89% £0.50 £0.75 £1.00 £2.00
> > 90-99% £1.50 £2.00 £2.50 £5.00
> > 100% £3.00 £4.00 £5.00 £10.00
> >
> > How can I put a formula in the pound sign that if the following staff at
> the
> > end of the week have the following
> >
> > SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
> > SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
> > THE SECOND TABLE.
> >
> > So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00
> >
>
>
>