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

Discuss the spreadsheet application
Post Reply
jkruse05
Posts: 2
Joined: Sat Jan 11, 2020 7:31 am

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

Post by jkruse05 »

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

=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
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Post by robleyd »

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

=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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

Points|Grades in A2:B9
0 H
20 G
38 F
55 E
70 D
82 C
90 B
100 A
-------------------------
Scores|Lookup Grades
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jkruse05
Posts: 2
Joined: Sat Jan 11, 2020 7:31 am

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

Post by jkruse05 »

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

=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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

=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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply