Finding the Right Excel Function

[Katelyn Ramsay]

Hello, 

We have a work contest this month and I would like to create a spreadsheet to track my progress and my competition. 

We earn one point for our daily goal of 6, then 1 more point per additional unit over 6. For example, if you sell 14 units in one day, you earn 1 point for daily goal of 6, then 8 more points, so a total of 9.  How would I write the formula for this? 

Your help is very appreciated!  Thank you!

[#2]

Katelyn,

Use this formula:

=IF(A2<6,0,IF(A2=6,1,A2-6+1))

[#3]

thank you! I ended up figuring out =IF(B3>=6,1,0)+IF(B3>6,(1*(B3-6)),0 and expanding it across each rep and down across the date. not sure if that is the best way to do it but it worked! Thank you very much for your reply.

[#4]

Katelyn,

a more advanced solution:

=MAX(0,A1-6+(A1>=6))

[#5]

Assuming all of your numbers are whole numbers starting in A1 then a simple IF formula in B1 should work:

=IF(A1>5, A1-5, 0)