[Solved] Compute Where a Given Number Falls in a range

[krovas]

I want to, given three numbers A, B, and C, compute where C falls, as expressed in 25% intervals, into a range defined by A - B. As as an example:

A = 80
B = 76
C = 77

So, the function would return a value of 25%, because 77 falls at the lower 25% of that range created from A - B. 78 would yield 50%, etc.

I have little idea would this could be accomplished in OpenOffice, and any constructive feedback would be appreciated. Thank you.

[FJCC]

If your numbers A, B and C are in E1, E2 and E3, this formula

Code: Select all

=ROUNDUP((E3-E2)/(E1-E2) * 4) * 0.25

will return 25% for any number in E3 greater than 76 and <= 77, 50% for 77< E3 <=78, etc. You may want to use a different function from the ROUND family. If you want 77.1 to return 25%, because it is closer to 25% than 50%, then use the plain ROUND() function.

Note that the function will return values between 0 and 1 if E3 is between E2 and E1. You have to format the cell to display that as a percent.

[krovas]

Very nice, thank you for the quick response. I am going to go try this now...

[krovas]

If your numbers A, B and C are in E1, E2 and E3, this formula

Code: Select all

=ROUNDUP((E3-E2)/(E1-E2) * 4) * 0.25

will return 25% for any number in E3 greater than 76 and <= 77, 50% for 77< E3 <=78, etc. You may want to use a different function from the ROUND family. If you want 77.1 to return 25%, because it is closer to 25% than 50%, then use the plain ROUND() function.

Note that the function will return values between 0 and 1 if E3 is between E2 and E1. You have to format the cell to display that as a percent.

Works like a charm. Thank you very much.