smoothly create an exponential graph in which the relation between the accuracy and evasio

[CVDom]

Hey all,

Attachment 840665

I have this set of test numbers. I'm trying to make a formula which outputs a chance to hit. The current formula I have is this: "=IF([@Accuracy]<=0,[@Accuracy]*([@Evasion]+1),[@Accuracy]/([@Evasion]+1))", which is giving the results below in the chance to hit category.

The trouble I'm having is figuring out how to adjust it so that either:
1: the weighting on evasion is higher: as in looking at 10% and -40% the chance to hit would be around 20-30%
Or 2: When evasion is lower than accuracy (cases being eg.1: 0% accuracy and -50% evasion should = 25%-50% chance to hit and eg.2: -10% accuracy and -40% evasion should be a positive chance to hit.) the chance to hit becomes positive.

The result should be a smooth curve that is not linear I.E. not subtractive. Currently it's a smooth curve except for 3 points, those being: 150% 100% 75%, 0% -50% 0%, and -150% 100% -300%

At least in the cases of the first and third I think it makes sense, as the numbers change. But correct me if that assumption is wrong.

So yes, can someone help write a formula that does that? The summary being to smoothly create an exponential graph in which the relation between the accuracy and evasion is a smoothly changing chance to hit, in both the positive and negative directions.

Accuracy	Evasion	Chance to Hit
300%	-50%	600%
290%	-40%	483%
280%	-30%	400%
270%	-20%	338%
260%	-10%	289%
250%	0%	250%
240%	10%	218%
230%	20%	192%
220%	30%	169%
210%	40%	150%
200%	50%	133%
190%	60%	119%
180%	70%	106%
170%	80%	94%
160%	90%	84%
150%	100%	75%
140%	90%	74%
130%	80%	72%
120%	70%	71%
110%	60%	69%
100%	50%	67%
90%	40%	64%
80%	30%	62%
70%	20%	58%
60%	10%	55%
50%	0%	50%
40%	-10%	44%
30%	-20%	38%
20%	-30%	29%
10%	-40%	17%
0%	-50%	0%
-10%	-40%	-6%
-20%	-30%	-14%
-30%	-20%	-24%
-40%	-10%	-36%
-50%	0%	-50%
-60%	10%	-66%
-70%	20%	-84%
-80%	30%	-104%
-90%	40%	-126%
-100%	50%	-150%
-110%	60%	-176%
-120%	70%	-204%
-130%	80%	-234%
-140%	90%	-266%
-150%	100%	-300%
-160%	90%	-304%
-170%	80%	-306%
-180%	70%	-306%
-190%	60%	-304%
-200%	50%	-300%
-210%	40%	-294%
-220%	30%	-286%
-230%	20%	-276%
-240%	10%	-264%
-250%	0%	-250%
-260%	-10%	-234%
-270%	-20%	-216%
-280%	-30%	-196%
-290%	-40%	-174%
-300%	-50%	-150%

Thanks!!

[AliGW]

You were told to improve the title of your thread, NOT open a new thread.

I will close the original - please do as you are instructed in future. Thank you.

[CVDom]

Stop posting on the threads and message me directly if there is an issue.

[AliGW]

That’s not the way it works here. Rule violations are dealt with in the thread in question. You are and will not be treated any differently to any other member here.

[MrShorty]

How much of this is math and how much is spreadsheet programming?

Currently, it appears that accuracy is the independent variable, and evasion and chance to hit will be functions of accuracy.
Evasion looks like a "sawtooth" kind of function, where it alternates between linear increasing function (y=mx+b, m is positive) and linear decreasing functions (y=mx+b, m is negative).
Chance to hit is a composite function of accuracy and evastion. Below 0, chance to hit is accuracy/(evasion+1) which becomes accuracy/(sawtooth+1). Naturally, there will be "discontinuities" at the "points" of the sawtooth. Above 0, chance to hit is accuracy*(evasion+1) which becomes accuracy*(sawtooth+1). Again , there will be natural discontinuities at the "points" of the sawtooth. There will also naturally be a discontinuity at 0 due to using different functions above and below 0.

