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

Arrays

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


Joined: 21 Jun 2005
Posts: 21

PostPosted: Tue Oct 10, 2006 5:37 am    Post subject: Arrays Reply with quote

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Oct 10, 2006 7:43 am    Post subject: Reply with quote

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


Joined: 21 Jun 2005
Posts: 21

PostPosted: Tue Oct 10, 2006 9:02 am    Post subject: Reply with quote

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Oct 10, 2006 9:10 am    Post subject: Reply with quote

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

OOops, I forgot about this.
Make a second "Sold" column.
Sold2
=H2:H1000 Ctrl+Shift+Enter
_________________
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