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

Discuss the spreadsheet application

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

Postby jkruse05 » Sat Jan 11, 2020 7:49 am

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

Postby robleyd » Sat Jan 11, 2020 8:37 am

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
User avatar
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

Postby Villeroy » Sat Jan 11, 2020 1:15 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
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

Postby jkruse05 » Sat Jan 11, 2020 4:31 pm

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

Postby Villeroy » Sat Jan 11, 2020 6:59 pm

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


Return to Calc

Who is online

Users browsing this forum: Bill and 20 guests