It's not clear to me what you want to be different. I would tentatively suggest that getting rid of the current discontinuities involves getting rid of the "if above 0 do A and below 0 to B" choice, along with getting rid of the discontinuities (sawtooth points) in the evade curve. I'm not sure I understand what you want to happen differently. Help us understand what should be different, and we should be able to help you program those differences into the spreadsheet.

[CVDom]

Evasion is also an independent variable. I chose the sawtooth pattern to see a variety of combinations while maintaining some larger pattern for viewing purposes.

Thank you for confirming my hypothesis about the discontinuity points. Though I still think there's a way to remove it mathamatically at 0 as I've done so before with functions like: =(LOG(ABS(AL69)+1,10)*SIGN(AL69)) where column 1 is 10 to -10, or the same concept with this: =(0.01*ABS(AX50)^2)*SIGN(AX50).

Anyway what I'm looking to be different is that let's say you had an accuracy of -10% and evasion of -50% that should result in a positive number. But because I have to add 1 to evasion for it to work when it's equal to 0, dividing those two numbers no longer results in a positive.

I apologize, I don't really know how to describe it well. I'd like a logarithmic S curve if the data were linear 100% to -100% . But it's more complex because there are two variables. For example if you change evasion to match the values of accuracy, there are many problems, there's a divide by 0 and the chance to hit outputs are flipped when they shouldn't be.

I agree the IF statements are a problem, but I'm not sure how to write it with pure math.

I'd like to create a formula which scales the numbers together in a satisfying way which makes sense. - That's what I want but I can't describe it in terms of math or formulas.
More explanation is that: If your evasion is so bad as to be negative and lower than a blind man's negative accuracy when the blind man attacks you would dodge into the attack thus the chance for a hit is a positive. But if you were good at evasion you'd dodge properly. But not so well as to negate all possibility of a hit. The opposite is true, if I'm extremely accurate and you're so bad at evasion as to be negative then my chance to hit is even higher. If I'm accurate and you're good at evasion then my chance to hit is lower. Lastly if we are both horribly inaccurate and negative at evasion then my chance to hit is still negative because we are equal in accuracy and evasion, or if your evasion is say -10% and my accuracy is -50% then the chance to hit should still be negative.

This sounds like subtraction... Speaking of which after writing that I just went back and tried this: =LOG(ABS([@Accuracy]-[@Evasion])+1,10)*SIGN([@Accuracy]-[@Evasion])

Which actually works very well (tested on a linear set of numbers it forms a beautiful S curve). However, if you paste and replace the original formula with the workbook I provided you get sections of flat, which makes sense, but is there any way I can make a hybrid of the two so as to not have perfectly flat sections? To represent different values have different strengths?

Like originally 10 and -40 results in 26, whereas now it's 27 for all where the difference is 50 etc.

[CVDom]

I guess I'd like to do a Fourier transform (adding two waves together), but of the inverse of evasion, where by combining these waves (numbers) they increase the amplitude of eachother. to which I could then apply a log function as a sort of limiter.

Before the LOG limiter a rough drawing of it would be something like this: Screenshot 2023-08-24 142016.png where white is accuracy, orange is evasion, yellow is evasion inverted, blue is them amplified together.

[MrShorty]

Here's an older thread with some ideas on programming Fourier transforms into a spreadsheet: https://www.excelforum.com/excel-gen...verse-fft.html

[CVDom]

Thanks MrShorty, I think I'm wrong in saying this is a Fourier Transform. There is no time value. It's 2 columns of numbers I'd like to combine in a similar fashion to what I've seen done in videos about adding sinewaves.

But honestly I have no idea what I'm reading about there.

Here is an image of what I'm looking for:
Attachment 840712
where white is accuracy, orange is evasion, yellow is evasion inverted, blue is them amplified together.

[MrShorty]

Attachment failed to attach. Be sure to follow the instructions at the top when adding attachments.

Never mind, I see the picture in the earlier, edited post.

[CVDom]

I found this works well until 0 and below: =([@Evasion]*-1)*[@Accuracy] any way to get it to work when accuracy is at 0 or below?

[MrShorty]

