If statement or something else

[lucyclark83]

Hi,

I need to calculate the points in the lower table as per the numbers in the top table:

So, Emma's points for interviews needs to be worked out 4 interviews, but as she is 2 interviews higher than the average the 2 above are worth double so 2*10 plus 2*20 - is this possible?

Need to compare each of the individual's total against the averages, and take into account the amounts over the average and double that score. Does that make sense?

	Avg wkly result last Qtr		Points per 1	Double Points
Interviews	2		10	20
Interviews TP	1.5		15	30
Sent CV's	7		3	6
No. candidates	4		6	12
Mailshot sent CV's	48		0.5	1
SM's & EM's (ed or up)	<1		32	64
Leads created	14		1.5	3
Leads converted	<1		22	44
Projects created	<1		100	200
No. calls	100		0.2	0.4
Call time (minutes)	293		0.07	0.14
	Emma	POINTS
Interviews	4	60
Interview taking place	0	0
Sent CV's	10	39
No. candidates	8	72
Mailshot Sent CVs	0	0
SM's & EM's Ed or Up	0	0
Leads created	4	6
Leads converted	0	0
Projects created	0	0
# Calls	64	12.8
Call Time	05:54	29.05

<colgroup><col span="5"></colgroup><tbody>
</tbody>

 

[RoryA]

Since it looks like a fixed format, you basically want:

=MIN(emma's_score,average_score)*single_value+max(0,emma's score-average_score)*double_Value

substituting the appropriate cell references into that formula.

 

[lucyclark83]

Amazing, thank you! What about where I have the call times?

 

[RoryA]

If that 5:54 is 5 minutes 54 seconds, then you need to multiply it by 24 to convert it to a true minute value (5.9).

 

[lucyclark83]

Sorry I should have said, the 5:54 is 5 hours 54 minutes, can this be added to the formula, or would I need to have this as another field?

 

[RoryA]

In that case you'll need to multiply that by 1440 (24 hrs * 60 mins) in the formula.

 

[lucyclark83]

I'm still struggling with the call times, doesn't look right.
Where would I multiply it by the 1440?
This is what I'm trying
=MIN(emma's_score,average_score)*single_value+max(0,emma's score-average_score)*double_Value*1440

 

[RoryA]

You’d multiply emma’s Score by 1440 in both instances.