I have a problem in Excel where I want to use a formula to calculate the number of delegates a specific county can send to the regional congress.

In this case a county is allowed to send 1 delegate per 300 votes (1-299: 1, 300-599: 2 etc.) Thus for this case I used the Ceiling formula which worked great, but the tricky part is that the rules changes when the amount of votes exceeds 3000, then the rule is 1 delegate per 1000 votes.

How can I add the second rule to the ceiling formula?

data sample

the picture shows some sample data, and for example, D8 I can use the simple Ceiling formula, but for D5 with over 3000 voters, I want a formula that calculates amount of Delegates per 3000 votes amount per extra 1000 votes.

CodePudding user response：

If this matches your expected outcome:

Then you may want to try this what I posted in the comments:

=(A1>=3000)*(INT(A1/1000) 9) ((A1<3000)*INT(A1/300) 1)*(A1>0)

Edit:

I adjusted the formula but for 5004 votes I got a different result to your table ..