Help me perfect a pup pricing formula!

Yeah what Starling said, I think what's happening to Selene right now is similar to what happened to Fox where it's everywhere because people got applicators and are breeding like crazy, but since there will be more special bases coming out, there aren't readily accessible applicators of it for a long time, and we know little about its pass rate its likely going to get rare again. 

On top of what @Kaz said, if the next base "looks better" than Selene, then chances are its price in the market will also skyrocket. Let's be real here, not that many people like Selene too much. The blue just doesn't fit with all the markings readily available in the game. Sure, you have the selene markings now, but what else are you gonna put on a selene base? Honey? Yeah, you've got some options when it comes to designs: "Pastel" and "Monochrome". If I learnt anything from the lunar event, then the next base will soar in popularity. I mean, it can't get worse than Selene! From a designer's point of view, anyway. Unless the next base is lime green.

Would you be willing to post your excel formula or a link to look at your sheet or something? I'm still figuring out how to do Excel formulas and it's currently breaking my brain a bit.

@Hollow yes!! Here is the formula as it looks right now:

=(20+(I2*5)+(J2*10)+(K2*15))*(if(C2="M","0.5","1"))*(if(L2="Clean","1","0.75"))* (ifs(M2>=550,"4",M2>=500,"3.5",M2>=450,"3",M2>=400,"2",M2>=350,"1.5",M2>=300,"1",M2>=250,"0.75",M2>=200,"0.5",M2<200,"0.25"))*(VLOOKUP(E2,Base!$A$1:$D$121,4,0))*(VLOOKUP(N2,Eye!$A$1:$C$121,3,0))*(if((AND(C2="M",H2="One",M2<300)),"0.5","1"))

I'm more than happy to explain any part of it that's confusing. I'm totally self taught with Excel, so even though it looks intimidating I promise it can make sense!!

Heres the breakdown for what cells contain what:

I2: standard marks (#)

J2: valuable marks (#)

K2: special marks (#)

C2: gender (M or F)

L2: breeding (Clean or Inbred)

M2: total stats (#) (all the stuff inside the ifs() function is saying, if its above this number then do this, etc)

E2: base (text) (this part of the formula is using the text in E2 to look up its modifier in another sheet)

N2: eye (text) (same as above, looking up the eye color modifier in another sheet)

H2: base rarity (one, two, or three) (used only in the potato modifier)

To break it down a bit further if that is helpful, here is the formula again in a mathy notation:

Price in SC = [20+(Mb*5)+(Mv*5)(Ms*5)] * [G] * [I] * [S] * [B] * [E] * [P]

And here is how the formula corresponds to each part:

(20+(I2*5)+(J2*10)+(K2*15)) = [20+(Mb*5)+(Mv*5)(Ms*5)]

(the markings determining what the "base" price is)

(if(C2="M","0.5","1")) = [G]

(the gender modifier, where males are worth half what a female is worth)

(if(L2="Clean","1","0.75")) = [I]

(the breeding modifer, where inbred wolves are worth 3/4 of a clean wolf)

(ifs(M2>=550,"4",M2>=500,"3.5",M2>=450,"3",M2>=400,"2",M2>=350,"1.5",M2>=300,"1",M2>=250,"0.75",M2>=200,"0.5",M2<200,"0.25")) = [S]

(the stat breakdown, using ifs to tier it out) (technically you could do this with a vlookup function just like the base and eye sections below but... fuck that, this is easier)

(VLOOKUP(E2,Base!$A$1:$D$121,4,0)) = [B]

(the base modifier, using the vlookup function to go fetch the corresponding modifier value from another sheet labelled "Base")

(VLOOKUP(N2,Eye!$A$1:$C$121,3,0)) = [E]

(the eye color modifier, using the vlookup function to go get the corresponding modifier value from another sheet labelled "Eye")

(if((AND(C2="M",H2="One",M2<300)),"0.5","1")) = [P]

(the potato modifier: using an and() function inside an if() to tell the formula "if a wolf is male AND only tier 1 AND has less than 300 stats, then multiply by 0.5; if not all of those things are true, then multiply it by 1")

And thats the breakdown! Seriously if you have ANY questions please do not hesitate to ask, this is complex and I looove explaining this to people because excel is so fucking cool once you get the hang of it.

Could anyone send me an updated link to the merchants guild discord? I'd love to join!! The formula looks amazing so far!!

@finley just sent you a message with the invite link! welcome welcome :D

Some thoughts on stats: I think it would make sense to have a more nuanced formula for stats, instead of just looking at the pup's raw stat total. (Also, sorry if this has all been mentioned before, since I haven't read the whole thread)

• Every pup has two favored stats from birth that will tend to get the bulk of its level ups. A pup with favored stats that are good for hunting (e.g., speed + agility for a chaser) is worth more than a pup with mismatched stats (e.g., agility + wisdom, which is not suited for any position on a hunting team). You can usually tell a pup's favored stats based on whichever stats are the two highest at birth (though not always, especially when the stats are close).
  
• Even among pups that favor a particular set of stats, stat distribution matters. A pup that strongly favors chasing (say, 120 speed + 120 agility at birth) is better than a pup that only slightly favors those two stats, even if the stat total is the same.
• Because you need three chasers on a hunting team, as opposed to only one finisher and only one stalker, I would expect that speed/agility pups would be worth more than strength pups or wisdom/smarts pups. I haven't done any searching of the TC to see if this empirically checks out.
• In addition to all of that, people need rainforest and glacier scouts, so I would expect strength and wisdom to be slightly more valuable stats than the others regardless of overall build.

I'm not sure how you would go about incorporating all of this in the formula! My first idea is that there could be a flat multiplier for pups with apparently favored stats in speed/agility, and a somewhat lesser flat multiplier for pups with apparently favored stats in wisdom/smarts. Possibly also a slight bonus for pups with 101 strength or 101 wisdom from birth, because they could scout rainforest or glacier immediately upon aging up.

Ideally I think you'd go even more nuanced than this, and measure what proportion of the pup's stats is concentrated in its relevant stats (whether it be str, or spd/agi, or wis/smarts) and have the multiplier be based on that; but that would be more complex to work out a good formula.

@Lionel thats REALLY cool and I REALLY like your analysis!! Basing stat price as a sum total is definitely not as helpful as this kind of a breakdown you're talking about. I do wonder how much the market reflects these kinds of desired stat blocks vs just rewarding highest overall stats. Perhaps something that takes into account total as well as if there is a concentration of stats in any role-useful areas?

Yeah, I think it makes sense to take into account total stats as one multiplier, and then concentration of role stats as a separate multiplier. Raw stats are nice from an aesthetic perspective, even for pups who aren't going to be hunters. (Personally, I just like big numbers. And when searching for pups I'll often put in a flat minimum like 300 or 400.) Having useful hunting stats is added value on top of that.

I might also suggest some tweaks to the raw stat formula. If I'm reading it right, 400 stats gives a multiplier of 2. 450 is 3, 500 is 3.5, and 550 is 4. So the biggest jump in value is between 400 and 450 stats, and after that it tapers off somewhat. I think this is a bit backwards: the higher you go, the harder it is to get an additional 50 stats, so the increase in value should be more. (Hopefully that makes sense.) There have also been a few 600-stat pups born, basically from leaderboard x leaderboard pairings, and those are future leaderboard candidates so I would put those even higher.

love this! would you be willing to put this in an online excel file so that we can all use it?