## [Solved] Returning a number when a result is within a spec

### [Solved] Returning a number when a result is within a spec

I haven't been able to find anything related to this, so finally made an account to ask. You've all been very helpful in the past, just by reading old stuff.

Anyway, what I'm trying to do is write a formula so that if the absolute value of an operation comes out within a certain number range, it will output a specific other number. For example, if I do ABS of the operation, and the answer is between 0 and 4, then output 5, if between 5 and 8, output 4, and so on. That number should then also be added to the largest of a set of cells, but that part is working fine. I think the IF statements are okay, based on other usages I've seen, I just can't find the proper format for checking the number range of the ABS output.

Here's what I have right now. I've tried [0,4], [0;4], [0:4], and with () instead of []. I think there's something else missing as well.
Code: Select all   Expand viewCollapse view
`=LARGE(G8:G10; 1)+IF(ABS(G8-G9)=[0:4];5;IF(ABS(G8-G9)=[5:8];4;IF(ABS(G8-G9)=[9:14];3;IF(ABS(G8-G9)=[15:20];2;IF(ABS(G8-G9)=[21:26];1;0)))))`

Technically this will then be repeated with the final output checked against G10 and added to the total, but I think I can get that working if I can get the first part.
Last edited by jkruse05 on Sat Jan 11, 2020 4:32 pm, edited 2 times in total.
Apache OpenOffice 4.1.6
Windows 7 Ultimate
jkruse05

Posts: 2
Joined: Sat Jan 11, 2020 7:31 am

### Re: Returning a number when a result is within a specified r

If ABS(x) =1 or =2 or =3 or =4 then also ABS(x) will be <=4. Thus, this should work (untested, you can keep any typos you find);

Code: Select all   Expand viewCollapse view
`=LARGE(G8:G10; 1)+IF(ABS(G8-G9)<=4;5;IF(ABS(G8-G9)<=8;4;IF(ABS(G8-G9)<=14;3;IF(ABS(G8-G9<=20;2;IF(ABS(G8-G9)<=26;1;0)))))`

Unless you are likely to need the Rank_c-th largest value from LARGE, you could simply use MAX(G8:G10).

Depending on how many comparisons you need, use of a lookup table, e.g. VLOOKUP, might be preferable.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3506
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Returning a number when a result is within a specified r

0 H
20 G
38 F
55 E
70 D
82 C
90 B
100 A
-------------------------
55 E =LOOKUP(\$D2;\$A\$2:\$A\$9;\$B\$2:\$B\$9)
3 H
6 H
36 G
49 F
96 B
82 C
104 A

=LOOKUP(\$D2;\$A\$2:\$A\$9;\$B\$2:\$B\$9) returns the last corresponding value from \$B\$2:\$B\$9 where \$D2 is smaller than or equal to \$A\$2:\$A\$9.
equivalent:
=VLOOKUP(\$D2;\$A\$2:\$B\$9;2;1)
=INDEX(\$B\$2:\$B\$9;MATCH(\$D2;\$A\$2:\$A\$9;1))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Returning a number when a result is within a specified r

robleyd wrote:If ABS(x) =1 or =2 or =3 or =4 then also ABS(x) will be <=4. Thus, this should work (untested, you can keep any typos you find);

Code: Select all   Expand viewCollapse view
`=LARGE(G8:G10; 1)+IF(ABS(G8-G9)<=4;5;IF(ABS(G8-G9)<=8;4;IF(ABS(G8-G9)<=14;3;IF(ABS(G8-G9<=20;2;IF(ABS(G8-G9)<=26;1;0)))))`

Unless you are likely to need the Rank_c-th largest value from LARGE, you could simply use MAX(G8:G10).

Depending on how many comparisons you need, use of a lookup table, e.g. VLOOKUP, might be preferable.

That worked like a charm, just had to add an extra IF at the beginning for when the ABS=0. I didn't want to use VLOOKUP for this because it's only comparing those few values already in the code. Thanks.
Apache OpenOffice 4.1.6
Windows 7 Ultimate
jkruse05

Posts: 2
Joined: Sat Jan 11, 2020 7:31 am

### Re: [Solved]Returning a number when a result is within a spe

=MAX(G8:G10)+INDEX({1;2;3;4;5};MATCH(ABS(G8-G9);{26;20;14;8;4};-1))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany