Ask Your Question
0

Trying to set up formula for a range of values?

asked 2019-11-08 16:22:05 +0100

TEPSMedia gravatar image

updated 2019-11-08 16:44:16 +0100

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

Do you see what I'm trying to do? if so, please help.

edit retag flag offensive close merge delete

Comments

Do you see what I'm trying to do?

Yes!

Please mark this answer as correct! ๐Ÿ˜‰

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-08 16:40:40 +0100 )edit

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

TEPSMedia gravatar imageTEPSMedia ( 2019-11-08 16:43:48 +0100 )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.

Opaque gravatar imageOpaque ( 2019-11-08 16:45:36 +0100 )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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-08 16:48:57 +0100 )edit

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

TEPSMedia gravatar imageTEPSMedia ( 2019-11-08 16:51:04 +0100 )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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-08 16:55:55 +0100 )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.

Opaque gravatar imageOpaque ( 2019-11-08 17:00:24 +0100 )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 ๐Ÿ˜Š

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-08 17:05:09 +0100 )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

TEPSMedia gravatar imageTEPSMedia ( 2019-11-08 17:16:23 +0100 )edit

1 Answer

Sort by ยป oldest newest most voted
0

answered 2019-11-08 16:47:09 +0100

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).

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-08 16:22:05 +0100

Seen: 19 times

Last updated: Nov 08