## is there a formula that will allow me to pull data from one google sheet tab to another based on a if contains conditional

#### Stack Exchange Network

Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

## algebraic number theory – Help with discovering a formula for Earth2.io

I’m not sure if this is the right place to ask for help but I would love for someone to take on the challenge of figuring out a formula being used by the website earth2.io.

It a new VR game that is selling land on a virtual version of earth.

The concept is that longer term, they will release a 3D environment and people can build on the tiles that they have bought etc.

The challenge that I have is figuring out how they are calculating their ‘new tile price’

As more tiles are purchased by users, the price increase. Prices are split by country and start at 0.10 per tile. As an example, the USA has sold 620,110 tiles and the new price is currently \$48.86

Currently, the best guess for the formula for this calculation is price = 0.1e^x (where x is the number of tiles sold).

This does seem to work at lower purchase levels but the margin of error starts to exceed 1% when over 800k tiles have been sold so I assume there is another factor in the maths.

There is a lot of data available for current prices within the game and I have a spreadsheet that I can share if it helps. There are also site such as https://www.earth2stats.xyz/ which include tables which can be accessed if needed.

If anyone is able to figure out what formula they are using, with a very low margin of error that would be amazing.

If this request for help is not inline with the site, I apologise – I’m a new user and hoped that there would certainly be a lot of smart people here who perhaps fancy a challenge!

Thanks in advance and happy to answer any questions if anything is unclear.

## house rules – How can I write a formula for this houseruled roll in AnyDice?

The trick to modelling these kinds of dice pool mechanics efficiently in AnyDice is to make use of the fact that each die in the pool is rolled and counted (and possibly rerolled) independently of the others.

This allows us to first calculate the probability distribution of successes for a single die of each size, and save it as an AnyDice “custom die”. We can then add together any combination of these custom dice to get the distribution of successes for any dice pool.

So, let’s see how to actually do that. First, we need a function that will calculate the success distribution for one die. If we didn’t have the explosion mechanic, this would be really easy to do — just something like:

``````function: roll ROLL:n target TARGET:n {
result: ROLL >= TARGET
}

output (roll d6 target 5) named "single d6, no explosions"
``````

The trick here is that, when you call a function expecting a numeric parameter (i.e. one marked with `:n`) but actually pass in a die (like `d6`) instead, AnyDice will automatically call your function for every possible result of rolling the die and collect the results into a new custom die.

Actually, for the simple example above we could’ve just done `output d6 >= 5` and skipped the function entirely. But we’re going to need a function to implement the explosion mechanic anyway. The new trick there is that we can have the function call itself recursively when an explosion occurs, e.g. like this:

``````function: roll ROLL:n target TARGET:n explode MAX:n limit LIMIT:n {
if ROLL = MAX & LIMIT > 0 {
result: (ROLL >= TARGET) + (roll dMAX target TARGET explode MAX limit LIMIT-1)
} else {
result: (ROLL >= TARGET)
}
}

output (roll d6 target 5 explode 6 limit 3) named "single d6, max 3 explosions"
``````

Note how I’m reducing the `LIMIT` parameter by one for every nested function call, thus ensuring that the rerolls will stop when the limit goes down to 0. Also note that I’m using the `MAX` parameter for two different purposes: both to specify the roll on which a reroll should happen and to define the size of the die used for the reroll. I could’ve used separate parameters for those, but that would’ve made the function call even more verbose.

Anyway, now that we have a function that lets us roll a single die of any size and count the number of successes, we can do the for each of the die sizes and save the results as custom dice:

``````PROTAGONISM: 2

SIX: (roll d6 target 5 explode 6 limit PROTAGONISM)
EIGHT: (roll d8 target 5 explode 8 limit PROTAGONISM)
TEN: (roll d10 target 5 explode 10 limit PROTAGONISM)
``````

And then we can roll any combination of those custom dice that we want to get the distribution of success counts:

``````output 2dSIX + 2dEIGHT + 1dTEN named "2d6 + 2d8 + 1d10, protagonism (PROTAGONISM)"
``````

