IFSUM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
 
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?
 
We input the figuers in daily to get the sales and hours per person.

Could you help with the following sum
 
Don't think that is any more information that we already had mate.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
Back
Top