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.

 

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:

  • 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!).

=IFERROR(SUM(INDIRECT(ADDRESS(AI126,AJ126)) Face),””)

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!

 

 

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 *