[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
rudagar
General User

Joined: 21 Jun 2005
Posts: 21

Posted: Tue Oct 10, 2006 5:37 am    Post subject: Arrays

Is there any way that, using an array if statement, you can return ONLY the non-empty result?

Right now, the formula is giving me 130 empty cells with 1 cell that matches correctly.

I am trying to find a currency value in Column H and return the corresponding value in Column B(text), but can't seem to get anything to work properly.

Let me explain more... I have data similar to this:
 Code: B            D        H (Item)       (Owner)  (Sold For) Car          Jim      \$10000 Computer     Steve    \$700 Stereo       Jim      \$300 TV           Bill     \$50 Golf Clubs   Steve    \$100

This is a very simplified verson.

Now, I have formulas in place that calculate each owner's highest seller, and lowest seller. What I want to do now, is to be able to return the item in the cell next to it. This is in a summary spreadsheet.

Right now, it has something like
 Code: Jim   ____  \$10000  ____  \$300 Bill  ____  \$50 Steve ____  \$700    ____  \$100

in the sheet... I want to come up with a formula to fill in those blanks.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Tue Oct 10, 2006 7:43 am    Post subject: This can be done with a data-pilot (also known as pivot-table in excel). Select your list (or entire columns of) Data>Pilot>Start... 1st dialog: From current selection Wizzard: Button "More Options" Specify some top-left cell of an empty range or sheet Check all options below. Drag "Item" and "Owner" into to the left "Row Fields" box. Drag "Sold" into "Data Fields", doubleclick and specify function Max Again drag "Sold" into "Data Fields", doubleclick and specify function Min._________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
rudagar
General User

Joined: 21 Jun 2005
Posts: 21

Posted: Tue Oct 10, 2006 9:02 am    Post subject:

 Villeroy wrote: This can be done with a data-pilot (also known as pivot-table in excel). Select your list (or entire columns of) Data>Pilot>Start... 1st dialog: From current selection Wizzard: Button "More Options" Specify some top-left cell of an empty range or sheet Check all options below. Drag "Item" and "Owner" into to the left "Row Fields" box. Drag "Sold" into "Data Fields", doubleclick and specify function Max Again drag "Sold" into "Data Fields", doubleclick and specify function Min.

It won't let me add Sold the 2nd time for Min.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Tue Oct 10, 2006 9:10 am    Post subject:

 Quote: It won't let me add Sold the 2nd time for Min.

Make a second "Sold" column.
Sold2
=H2:H1000 Ctrl+Shift+Enter
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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