Rounding down within a formula.

[Marvo]

My second and final question of the day (I promise), I have the following formula

=IF(G21="","",IF($G$38=1,$G$18/1,IF($G$38=2,$G$18/2,IF($G$38=3,$G$18/3,IF($G$38=4,$G$18/4,IF($G$38=5,$G$18/5,IF($G$38=6,$G$18/6,IF($G$38>6,"c/o"))))))))

What its doing is dividing a total prize (G18) into however many winners (G38). Two rules, prize has to be in increments of £1 and the minimum prize is £5.

In the instance above, G18 = £30. It works fine except when it divides £30 by 4 it returns £8, I need it to return £7. I placed a ROUNDOWN in the formula where it divides by 4 and that worked accept it then produced FALSE when you have 5 or 6 winners. Any ideas how this can be remedied or maybe a better idea of how to construct the formula?

Many thanks.

 

[Scott Huish]

Simplifying the formula for sure. Not sure how you ever get 8 by 30/4, but does this work for you:
=IF(G21="","",IF($G$38>6,"c/o",INT($G$18/$G$38)))

 

[Marvo]

That's fabulous, thank you.