Transposing a table view

Hi,

Pls see attached WB.

Preferable solution using only Sheet Formulas only.

Thanks, Elm

One way without helpers etc avoiding arrays and double evaluation

Please Login or Register  to view this content.

Thanks a lot, or as we use to say: Takk a einhver fjöldi

Elm

***************************************
Added Later:

What can be put in the formula instead of using the Choose function.

This is only out of curiosity.

Thanks

Nothing easily... the use of CHOOSE is to negate need for double evaluation (to handle errors) - with helpers or repetitive calcs you can dispense with it but in the context of your question (and limitations thereof) the approach is worth utilising.

The CHOOSE is one way of handling errors and is in essence used to populate a lookup_vector with 2 values, a null string and the output of the INDEX formula so you end up with

Please Login or Register  to view this content.

If the formula output returns an error it will be ignored by the LOOKUP and the Null will be returned as that is the last text string within the lookup_vector, if on the other hand the INDEX output is valid the LOOKUP will return that value as that is the last text string in the lookup_vector, eg:

Please Login or Register  to view this content.

If you want to dispense with CHOOSE, given sample file, then you could just as easily use a COUNT test initially to determine as to whether or not result required, ie:

Please Login or Register  to view this content.

In reality the above will also prove to be the more efficient option.

Per your sample where you're returning numerics or nulls to account for errors...

Utilising CHOOSE in conjunction with LOOKUP to negate double evaluation

a) won't really shorten the formula in this instance given the original is not that long

b) given you're returning mixed data types (numbers, text) whereas the LOOKUP method is designed for single data type (ie always number or always text)
more specifically a Null is a text string


In terms of alternatives...

If (and only if) Qty is never 0 then you can just use SUMIF and simply apply a Custom Number Format to display / print 0 as blank.

In the case of the third table where you have a matrix and column criteria may not exist then you SUMIF not viable.

IMO you should do as you are and validate the existence of the criteria first before conducting any calculations . that is another disadvantage of the LOOKUP approach - ie the "calculation" is performed at all times and if it's an expensive one that can be easily avoided it's not really an optimal approach.

I'm not sure I really understand... the earlier posts detail how the approach works... I'm not really sure what else you're expecting to find.

To reiterate then...

With regard to use of CHOOSE in context in LOOKUP to circumvent need for double evaluation:

-- CHOOSE is used to create a lookup_vector of 1 to n values

-- the "default" value (first value) should be of the same data type as that of the criteria

-- the Criteria value of the LOOKUP must exceed all values within the lookup_vector

To illustrate

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,10*10))

generates a 2 value lookup_vector which will evaluate to {0,100} the result of the LOOKUP will be 100 as this is the last value in the lookup_vector of the same data type as the criteria.

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,10*"a"))

will return 0 as the 10*"a" will generate an error value and given this is not of the same data type as the criteria (number) it is ignored and thus 0 is the last value of the same data type as the criteria found in the lookup_vector

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},"",10*"a"))

will generate an error as neither value in the lookup_vector are of the same data type as the criteria.

The last example illustrates my point regards your earlier formula which generate Null (text) for error or number if valid - given that requirement (mixed data type output) the LOOKUP/CHOOSE approach is not really viable.