home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Archive Magazine 1996
/
ARCHIVE_96.iso
/
discs
/
gerald
/
7_02
/
9311
/
BestShop
/
ReadMe
next >
Wrap
Text File
|
1993-09-25
|
3KB
|
59 lines
%OP%VS4.13 (28-Apr-92), Gerald L Fitton, R4000 5966 9904 9938
%OP%DP0
%OP%IRY
%OP%PL0
%OP%HM0
%OP%FM0
%OP%BM0
%OP%LM4
%OP%PT1
%OP%PDPipeLine
%OP%WC1026,2262,188,1748,0,0,0,0
%CO:A,72,72%
%C%Selecting a Maximum or Minimum
%C%by Gerald L Fitton & Denis Murray-Smith
Keywords:
BestShop Murray-Smith Fitton
It was Denis Murray-Smith who first put this problem to me in the
context of finding the best shop in which to buy a particular item when
you know the prices of the items in each of many shops. However, the
problem is of wider significance, for example, you may wish to find out
which of many subjects are best for each pupil and then find out which
subject is best received!
Load the file [Shopping]. The names of the products appear in column A
as Alpha, Beta, Gamma and Delta. The names of the shops appear in row
6 as Shopá1, Shopá2, Shopá3 and Shopá4. Of course you could have many
more items and a much greater choice of shops; I wanted to ensure that
the whole file fitted neatly across the screen and didn't take up too
much disc space.
Data (prices) is entered in the block B8E11. Column F contains a
formula which calculates the cheapest price for the item in each row.
Column G contains a formula which returns the name of the shop having
the lowest price.
The price of each of the four items in each of the four shops appears
in the range of slots B8E11. The formula in slot F8 is min(B8E8), and
it returns the lowest price in row B.
The slot G8 contains the formula lookup(F8,B8E8,B$6E$6). The arguments
of the lookup(,,) formula are: F8 - the key field containing the
minimum price, B8E8 - the range in which you hope to find F8 (the
minimum price), B$6E$6 - the range which contains the value to be
returned. Note the $ characters in this last range; these ensue that,
as you replicate the formula from G8 down through the column G, the
final argument of the lookup(,,) function does not change. For
example, in the slot G11 you will find lookup(F11,B8B11,B$6E$6).
The formulae in F8 and G8 have been replicated down the two columns
using the following technique. Place the cursor in F8 and execute
<CtrláZ> to mark slot F8. Place the cursor in G11 and execute <CtrláZ>
so that the 'block' F8G11 is marked. Alternatively drag from F8 to G11
to mark F8G11. Execute <CtrláBRD> to replicate the formula down
through the marked block. You can extend the formulae down as many
rows as you wish.
Please let me know if you find this simple sort of spreadsheet useful.