IFSUM

[TYE]

I need help I have a spreadsheet that has the following information,

Total Hours
SPH
SALES
£
Attendance

What I’d like to do is the following,

If an agent has got the following targets is should Calculator it self.

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


So at the end of the week if an agent has got 0.38 and 100% Attendance all
their sales will be paid at £4.00

Could you help me with sumif are something that would calculator it self.

Thanks
tye

 

[Bob Phillips]

Re-cut the data like so

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 use

=INDEX(A1:F8,MATCH(attendance,A1:A8,1),MATCH(sales,A1:F1))

--

HTH

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

 

[TYE]

I have cut that in the speadsheet but it does not work,

i have got two parts on the speadsheet.

The first part is

input the total weeks figures

As show below,

Total SPH Sales £ Attendance
%

0.00 #DIV/0! 0 £0.00
0.00 #DIV/0! 0 £0.00
34.25 0.38 13 #VALUE! 100
19.00 0.68 13 £13.00 80
32.00 0.37 10 £40.50 100
0.00 #DIV/0! 0 £0.00

Then i have the second table like below,
SPH
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

From this total i would like the main table to updata

e.g

First Name Surname Total SPH Sales £ Attendance
%
Claire Jepson 32.00 0.37 10 £40.50 100



Can you help

 

[Duke Carey]

In your example, how do we know that the line from the first table that
contains the data:

32.00 0.37 10 £40.50 100

is associated with Claire Jepson?

 

[TYE]

We input the figuers in daily to get the sales and hours per person.

Could you help with the following sum

 

[Bob Phillips]

Don't think that is any more information that we already had mate.

--

HTH

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

 

[TYE]

At the end of the week i have a list all the
staff names
Total Hours
SPH
Sales
£
Attendance

I want the £ getting it information from this 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

e.g

Name Total sph Sales £ Attendance%
Jon smith 32 0.37 10 40.50 100%

Where its pound sign i want it to lookup the information from the second
table and updata it (£)

Can you help with this

Thanks