Questions using Datapilot and MinFunktion

PBiela

Hi Forum,

i've been using macros for almost all my OpenOffice task in the last years.
Now i wanted to use the DataPilot Function to prefom some simple
task on a spreadsheet filled with product data.

I've got serveral producers and prices for some kind of object,
now all i want is to display simply the cheapest producer for each object.
Maybe with the ability to drilldown to all producers of that object.

I'm pretty sure that the DataPilot should be able to prefrom that task.
But somehow i can't convince mine to do it.

I've been able to generate an DataPilot that will display the items and the producers
but it display's just the cheapest item of each producer, not comparing between the objects. Or it displays all Items and Producers.

Now i know how to write an macro to do what i want or how to solve the problem
using a databank, but how should i do it using a DataPilot?

Maybe someone here can help me a bit. Smile

Peter Biela
_________________
Question

Who is John Galt? - Ayn Rand Question

squenson · Posted: Tue May 29, 2007 11:07 am Post subject:

PBiela,

I can offer you a solution with two additional columns.

Let's say you have your producers in column A, your items in column B and your prices in column C. Give the name "Items" to the vertical range of cells containing your items, and "Prices" to the vertical range of cells containing your prices.

In D1, insert "Minimum Price"
In D2, type the formula:

PBiela · Posted: Wed May 30, 2007 2:55 am Post subject:

Thank you very much, Squenson.
I will try your solution next time i'm on that sheet.
I'm sure it will work just fine.

Thanks again.
_________________
Question

Who is John Galt? - Ayn Rand Question

Villeroy · Posted: Wed May 30, 2007 4:10 am Post subject:

What's wrong with it?
Created a quick table with (unreasonable) random values and created a pilot from that.
Rows: Vendor
Column: Item
Data: Min(Price)

Min - Price Item
Vendor Item1 Item2
Vendor1 80 100
Vendor2 20 200
Vendor3 20 300
Vendor4 20 500
Vendor5 30 400
Total Result 20 100

From all offers by Vendor1 for Item1 the minimum is 80
From all offers by Vendor1 for Item2 the minimum is 100
From all offers by Vendor2 for Item1 the minimum is 20
...
I get the same results with squenson's array formula.

Finally I selected entire columns B and C (the two item columns) and created a conditional format "value =MIN(B$1:B$65536) -->"Green" [B$1:B$65536 relative to active cell in B].
This highlights all minimumns.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org