- BY Kevin Lehrbass
- POSTED IN Formulas
- WITH 0 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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:

- how did I use formulas to move the face?
- can I incorporate some dynamic arrays?
- 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:

- Volatile Functions: Talk Dirty to Me (www.sumproduct.com)
- Conditional Formatting is volatile (www.vertex42.com)
- Volatile functions – what’s the big deal? (www.vertex42.com)
- Volatile Excel functions (www.decisionmodels.com)

**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.