# Flirting with Volatility

I had some questions and improvement ideas for my formula pixel face. I learned a lot playing around with this.

### Recap

My curiosity got the best of me in 2019. Challenge:

Can I make a pixel face move without VBA? (post)

Can I display a message when the face intersects a cell with a number?

Previously, I used VBA to make the face move.

### Now what?

I couldn’t get several questions out of my head:

1. how did I use formulas to move the face?
2. can I incorporate some dynamic arrays?
3. can I make formulas less volatile & more efficient?

### 1. How does the Face Move?

(a)Each face item has a starting row/column position.

The blue left eye = row 9 and column 8.

(b)Spin buttons added. Cells below “Up/Down” & “Left/Right” above receive spin button values.

(c)Below, current positions = original positions adjusted by spin button values.

Currently values are identical as “Up/Down” & “Left/Right” both = 0. Click spin buttons to change them.

(d)Conditional Formatting rules added for each face item based on (c).

By the way, I just noticed “Duplicate Row” option. How long has that been there?

### 2. Incorporate Dynamic Arrays?

In 2019 I just put numbers in cells. Conditional formatting added the purple color.

Could dynamic arrays create purple obstacles that vary in size?

Goal: move face to green area without touching purple cells . Can I make purple areas change size?

I experimented with formulas like these to create some variability:

• RANDARRAY(RANDBETWEEN(1,4),RANDBETWEEN(1,4),1,1,TRUE)
• SEQUENCE(2,3,INT(RAND()*10),INT(RAND()*10))
• SEQUENCE(RANDBETWEEN(1,3),4,1,0))
• IFERROR(SEQUENCE(INT(RAND()*4),2,5,0),1)

RANDBETWEEN or RAND inside SEQUENCE produced occasional #SPILL! errors.

WHY? How could it sometimes work? Answer from Microsoft:

Occasional #SPILL! errors annoyed me so I created a non volatile way to create variability! See area with the purple background starting in cell AQ54. INDEX/MATCH inside sequence uses this area.

If the face intersects a purple cell a message will be displayed (see cell AW78).

### 3. Less volatile & more efficient?

Some insist on never using volatile functions. I agree 99% of the time (post).

Remove Volatile Functions:

My original formula to test overlap between face and number cells used INDIRECT (!volatile!).

My modified formula below (cell AK78) does not use any volatile functions.

=IFERROR(SUM(Face INDEX(\$A\$1:\$BK\$35,AI78,AJ78) ),””)

Only Calculate If Required!

=IF(AF78>\$AG\$76,””,IFERROR(SUM(Face INDEX(\$A\$1:\$BK\$35,AI78,AJ78) ),””))

IF tests if the formula is needed. It compares counter with AG76 value (count cells with numbers).

ROW & COLUMN functions

Various formulas used ROW and COLUMN functions many times. I replaced them with hard coded counters. Downside? I’d have to adjust them if I insert new rows or columns.

Example: cell AG78 formula no longer requires ROW & COLUMN functions:

=IF(AF78>\$AG\$76,””,SMALL(IF(ISNUMBER(\$D\$4:\$BK\$35),\$A\$4:\$A\$35+(\$D\$1:\$BJ\$1/100),””),AF78))

Name Range: Face

The named range to identify the face’s current location used volatile OFFSET function. I changed it to INDEX. Yes, INDEX can be used for a dynamic range (post) and it isn’t volatile.

=INDEX(grid!\$A\$1:\$BK\$35,grid!\$AC\$67,grid!\$AG\$67):INDEX(grid!\$A\$1:\$BK\$35,grid!\$AC\$67+grid!\$S\$67-1,grid!\$AG\$67+grid!\$V\$67-1)

Thanks to Robert Gascon for reminding me of this a couple of years ago.

Conditional Formatting

Conditional formatting rules are “super-volatile” as per Bill Jelen (post). I’ve made the formulas more efficient but CF rules remain volatile. I considered using custom formats as Bill suggested but that would require a full redesign.

Change the purple obstacles

To redesign the playing area add/remove the sequence formulas (purple obstacles).

### Volatility in Excel

Some good sources for learning more including:

### Losing My Mind

I think the universe played a trick on me.

Previously, I had formatted the input area with custom format “;;;” so numbers wouldn’t be visible. My thinking was probably that this was the easiest way to hide them. I had completely forgotten about this. Somehow two of my formulas had the same format! So the formula was working but I couldn’t see the result! 🙂

I won’t admit how long it took me to figure this out!