2

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?

1
  • 2
    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
2

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:

enter image description here


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)
0
1

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

Formula

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

Screenshot

enter image description here

3
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.