OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Questions using Datapilot and MinFunktion

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
PBiela
Power User
Power User


Joined: 10 Feb 2004
Posts: 56
Location: Frankfurt/Main Germany

PostPosted: Tue May 29, 2007 12:44 am    Post subject: Questions using Datapilot and MinFunktion Reply with quote

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
Back to top
View user's profile Send private message
squenson
Super User
Super User


Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

PostPosted: Tue May 29, 2007 11:07 am    Post subject: Reply with quote

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:
Code:
=MIN(IF(Items=B2;Prices;999999))
and press Ctrl-Shift-Enter to validate the formula (a pair of brackets will appear around the formula).

What this formula does is for each value in the range "Items", is it equal to the item on the current row. If yes, take the price on that row, if not, use an arbitrary large amount. Now, from all these values, select the minimum. So D2 contain the minimum price of the item in B2. Copy the content of cell D2, and paste it to the range D3:Dxxx

In E1, type "Best Price"
In E2, type the formula
Code:
=IF(C2=D2;"Cheapest";"Not cheap!")
which simply compares the minimum price with the current row.

Now, select the whole range, including the first row with the labels, click on menu Data > Filter > AutoFilter, and a small button with a down arrow appears in each cell of the first row. Click on the arrow in cell E1, then select the value "Cheapest", and you will have all the items with the cheapest price selected. Note that if two items have two producers offering the same price, both rows will appear.
_________________
Help us to help you: Add [Solved] to the title of the thread if you agree with the answer
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Back to top
View user's profile Send private message
PBiela
Power User
Power User


Joined: 10 Feb 2004
Posts: 56
Location: Frankfurt/Main Germany

PostPosted: Wed May 30, 2007 2:55 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed May 30, 2007 4:10 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group