Hello all-
I'm trying to work an AND statement in to a formula so that if 2 conditions are met (fuel and type) then the date of that record will be displayed along with the other matched dates in ascending order on a report tab. This statement works for a single condition,
=INDEX(data!$A$1:$A$100,SMALL(IF(data!$B$1:$B$100=$A$3,ROW(data!$A$1:$A$100)),ROWS($B$2:B3)))
and this is the statement that I've been trying to get to work for two conditions,
=INDEX(data!$A$1:$A$100,SMALL(IF(AND(data!$C$1:$C$100=$B$3,data!$B$1:$B$100=$A$3),ROW(data!$A$1:$A$1 00)),ROWS($B$2:B3)))
Where am I dropping the ball here? I've also attached a workbook with this example in it.
Thanks in advance for wisdom and insight shared.
<data tab>
date | type | fuel
6/21/2011 | Blue Volvo | diesel
6/21/2011 | Red Citroen | electric
6/23/2011 | White Landcruiser | gasoline
6/23/2011 | Green Ford | diesel
6/23/2011 | Silver Toyota | electric
6/23/2011 | White Landcruiser | diesel
6/28/2011 | Blue Volvo | corn oil
6/28/2011 | Red Citroen | electric
6/28/2011 | Yellow Mazda | gasoline
6/28/2011 | Green Ford | diesel
7/2/2011 | White Landcruiser | electric
7/2/2011 | Silver Toyota | electric
7/2/2011 | Green Ford | diesel
7/2/2011 | Yellow Mazda | corn oil
7/3/2011 | White Landcruiser | diesel
7/3/2011 | Blue Volvo | gasoline
7/12/2011 | White Landcruiser | electric
<report tab>
White Landcruiser | electric
7/2/2011
7/12/2011
White Landcruiser | diesel
6/23/2011
7/3/2011
Red Citroen | electric
6/21/2011
6/28/2011
I'm running out of the door so have not looked at your file but in terms of Array / SUMPRODUCT functions - in general terms:
You can not use AND/OR functions
ANDs are performed by means of *
ORs are performed by means of +
In your example - an AND based test would look like:
alternatively - you can use embedded IFs=INDEX(...,SMALL(IF((test1)*(test2),ROW(...)),ROWS(...)))
the latter is arguably more efficient but pending version you are limited regards number of embedded IFs you may use.=INDEX(...,SMALL(IF(test1,IF(test2,ROW(...))),ROWS(...)))
Quickly glancing at your data set ... if returning numbers (ordered etc...) you don't really need the INDEX as you can use a single SMALL - ie use the Date field rather than Row and apply k to the dates.
Also... Pivot Table(s) ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Got it-
The below worked great to parse out the dates
={INDEX(data!$A$1:$A$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2),ROW(data!$A$1:$A$1 00)),ROWS($B$1:B1)))}
I'm working on copying the count value for each specific incidence of date-type-fuel.
Here is what I have so far working with nested IF statements:
=INDEX(data!$A$1:$A$100,IF(data!$E$1:$E$100=$B$2,IF(data!$C$1:$C$100=$A$2,IF(data!$A$1:$A$100=$C2),R OW(data!$D$1:$D$100)))),ROWS($B$1:B1))))
This would go in D2 on the report tab.
Help. File uploaded.
Thanks in advance.
<data tab>
date | type | fuel | count
6/21/2011 | Blue Volvo | diesel | 13
6/21/2011 | Red Citroen | electric | 12
6/23/2011 | White Landcruiser | gasoline | 11
6/23/2011 | Green Ford | diesel | 12
6/23/2011 | Silver Toyota | electric | 15
6/23/2011 | White Landcruiser | diesel | 12
6/28/2011 | Blue Volvo | corn oil | 17
6/28/2011 | Red Citroen | electric | 19
6/28/2011 | Yellow Mazda | gasoline | 12
6/28/2011 | Green Ford | diesel | 10
7/2/2011 | White Landcruiser | electric | 11
7/2/2011 | Silver Toyota | electric | 12
7/2/2011 | Green Ford | diesel | 15
7/2/2011 | Yellow Mazda | corn oil | 12
7/3/2011 | White Landcruiser | diesel | 23
7/3/2011 | Blue Volvo | gasoline | 12
7/12/2011 | White Landcruiser | electric | 13
<report tab>
type | fuel | date | count
White Landcruiser | electric | 7/2/2011 | 11
| | 7/12/2011 | 13
White Landcruiser | diesel | 6/23/2011 | 12
| | 7/3/2011 | 23
Red Citroen | electric | 6/21/2011 | 12
| | 6/28/2011 | 19
This,
=INDEX(data!$F$1:$F$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2)*(data!$A$1:$A$100=$ C2),ROW(data!$F$1:$F$100)),ROWS($B$1:B1)))
returns only the correct first match for all three conditionals and "#NUM!" thereafter.
Got it.
{=INDEX(data!$F$1:$F$100,SMALL(IF((data!$E$1:$E$100=$B$2)*(data!$C$1:$C$100=$A$2)*(data!$A$1:$A$100= $C2),ROW(data!$F$1:$F$100)),ROWS($B1:B1)))}
Is there a more concise solution?
Arrays are inefficient so you should only ever really use in moderation.
Here it would seem as though a Pivot Table would be the perfect tool for analysis - easy to use, flexible and efficient - see attached for working example (Pivot sourced from Dynamic Named Range)
Per your sample this is an XL2007+ file/pivot format.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks