 |
|
|
|
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Suppose I have a spreadsheet with 3 columns, thus:
A,A,3
A,B,1
A,C,4
B,A,1
B,B,5
B,C,9
C,A,2
C,B,6
C,C,5
A cell with =lookup("ac",concatenate(a1:a9,b1:b9),c1:c9) returns the value
4, as is expected. But if you then sort a1:c9 by column b, the lookup
returns...3?
What exactly is going on, and how do I *really* get the associated value of
particular criteria regardless of how a sheet is sorted?
--
Tim Cook
http://empyrean.freesitespace.net
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Nevermind, found a better solution using index() and match()
--
Tim Cook
http://empyrean.freesitespace.net
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On Sat, 24 Jan 2009 12:11:58 -0500, "Tim Cook" <z99### [at] gmail com> wrote:
>Nevermind, found a better solution using index() and match()
But I do mind Tim. :)
I tried replying earlier but the server was down :(
I got values of N/A for a couple of lookup values.
Strange.
--
Regards
Stephen
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Not that anybody cares, but...
What I'm doing is assembling a spreadsheet of various data from the game
X-Tension (the argonopedia website has a few errors and is lacking some
other particularly relevant info)...you have space stations that require x
resources to produce y product, and I'm trying to identify the most
profitable things.
Each item has a minimum, average, and maximum selling price, which you can
trade between NPC stations to make enough money to buy your own factory...at
this point, if you buy all your factory's resources from NPCs, there's a
minimum, average, and maximum cost per unit created (assuming you buy at
those prices).
I have a set of columns with how much of each resource a station needs to
produce however many items per production cycle, and was trying to calculate
how many energy cells are needed per unit (energy cells being the lowest
common item that everything else needs; a Crystal Fab for a particular race
needs 16 food, 5 silicon wafers and 120 energy cells per 10 Crystals; the
food item in turn needs 15 energy cells and 12 raw ingredients, the raw
ingredient factory needs 5 energy per unit, and the silicon mine needs 24
energy cells for each wafer, bringing the energy cost of a Crystal to 480).
This way all the products can be compared vs. how long they take to make to
see what's the best way to make money.
While I could have referenced everything manually, if I ever wanted to sort
the sheet in a different way, everything would get mixed up...so (again
using the Crystal Fab as an example) ended up with
=(AJ60+W60*(INDEX(Stations,MATCH(W$1,Products,0),12))+AI60*(INDEX(Stations,MATCH(AI$1,Products,0),12)))/M60
as the formula (column AJ is energy cells needed, W is that race's food,
Products is named list of column E, AI is silicon wafers, and M is number of
items produced per cycle). Average cost of a Crystal is
=((AJ60*LOOKUP(AJ$1,Product,CrAvg))+(AI60*LOOKUP(AI$1,Product,CrAvg))+(W60*LOOKUP(W$1,Product,CrAvg)))/M60
with Product and CrAvg being named lists on another sheet that's just the
products and their selling prices.
And this is pretty much how I've been wasting my time for a few weeks. :P
--
Tim Cook
http://empyrean.freesitespace.net
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On Sat, 24 Jan 2009 19:07:38 -0500, "Tim Cook" <z99### [at] gmailcom> wrote:
>Not that anybody cares, but...
>
[Snip]
True, but...
It is strange that LOOKUP failed like this. I would like to know why.
>And this is pretty much how I've been wasting my time for a few weeks. :P
I spend hours using Excel when I am building scenes. It looks, to me, like you
are building an Excel version of a mainframe game I played years ago.
--
Regards
Stephen
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> Suppose I have a spreadsheet with 3 columns, thus:
> A,A,3
> A,B,1
> A,C,4
> B,A,1
> B,B,5
> B,C,9
> C,A,2
> C,B,6
> C,C,5
>
> A cell with =lookup("ac",concatenate(a1:a9,b1:b9),c1:c9) returns the value
> 4, as is expected. But if you then sort a1:c9 by column b, the lookup
> returns...3?
The lookup() function only works if the list you are looking up in is sorted
in ascending order. By sorting the cells by column B,
concatenate(a1:a9,b1:b9) is no longer is ascending order.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
"scott" <sco### [at] scottcom> wrote:
> The lookup() function only works if the list you are looking up in is
> sorted in ascending order. By sorting the cells by column B,
> concatenate(a1:a9,b1:b9) is no longer is ascending order.
The question is...why? What use is something that looks things up that
requires you do its work for it first?
--
Tim Cook
http://empyrean.freesitespace.net
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
>> The lookup() function only works if the list you are looking up in is
>> sorted in ascending order. By sorting the cells by column B,
>> concatenate(a1:a9,b1:b9) is no longer is ascending order.
>
> The question is...why? What use is something that looks things up that
> requires you do its work for it first?
It's because lookup always returns a "closest match" result, even if it
can't match exactly.
Use hlookup or vlookup instead, it lets you set a flag which will only
return exact matches, then the list does not need to be ordered.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
And lo On Mon, 26 Jan 2009 13:51:14 -0000, scott <sco### [at] scottcom> did
spake thusly:
>>> The lookup() function only works if the list you are looking up in is
>>> sorted in ascending order. By sorting the cells by column B,
>>> concatenate(a1:a9,b1:b9) is no longer is ascending order.
>>
>> The question is...why? What use is something that looks things up that
>> requires you do its work for it first?
>
> It's because lookup always returns a "closest match" result, even if it
> can't match exactly.
>
> Use hlookup or vlookup instead, it lets you set a flag which will only
> return exact matches, then the list does not need to be ordered.
Except you wouldn't be able to use the concatenate function with either of
those as they require the answer range to be included within the search
range ie. vlookup("ac", A1:C9, 3, False) would return nothing as no value
of "ac" exists within A1:A9 and you can't wedge the combined values into a
range. However if you created another column of concatenated values at C
with the results now moved to D then vlookup("ac", C1:D9, 2, False) would
return 4 regardless of sort.
Without creating a new column then perhaps only
=INDEX(C1:C9,MATCH("AC",CONCATENATE(A1:A9,B1:B9),0),1) entered as a array
formula would work, but yes I agree it is clumsy.
--
Phil Cook
--
I once tried to be apathetic, but I just couldn't be bothered
http://flipc.blogspot.com
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
|
|
