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.