Excel Logic Problem - Circular Reference

[darkDOS]

Not strictly a language specific question, but a logic problem I think best fits to Programming.

I am trying to make an Excel sheet that helps plan a character in a game.

 

The Total Stat is the sum of the Base Stat and Bonuses applied.

The game has items that can only be equipped if the Total Stat meets or exceeds a value.

Once the item is equipped, it can add to the Bonuses that in turn increases the Total Stat.

 

 

I cannot for the life of me figure out the logic that avoids a circular reference since the total is dependent on the bonuses which is turn dependent on the total and so on... 

[Eigenvektor]

23 minutes ago, darkDOS said:

I cannot for the life of me figure out the logic that avoids a circular reference since the total is dependent on the bonuses which is turn dependent on the total and so on...

Surely there's some kind of "base total" that is used to calculate the bonuses, which are then used to calculate the overal total. You need to distinguish between those two values.

 

If your total increases your bonuses, which in turn increase your total, you have an endless loop which increases continuously. That would mean it would always go towards infinity.

Here's how it should work:

BASE = some value
BONUS = $BASE * factor
TOTAL = $BASE + $BONUS

EXTRA-BONUS = IF ($TOTAL > X; 123; 0)
REAL_TOTAL = $BASE + $BONUS + $EXTRA-BONUS

[darkDOS]

21 minutes ago, Eigenvektor said:

Surely there's some kind of "base total" that is used to calculate the bonuses, which are then used to calculate the overal total. You need to distinguish between those two values.

 

If your total increases your bonuses, which in turn increase your total, you have an endless loop which increases continuously. That would mean it would always go towards infinity.

The bonus is not calculated, the bonus on the item is a fixed number. The bonuses are applied *if* the item is equipped. 

 

So, as an example you had two items:

Item 1 requires 10 total stat and if equipped applies a 10 bonus.

Item 2 requires 20 total stat.

 

When the total reaches 10, you will be able to equip Item 1 and then in turn Item 2 due to the bonus applied from Item 1.

Worth noting, this is performed in one iteration meaning the bonus is only applied once if applied at all.

Perhaps that is maybe the issue with trying to do the calculation in Excel?

 

/edit

 

I have to experiment with your example. It looks promising, will report back.

 

[Eigenvektor]

13 minutes ago, darkDOS said:

So, as an example you had two items:

Item 1 requires 10 total stat and if equipped applies a 10 bonus.

Item 2 requires 20 total stat.

This should do it:

     

 

The final total includes the total and the additional bonuses from the items. The items are only applied when the total (excluding those items) exceeds a threshold.

[meidi]

On 9/29/2021 at 6:57 PM, darkDOS said:

Not strictly a language specific question, but a logic problem I think best fits to Programming.

I am trying to make an Excel sheet that helps plan a character in a game.

 

The Total Stat is the sum of the Base Stat and Bonuses applied.

The game has items that can only be equipped if the Total Stat meets or exceeds a value.

Once the item is equipped, it can add to the Bonuses that in turn increases the Total Stat.

 

 

I cannot for the life of me figure out the logic that avoids a circular reference since the total is dependent on the bonuses which is turn dependent on the total and so on... 

Hi,

have you tried enabling the iterative calculation in the option section?