| View previous topic :: View next topic |
| 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. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 http://forum.openoffice.org |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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. |
OOops, I forgot about this.
Make a second "Sold" column.
Sold2
=H2:H1000 Ctrl+Shift+Enter
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
