# Is there a better way to write this ifs statement?

I’m making a calculator for fun in relation to a Roleplay group of mine. Essential a certain number, to number, equals another number. However, the way I’m writing it seems inefficient as I have been writing each number one by one like so:

``````=ifs(A13=1,-2,A13=2,-2,A13=3,-1,A13=4,-1,A13=10,3,len(A13)=0,A1)
``````

Where A13 is the cell where people input their numbers. I was wondering if there was some way to go about this better? An example of how this works is:

1-4 = 1

5-8 = 2

9-12 = 3...etc.

Is there a way to write Number To Number ? Or any suggestions for how I could go about doing this better?

• Welcome to Web Applications. Please follow the tour, read How to Ask, add a brief description of your search/research efforts and try to reword your question as it's actually worded it looks like a primarily opinion-based questions which are not allowed here. – Rubén Jun 2 '20 at 21:59

It looks like some arithmetic does the job, something like this:

• add 3 to the number, divide by 4 and round down. The following formula does that (the round down is performed by the `INT` function):

``````=INT((A13+3)/4)
``````
• perhaps it's easier to divide by 4 and round up; we have the `CEILING` function for that:

``````=CEILING(A13/4)
``````

They'll both produce the same results: A general trick to generate a formula like this one:

``````1-2 = -2
3-4 = -1
5-6 = 0
``````

Your input is divided into buckets of size 2, so the first step is to divide by two:

``````1 -> 0.5
2 -> 1
3 -> 1.5
4 -> 2
5 -> 2.5
6 -> 3
``````

Then, add or subtract a number such that the outcome is correct for the lowest or the highest number in the input 'bucket'. In this case, we can subtract 3:

``````1 -> 0.5 -> -2.5
2 -> 1   -> -2
3 -> 1.5 -> -1.5
4 -> 2   -> -1
5 -> 2.5 -> -0.5
6 -> 3   ->  0
``````

Then, round up or down. In this case, up. So we have to

• divide by 2
• subtract 3
• round up

and our final formula becomes

``````=CEILING(A1/2 - 3)
``````

Another way of doing it is making use of the `QUOTIENT` function:

## Formula

``````=ARRAYFORMULA(QUOTIENT(B1:B17,4)+1)
``````

## Screenshot • What is the B1:B17 portion? – Steph Jun 10 '20 at 0:25
• That is a good question Steph, I'll look up my file and post a screenshot . – Jacob Jan Tuinstra Jun 10 '20 at 7:47
• @Steph I noticed that my anser is not exactly what you want..... – Jacob Jan Tuinstra Jun 10 '20 at 8:04