Help to calculate golf handicap by rounding down numbers to be no greater than 2

I have many groups of 18 numbers that vary from -1 to +6 (golf above par numbers) that I want to add up but when adding any number greater than 2 it will round down to 2.

Is this possible? I have googled away now for half an hour and can't find any method to do this so have come here where I have found excellent solutions to my Excel puzzles before!

Is this the sort of thing you're after ? Any number in A1 greater than 2 is reduced to 2, any number less than or equal to 2 stays the same

Please Login or Register  to view this content.

You are fantastic that works perfectly! Many thanks it took less than a minute to calculate handicap for 26 scores!


For others who come across this post...

On 18 vertical cells with golf shots above par the formula

=IF(G3>2,2,G3)+IF(G4>2,2,G4)+IF(G5>2,2,G5)+IF(G6>2,2,G6)+IF(G7>2,2,G7)+IF(G8>2,2,G8)+IF(G9>2,2,G9)+IF(G10>2,2,G10)+IF(G11>2,2,G11)+IF(G12>2,2,G12)+IF(G13>2,2,G13)+IF(G14>2,2,G14)+IF(G15>2,2,G15)+IF(G16>2,2,G16)+IF(G17>2,2,G17)+IF(G18>2,2,G18)+IF(G19>2,2,G19)+IF(G20>2,2,G20)

adds up all the numbers but squishes any individual number above 2 down to 2. Obviously this is for my spreadsheet and so your cell refs would differ!