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?

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

 

[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.