# Trying to set up formula for a range of values?

I'm trying to figure out how to structure this answer.

If the answer in cell D201 = X amount then display a certain number.

Example of what I need: If D201 = anything from 7.0 to 9.29 then cell D203 should display the number 5, but if the value of D201 is between 5.5 to 6.99 then display 4, or the value of D201 is between 3.0 to 4.49 then display a 3

edit retag close merge delete

Do you see what I'm trying to do?

Yes!

( 2019-11-08 16:40:40 +0200 )edit

Are you trying to be funny there? I can't tell.

( 2019-11-08 16:43:48 +0200 )edit
1

Hello =IFS(AND(D201>=3,D201<=4.49),3,AND(D201>=5.5,D201<7),4,AND(D201>=7,D201<=9.29),5) but be aware, that your requirements specified in details leave off some ranges like "D201<3,D201>9.29, 4.49<D201<5.5 and what about numbers between e.g 6.99 and 7.00 (mathematically there a uncountable infinte numbers in the range) - thus I did not put that into an answer.

( 2019-11-08 16:45:36 +0200 )edit

Yes, i'm trying to be funny. That isn't something you should worry about: it never hurts to smile, and I prepared an actual answer when you wrote your comment.

( 2019-11-08 16:48:57 +0200 )edit

Thank you, I can fill in the missing values. That is a big help.

( 2019-11-08 16:51:04 +0200 )edit

Note that @Opaque used syntax only applicable where comma is argument separator. Universal separator is semicolon. However, even using it, there is still a problem of decimal separator, which may be comma or colon, so there's no totally universal syntax.

( 2019-11-08 16:55:55 +0200 )edit

@Mike Kaganski - yes, I remember your advice once given :-) but it conflicts with another advice "Test before you post" and using an en_US UI turns my ; to , and then copy and paste-inattention hits me :-). And yes - I know about the separator setting for "Function", but on my test LibreOffice instance I'm using a fresh profile once a day.

( 2019-11-08 17:00:24 +0200 )edit

@Opaque: Yes, I understand you: I myself use Russian locale, with commas as decimal separators, and have to edit answers every time I know that dot is expected by the asker (most of the time).

For argument separator, though, one might change preferences (I'd say that would make contributing here more comfortable).

but on my test system I'm always using a fresh profile once a day

I see 😊

( 2019-11-08 17:05:09 +0200 )edit
1

=IFS(AND(D201>=3,D201<5.5),3,AND(D201>=5.5,D201<7),4,AND(D201>=7,D201<=9.29),5,AND(D201>=2,D201<=3),2,AND(D201>0,D201<=2),1,AND(D201>=9.29,D201<=10),6,AND(D201=0),0)

This is what I ended up doing and it works great! Thank you @Opaque

( 2019-11-08 17:16:23 +0200 )edit

Sort by » oldest newest most voted

Use IFS function:

=IFS(AND(D201>=7;D201<9.3);5;D201>=5.5;4;D201>=3;3)


This slightly simplified your example (in it, values between 4.5 and 5.5 were undefined).

more