I'm not sure what the horizontal axis is in this picture (other than row number in the spreadsheet). White/accuracy is a simple straight line white=mx+b (m is negative). Orange/evasion is still a sawtooth, though you are talking about making it a smoother sine/trig function orange=A*sin(B*x+C)+D. Yellow is inverse evasion, which appears to mean Yellow=-orange=-Asin(Bx+C)-D. The end goal of this exercise is to find an expression for blue/chance to hit in terms of the other functions. I'm not sure what you want this expression to be. It could be something like blue=K*white+L*orange, but K and L do not look like constants, they look like they, too, might be functions of x, but I have no idea what they ought to be. Or maybe a more complex composite function.

It seems to me that, once we figure out what the composite function ought to basically look like with some adjustable parameters, we can then program that into the spreadsheet and adjust parameters until we get the exact shape of the blue curve that you want.

[MrShorty]

What does it mean for the function to "work at 0 and below?" I see no mathematical or programmatic reason why that function wouldn't work below 0, but I'm also not sure what "work" means.

[CVDom]

It doesn't work as in the result is wrong.

The picture is an explanation of what the end result should look like, rather than looking for formulas to make it. The horizontal axis is the sequential number corresponding to the row as in the graph with the attached excel file. The vertical axis is the percentage. Looking at the attached excel file the graph has as linear line going down and an M shaped line. Those two lines are the accuracy and evasion, respectively. The image I drew is to explain what I'm looking for which is a formula that combines those two columns into a sensible series of numbers represented by the graphs.

The hand drawn graph has, the accuracy: white, evasion: orange, evasion inverted: yellow, and the result of combining them (the only thing I actually want): blue.

The inversion is so that multiplying the numbers results in the correct amplification.

[MrShorty]

I tried blue=K*white+L*orange+M*rownumber with K=0.9, L=-0.8, and M=0.04, and the blue curve started to look like a stiff "W" shape roughly similar to your smooth blue curve**. If we had a smoother curve for orange rather than the sawtooth that we currently have, that would smooth out some.

But at that point, I'm really just guessing. It still sounds like you are trying to figure out some kind of composite function. I expect that, once we know what the composite function ought to look like, we will be able to put that into the spreadsheet.

** If you are unsure how to program this into the spreadsheet, I entered 0.9, -0.8, and 0.04 into L1:L3. Then I entered =$L$1*A1+$L$2*B1+$L$3*ROW(A1) into G1 (copy/paste/fill down) and added that column to a scatter chart with the other columns. Try that, and tinker with the values in L1:L3.

[CVDom]

Thanks for the help man!

I think you're confused. Both the initial two columns are independent variables. The sawtooth pattern is only there because I chose those values to as to see what a wide variety of number combinations would do. I'm trying to make a formula that amplifies these two columns together like adding waves, then once I have the correct formula I can change the first two columns to be any combination of numbers and the shape doesn't matter. - I've only applied a shape here for sake of explaining what formula I need.

Indeed I'm looking for a composite function. The closest I've come has been: =IF([@Accuracy]<=0,[@Accuracy]*([@Evasion]+1),[@Accuracy]/([@Evasion]+1)) and I've been unsuccessful so far at removing the IF statement and making it purely mathematical, but secondly not all the numbers derived from that equation are correct. Any time evasion becomes a negative value the result isn't correct.
The effect should be that at 0 (horizontal) on the graph in the doc provided that's when the yellow line should do a vertical drop and then continue until going back up when the evasion drops again. And just before that drop the yellow line should be nearly flat. Essentially ending up similar to my drawing.

[MrShorty]

I could be confused, I certainly am having trouble understanding the exact behavior you want from the final function.

I'm not sure I can recommend anything without some understanding of the composite function you want. Right now, it feels like I am just looking at charts and numbers and trying to guess at a relationship.

What I might suggest at this point is to identify any key points in the table and write down what value the final function ought to have at that point. Then see if the "shape" echoed by those key points suggests anything.

[CVDom]

Hey! All,

I believe I figured it out.

Attachment 841669

The Dark Blue and Dark Yellow Lines are what I was looking for. Bumps on the graph at the appropriate values, due to the numbers rather than the formula.

