1

I have an Excel spreadsheet that calculates quasi-random attribute scores for a role-playing game using four different methods. One cell, Q20, contains the SUM of the attribute scores thereby generated. I want to have another cell, Q23, record the highest such total ever calculated.

I have tried =IF(Q20>Q23,Q20,Q23) in Q23, but Excel warns about the circular reference and the value never updates. Does anyone have a formula solution for this scenario?

Thanks,

Justin.

Improve this question

1 Answer 1

Reset to default
1

You can bypass "circular reference" warnings in these cases by disabling automatic worksheet calculations, i.e. use the manual recalculation, which means every time a value changes you need to press F9 to recalculate.

You don't mention your Excel version, but for Excel 2016

To put the workbook into manual recalculation mode, you select the Manual option on the Calculation Options’ button on the Formulas tab of the Ribbon (Alt+MXM). After switching to manual recalculation, Excel displays CALCULATE on the status bar whenever you make a change to the worksheet that somehow affects the current values of its formulas. Whenever Excel is in Calculate mode, you need to bring the formulas up-to-date in your worksheets before saving the workbook (as you would do before you print its worksheets).

To recalculate the formulas in a workbook when calculation is manual, press F9 or Ctrl+= (equal sign) or select the Calculate Now button (the one with a picture of a calculator in the upper-right corner of the Calculation group) on the Formulas tab (Alt+MB).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .