Search code examples
sortingrandomgoogle-sheetsgoogle-sheets-formulaarray-formulas

Google Sheets: Why does Iterative Calculation create duplicates, make formulas act weird?

I'm trying to make a NPC character sheet genetator for my Dungeons and Dragons setting in Google Sheets. Right now, I want to have Google Sheets distribute random numbers, but the highest of the numbers should go to the more important abilities for the selected class. And then I want it to distribute more points in a probably quite inefficient way, but that's not my main problem right now xD

It all works fine until I turn on Iterative Calculation, so some values can change based on each other in the table that shows which are the more important abilities for the selected class. Now there's random duplicates of values and sometimes Google Sheets fails to simply copy a range correctly. I'm not sure how much sense I'm making here, and I'm very much a beginner at this. Please, someone smart explain me what the problem is and how I can work my way around it! Thank you!

Link to the example: https://docs.google.com/spreadsheets/d/12zrEADm4p7teHsl4WPzeuRe5yo0wCS54gt_9_b_Xh60/edit?usp=sharing

I highlighted the ranges that started acting weird in light red. The cells with formulas that depend on each other I highlighted with blue.

Solution

  • turn off the iterative calculation and use:

    =ARRAYFORMULA(CHAR(64+SORT({1; 2}, 1, RANDBETWEEN(0, 1))))

    enter image description here

    update:

    =INDEX({{"B";"A"},{"A";"A"},{"A";"B"}},, RANDBETWEEN(1, 3))