The smoothness/curve of the graph has nothing to do with the values selected those numbers were picked mealy to evaluate the formula, and the shape of the graph was only to be used as an evaluation/description tool.

I wish I could attach the sample document for all of you to look at.

Regardless,

The poly trend line of the blue graph is what I was hoping for but it relies on the trend of the data rather than individual number combos, which is what the formula should calculate: individual combos of numbers. I was merely looking for a mathematical formula which could in a sense (not literally) round the numbers to have a non-linear relation. That is to say that specific values say an accuracy of 0% and an evasion of -50% would result in roughly a chance to hit of 25% and that 10% accuracy and -50% evasion results in roughly 35% chance to hit, but NON linear, that is to say as the numbers expand in difference the result is exponentially larger (but then clamped with a LOG function, to flatten the extremes of the "curve")

I think I am happy with the formula I figured out. I'm not sure if it's perfect, but I'm happy with atm. Here is the formula:
=((ABS([@Accuracy]) * [@Evasion]) - ([@Accuracy]*1.165)) * -1 (This formula has no LOG and is the first step in the final calculation.)
=(LOG(ABS([@[Chance to Hit]]*2)+1,3)*SIGN([@[Chance to Hit]])) (This is the next step in the final calculation.)
=LET(a,(ABS([@Accuracy]) * [@Evasion] - [@Accuracy]*1.165) * -1,b,LOG(ABS(a*2)+1,3)*SIGN(a),b) (This is the final combined calculation.)

Therefore, in 1 cell I am able to calculate the result of two numbers in a logical way using purely math (before the log function).

If someone, has a better/more satisfying solution please let me know.

Thank you all very much!

[CVDom]

Apologies,

Couple updates:

The above first formula isn't correct as in it's providing values that don't make sense. For example Accuracy of 60% and evasion of 10% should result in 55% (ish) chance to hit, but instead the above formula results in a chance to hit of 64%. The rest of the values look good, but if one is wrong then they're all wrong, - which means the formula is wrong.
I've changed it to this: =((ABS([@Accuracy]) * [@Evasion]) - ([@Accuracy])) * -1 (which fixes the above problem but creates a new one: being that when accuracy is above 100% and evasion is 100% or above the result is always 0%, which I feel is incorrect. I think it should be like 25% of how ever much above 100% the accuracy is. which is why I added an accuracy multiplication of 1.165 originally. But that screws up the bottom figures where accuracy is negative and evasion is negative, meaning the chance to hit should be less negative than the input accuracy value)

I fixed that issue with this: =((ABS([@Accuracy]) * [@Evasion]) - (IF([@Accuracy] >= 0, [@Accuracy]*1.165, [@Accuracy]/1.165))) * -1
But that leaves the issue of 60% and 10% I described above. Also I'd love to be able to leave IF statements out of the equation.

Any solutions?

[MrShorty]

I'm none the wiser.

Since it appears that you have the ability to program whatever you settle on into the spreadsheet, it appears to me that the real question is a math/algorithm development question. I still don't understand what you want to do differently. In hopes that it will help, here are some things I see.

First, translating your excel formulas into more generic algebra formulas (maybe it will be easier to talk about them that way), you have an equation for "chance to hit" (your LET() function calls this a) based on "accuracy" (let's call this x), and "evasion" (let's call this y).

a=-1*(y*abs(x)-f*x)=f*x-y*abs(x) where f is a "fudge factor." You've suggested values of 1 and 1.165 and 1/1.165 for f.

then the final calculation (your LET() function calls this b) is

b=log3(2*abs(a)+1)*sign(a).

Assuming I've correctly interpreted your LET() function, I entered these formulas into the attached spreadsheet (tab "post18and19") using your original data.

I've also added tabs to look at how a changes for different values of f ("fudgefactor") and a tab for contour plots to look at how a changes with accuracy at fixed values for evasion and how a changes with evasion at fixed values for accuracy. I don't know if these will help, but my hope is that they can be used to illustrate what you want to be different from what is currently happening.

I've added some target values you have suggested from throughout the thread, but perhaps they will help in explaining what should happen differently.

Sorry to spam you with a bunch of charts and analysis, but I don't know that we can help much until we can really understand what you want to have happen.