And there we have it. Here’s what the output of the complete program looks like:

One minor detail worth noting in the complete program linked above is that I also added in the line:

``````set "maximum function depth" to PROTAGONISM + 1
``````

Normally AnyDice limits the number of nested function calls to 10, and it turns out that this isn’t quite enough if you set `PROTAGONISM` to 10. Setting the limit to one more than the protagonism value is sufficient for our purposes.

However, it also turns out that the protagonism value really doesn’t have all that much effect is practice. For example, if we run the code above in a loop for all protagonism values from 1 to 5 and plot the results as graphs, they look like this:

Notice how all the graphs are basically drawn right on top of each other, and that the mean success count shown in the legend is also almost the same for all of them (approximately 2.56 for protagonism 1, 2.60 for protagonism 2 and 2.61 for all higher values).

Basically this is because getting more than one reroll in a row is very unlikely — a one in 36 chance for two consecutive rerolls on a d6, less for bigger dice and much less for longer chains of rerolls. So basically, while it might feel really awesome to get the rare triple explosion on a roll, it happens so rarely that it doesn’t really make any difference to the averages. And with your success counting mechanic, the reward isn’t even all that much — all a reroll gives you is a (chance for) a single extra success point, which may not even make any difference if you’re not just under the difficulty target.

Honestly, I’d recommend either rethinking the protagonism mechanic to give better rewards, or get rid of it completely and just allow unlimited rerolls.

## search trees – How to avoid division by zero in UCT formula?

In step 2 (expansion) of the MCTS algorithm, one or more child nodes are created, and one node named C is chosen among them. The simulation step is applied to C giving it a score of 0/1 or 1/1. What happens to the other newly created leaf nodes? I assume they get a score of 0/0, which will not work very well with the UCT formula that decides which nodes to explore by maximizing wins / total simulations + some other stuff which will cause a division by zero when evaluating this expression. How is this reconciled?

## worksheet function – Excel – Use Formula In Different Cell From Referencing In If Statement

I am currently working on a listing template that uses different things like a title generator. This generator uses a ton of if statements based on the data that it is given. I have broken this generator up into different functions for each data type so that I can edit these individual functions for later use.

What I am having a tough time doing is creating an algorithm that references a formula in a different cell. I would like this algorithm to execute a specific formula based on the data that is given. For example, I can have my data type in column A and the data formulas in column B. For each item in column A, there is a unique formula for it in column B. This is what I have in my functions sheet. In a different sheet, lets call it data sheet, I have a table that contains a lot of different data. One column is data type, and it contains the same data types as in my formulas sheet. In this sheet, I would like to execute the exact formula in the formulas sheet based on the data type that I have in this data sheet.

I used formulatext() and it returns the formula that I need. My problem is that I would like to execute this formula instead of receiving a long string of it.

## multivariable calculus – Using the chain rule to derive a formula for partial derivativs in spherical coordinates

For the spherical coordinates;

$$x=rsin{ theta} cos{phi}$$

$$y=r sin{theta} sin{phi}$$

$$z=rcos{theta}$$

Where $$theta$$ is the polar angle and $$phi$$ is the azimutal angle, Is it possible to uae the chain rule for multivariable calculus in order to derive an expression for the partial derivatives $$frac{partial}{partial x}, frac{partial}{partial y}$$ and $$frac{partial}{partial z}$$ in terms of $$frac{partial}{partial r}, frac{partial}{partial theta}$$ and $$frac{partial}{partial phi}$$ ?

More specifically, I have an operator in cartesian coordinates that I want to write in the spherical coordinates (without using results from the internet, I want to understand how to derive the expression). The operator is;

$$L_x=-ioverline{h} (y cdot frac{partial}{partial z} – zfrac{partial}{partial y})$$

That is the angular momentum operator for the $$x$$ coordinate in physics (up to the fact that Im not sure how to write h_bar properly)

Im not sure what method can I use to replace the partial derivatives in cartesian coordinates in the operator, with the spherical partial derivativea.

