Return Number in Different Column Based on Which Repetition it is

[XionVonko]

Hello Excel Experts,

I want to create a trade calculator, for a game I like, that will determine the value of what I want to buy in metal ingots (the first currency type). So if I want 50 metal ingots, 20 cementing paste (2nd currency type that is more valuable than metal), and a various amount of dinosaur eggs, kibble, and tamed dinosaurs (that is, trading an entire dinosaur), then it will return to me the value of the entire request in metal, cementing paste, and for each requested item.

I have it all working except for one part; it won't work for repetitions of the same animal. I have two sheets: the calculator itself, and a technical references sheet (for neatness' sake). In the tech sheet, I have a table that stores the value of every dinosaur's egg, kibble, and tamed value. The tamed dinosaur values vary based on what level the dinosaur is and its gender. Should the buyer want multiple dinosaurs of the same race but varying level and gender, I would have to have multiple tables to store each unique combination. That is, a level 50 Male Trex is less expensive than a level 50 Female Trex, and a Level 50 Trex is more expensive than a level 10 Trex.

If I wanted to buy a level 50 Male Trex, a level 50 Female Trex, and a Level 20 Male Trex, the tables I've reserved for up to 5 repetitions would have to detect the level, gender, and race of the unique dinosaur in order to determine its worth in metal.

My issue is that I don't know how to make excel detect where each repetition is so that it can return the level and a value to determine the gender.

The best I've got right now is =B3*Q3*IF(COUNTIFS('Trade Calculator'!D5:D25,'Trade Calculator'!D5,'Trade Calculator'!B5:B25,"<>0",'Trade Calculator'!A5:A25,"Tamed",'Trade Calculator'!B5:B25,"<>")>1,VLOOKUP('Trade Calculator'!D5,'Trade Calculator'!D6:F25,3,FALSE),IF(COUNTIF('Trade Calculator'!D5:D25,'Trade Calculator'!D6)>1,0,0))

Where B3 and Q3 are the base egg price of the dinosaur, and the gender multiplier respectively. The huge third part is the part that is supposed to detect the second valid repetition. A valid repetition is one which has a quantity (not 0 or blank), has "Tamed" selected from the list, and has the same dinosaur name as another cell in the list. Therefore, this third part is supposed to detect that there is a valid repetition somewhere in the request, determine where it is, return the level, and multiply it so it gives the metal value of the tamed dinosaur.

I'd appreciate all the help I can get!!

 

[mikerickson]

I'm not sure what you mean by "repetitions".

A typical invoice from a seller has columns

Quantity, Item, Cost per item, Extended Cost


Where Extended Cost is Cost per item * Quantity.

At the bottom, the Total Cost is the sum of all the Extended Costs.

So even if an item is repeated on different lines of the invoice, the total cost will be accurate.
Is that the "repetition" that is giving you problems?

Or are you having trouble describing each of the variations of Level, Gender and Type of dinosaur.

It may be best to treat each of those possibilities separately and create a list of all the possible dinosaurs and each cost.
Rather than calculating the "Cost per Item" on each line of the invoice, just use VLOOKUP to refer to that table.

 

[XionVonko]

I'm having trouble solving for the variations of level and gender of the same dinosaur. I have a table with each dinosaur having its own row and the columns are the prices of eggs, kibble, and tamed. In order to accommodate more than one dinosaur of the same race but different level and gender, I'd have to have another row of the same dinosaur but different tamed cost. To try and fix that, I've created a total of 5 column sets for tamed animals. The idea was that the first column set (the set being two columns with a buy price and sell price but that's not relevant, just so you know why I refer to it as a column set) would have the price of the first listed dionsaur, the second would have the price of the first repeated dinosaur, and the third would have the price for the second repeated dinosaur, up to a total of 5.

Making a large table of all the possibilities is unrealistic because the levels can break 200 (thus I'd have to accomodate 1-200+ levels) and the gender would double that amount.

I've found twice now that part of the issue is that the MATCH function cannot match based on multiple criteria; it only matches to the one thing you put in the first spot. I don't know how to work around that.