At 16:07 27/03/2019 -0400, Vince Bonly wrote:
This Dropbox link gives access to an AOO Calc file, with which I am
having a frustrating problem:
https://www.dropbox.com/s/ys2gppp7maiwelp/%23DIV%20by%200%21%20Problem%20in%20Calc.ods?dl=0
With reference to cell range $E$76:$T$76 (Bowler's Highest 15-Game
Average data), I want Calc to examine the numerical values and
identify which Bowler's Highest 15-Game Average score is the LARGEST value:
1. display within cell $E$78 the name of the bowler that has the
LARGEST value found within $E$76:$T$76; i.e., 189.83 found in cell
M76 and correlation to cell M74.
2. display within cell $F$78 the name of the bowler that has the 2nd
LARGEST value found within $E$76:$T$76; i.e., 180.50 found in cell
E76 and correlation to cell E74.
[etc.]
In this example, cells $E76:$T$76 would yield: "SAM C."; "BOB M.";
"WILLIE M."; "VINCE B."; and "RUSSEL P.".
I suspect that the HLOOKUP function would be needed to accomplish
this task. ?? In the Help file, there is no example of using the
HLOOKUP function, and I am uncertain about the relational row
location as required by the HLOOKUP function.
See the VLOOKUP() function, which is similar but with rows and
columns swapped, and is explained fully.
At 16:33 27/03/2019 -0400, Vince Bonly wrote:
In 5. above, it should read as:
"display within cell $I$78 the name of the bowler that has the 5th
LARGEST value found within $E$76:$T$76; i.e., 149.33 found in *cell
L76* and correlation to cell L74."
There's a moral here, which applies to spreadsheet design generally:
if you type a lot of similar items, you will inevitably make slips.
Always do things the minimum number of times necessary to indicate
the structure of what you need.
At 08:57 28/03/2019 -0400, Vince Bonly wrote:
The Dropbox link written in my previous messages, apparently, is not
allowing the message to pass through to this Mailing List, ...
No, that's not true; they were distributed, as you can see at
https://markmail.org/search/list:org.apache.incubator.ooo-users#query:list%3Aorg.apache.incubator.ooo-users+page:1+mid:w32pzbb6fuu4yf5d+state:results
. Any problem must have been with receipt of copies sent to you.
First, since you are searching for calculated values, you will have
problems with rounding. You may want to experiment with the setting
at Tools | Options... | OpenOffice Calc | Calculate | Precision as
shown, but it may be easier to round your values explicitly. Change
your formulae in row 76 from =AVERAGE(...) to =ROUND(AVERAGE(...);2).
(You will still need to format cells to show two fractional places,
so as to avoid losing trailing zeroes.)
In E79, enter:
=LARGE($E76:$T76;COLUMN()-4)
Fill this along row 79 as far as required.
In E78, enter:
=HLOOKUP(E79;$E76:$T82;7;0)
Fill this similarly along row 78.
Note that HLOOKUP() can harvest values only from rows *below* the
matching value, so once it has found a value in row 76 it cannot look
upwards to obtain the name from row 74. I have taken advantage of the
fact that you have copies of the same names in row 82.
It may well be easier to lay out your calculation table in different
way. Since you will want the final area to be viewed or printed to
retain the appearance you have, you may well find it easier to
perform the calculations elsewhere - perhaps even on another sheet -
and to import the results into your display table.
Note also that my suggestion does not deal properly with equal values
for averages!
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
