Flirting with Volatility

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



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.


Updated Pixel Face

Download my updated Excel file hereΒ and follow along below.


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:

  • SEQUENCE(2,3,INT(RAND()*10),INT(RAND()*10))
  • 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:



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.


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!



About Me

I’ve taken courses, read books, and watched videos but I still learn the most by building & playing with Excel. Theory can be helpful but hands on experience is essential. It’s also a fun thing to play with while watching NBA/NHL playoffs, Netflix and Prime.


Leave a Reply

Your email address will not be published. Required fields are marked *