Attribute a specific number to a specific cell if conditions are fulfilled

Hi guys,

I have attached an excel workbook that shows how i want my data to look like after using the formulas that I hope you'll help me get.

In summary, I want a formula that distributes the students as follows (note that the maximum number of students per day is 20):

• If the total number of students is below 21, attribute that number of students to Monday, Wednesday, and Friday column cells
• If the total number of students is above 20 and below 41, attribute 20 students to Monday and Thursday column cells, and the difference to Tuesday and Friday column cells. e.g: we have 30 students, attribute 20 students to Monday and Thursday, and the remaining 10 to Tuesday and Friday.
• If the total number of students is above 40 and below 61, attribute 20 students to Monday and Thursday column cells, another 20 to Tuesday and Friday column cells, and the difference to Wednesday and Saturday column cells. e.g: we have 50 students, attribute 20 students to Monday and Thursday, attribute 20 more to Tuesday and Friday, and the remaining 10 to Wednesday and Saturday.

Thanks for your help.

Formula in B2 to G2 respecively

=IF($A2<=20,$A2,20)

=IF($A2<=20,"",MIN($A2-20,20))

=IF($A2<=20,$A2,IF($A2>40,MIN($A2-40,20),""))

=IF(A2<=20,"",20)

=IF($A2<=20,$A2,MIN($A2-20,20))

=IF($A2<=40,"",MIN($A2-40,20))

Thanks John, it's working perfectly.

Glad to have helped.

If your problem has been addressed could you please mark the thread as solved ("Thread Tools" at top of first post). Thank you.

sure, let me do it.