## I will do excel spreadsheet formula, vba, macros, chart and pivot tables for your assist for \$10

#### I will do excel spreadsheet formula, vba, macros, chart and pivot tables for your assist

Hello Everyone,

We are specialize in Excel solutions. We have 5+ Year’s experience. Our services are fast as we typically deliver in 48 hours (even for 3-day packages).

We also take pride in providing after sales assistance. 100% Satisfaction Guaranteed and We will always be there for your queries.

We are available almost all hours of the day and would be happy to assist you with any Excel issue(s) you may have.
Services Offered:

• Customized Automated excel spreadsheet with Formulas, Tables or Graphs.
• Customized Automated Google Sheets with Formulas, Tables or Graphs.
• Basic updates to existing worksheets (fixing formulas, re-organizing data, etc.)
• Amortization and Other Calculators
• Sales, Expenses and other Trackers
• Excel VBA
• Microsoft Excel macro
• Payroll Software
• Invoices/Pay Slips
• Buttons, Drop-down lists, menu, forms.
• Creating User Forms
• Charts
• Vlookup / Hlookup
• Countif / Countifs
• Sumif / Sumifs
• Removing Duplicates
• PDF to Ms-Excel,
• Excel to PDF
• Data Validation
• Data Analysis
• Pivot Tables
• Private Consultations
• 100% Satisfaction assurance
• 100% Quality assurance

Thank you for reading, We are looking forward to working with you!

Thanks a lot!!!

.(tagsToTranslate)microsoft(t)excel(t)vba(t)formulas(t)data(t)entry

## Google Sheets formula IF or SUM with conditional formatting

Im looking for some much needed help after hours of looking.

I have lets call it a task list of things to do. Columns A B and C have text, column D is a number (hours it will take me to complete task) and column E is empty and use it to ‘tick’ off my list by typing ‘x’ with conditional formatting this in turn ‘greys out’ the whole row – and it also removes the number on this row from my chart, which is exactly what I want, but I also want my total values of column D to reflect this. I put my SUM in column F.

I want to SUM (or add together all values in column D) which is fine with a simple SUM formula, but I want sheets to minus any number that I have completed with my ‘x’ in column E. Im sure it must be possible as it does this automatically with any chart I add, but just want 1 cell with the answer?

FYI the conditional formatting I use is =\$E10=”x” making the row grey if theres an ‘x’ there and I also use =ISBLANK(#REF!) if no ‘x’ is present

Any help is most appreciated

## automata – Determine if a Kripke structure satisfies an LTL formula

I am trying to understand the correlation between LTL and Kripke structures. Zoom classes are very tough to follow and i can’t find anything online. I have the following Kripke structure with q3 as start. I have to demonstrate that it either satisfies or not the following LTL formulae.

1. a U b

2. a U X(a ∧ ¬b)

3. X¬b ∧ G(¬a ∨ ¬b)

I would really appreciate if somebody could explain to me the steps to prove that a KS satisfies a LTL formula.

## ordinary differential equations – Formula for the derivative of finite power series in reversed order of terms.

I wanted to solve the polar part in Schrödinger’s wave equation for the H-atom by direct substitution of functions of form:-
$$Theta_{lm}(theta) = a_{lm} sin^{|m|}theta sum_{r≥0}^{r≤(l-|m|)/2}(-1)^rb_r cos^{l-|m|-2r}theta$$
The $$a$$‘s are normalisation constants, no problem there. However, the problem of determining the $$b$$‘s ultimately drops down to finding the first and second derivatives of the polynomial in $$z=cos theta$$:
$$P(z)=sum_{r≥0}^{r≤(l-|m|)/2} (-1)^rb_r z^{l-|m|-2r}$$
Which is a finite power series written in decreasing order of powers. I couldn’t find a formula for so (well sometimes I get that dumb), but I think it does exist, maybe some reference book or website. I emphasize that what I’m doing is right the reverse of Frobenius-method. Thanks.