Two questions, reverse, variables.

QuestionEdit

i was the guy last time about variables, here is a fast resume of it:

Hello there, thanks for the help last time, you did a great job. But i have a new question:

I would like to make a variable from a point at -8388608 to 8388608

Now

This is for diablo 2, i am making a calculator which can calculate onscreen to offscreen.

if we have onscreen mana 1572863, we need to do this: (1572863-1048576) * 16 = offscreen mana = 8388592. That is no problem for me, but if i do like this: (1572864-1048576) * 16, the mana turns into 8388608 but it should turn into -8388608.

now let us try another value: (1614058 - 1048576)*16 = 9047712, but the actual result should be: -7729504 because the mana is now higher than 8388607 ( max is 8388607, 8388608 = rolled ( a.k.a negative ))

-8388608 - 0 = rolled range

1 - 8388608 = unrolled range

If you got any ideas on how this is possible i would appreciate it alot :) Let me try to explain it otherwise:

Its a game, called Diablo II, you might have heard of it might not. Anyways, nearly everything in the game has a rollpoint, when the roll is reached the attribute is either at 1 or 0. Now for my calculator i need to calculate something Called "Onscreen Mana to Offscreen Mana"

Here is the formular on how to:

(Onscreenmana-1048576)*16 = Offscreen Mana So if our onscreen mana is 1572863, then (1572863-1048576)*16 = 8388592 Offscreen mana

However, the roll point is mostly at 8388608 ( 8388607 is unrolled ) now if i as an example calculates this: (1572864-1048576)*16 = 8388608 Offscreen mana, but since mana is now 8388608 the real result should now turn into a negative: -8388608. So the variable would be from -8388608 to 8388608.

However, having 2295510 Onscreen Mana should now be something like this: (2295510-1048576)*16 = 19950944, this value should now turn like this: 3173728 Offscreen mana, because the max point we can get is 8388608 and the min point is -8388608. This means that the mana has been rolled once and then unrolled again and ends at 3173728 mana.

its like this: having mana 1616611

(1616611-1048576)*16 = 9088560, but the real resulst should turn out as a negative value = -7688656. This would be pretty much the same as like saying: 9088560 - 8388608 = 699952 = 8388608 - 699952 = 7688656, but negative = -7688656.

i hope this was enough explanation for you. Now for the Reverse Question i got this.

A1 = 8191 A2 = 75 <- 223 Mana Times A3 = 75 <- 53% ManaTimes

B1 = =ROUNDDOWN((A1+A2*223)*(1+0,53*A3;0)) = 1015327

Now i want it to be like this:

A1 = 1015327

B1 = 75 Times 223 Mana B2 = 75 Times 53% Mana B3 = Additional Mana Add ( 0 - 223 (max 222) ) <- i know how to make this my self, just to point it our for you.

You see that you are writing the value you want, and it will type in how many times you need each mana and mana%, its fine if 223 and 53% is added both same times, better would be if it could find the best resolution, i am talking about bytes taken now, let me try to explain it.

We have our characters, they can fill up 8191 bytes, 8192 bytes and game crashes. Anyways Mana% and Mana fills up both 2 bytes per time added, so it could be nice if it could calculate best possible way to gain the selected mana, example: 55 times 53% and 60 times 223 mana + 22 additional mana. Then if additional mana is bigger than 1 then +2 bytes. like this:

(B1*2)+(B2*2)+IF(B3>0;+2;0)

I am not sure if reverse and variables is even possible. Take the time you want, don't rush :)

Regars, Simon Juhl Birkedal.

AnswerEdit

I'm sure I don't fully understand your question, but from what I am reading it appears to me that you can solve both of the issues with IF() formulas or some combination of IF() formulas for example

=IF(A1<=1572863,(A1-1048576)*16,(-A1+1048576)*16)