Eliminating Color filled cells from the formulas

UDFNewbie

New Member
Joined
Mar 1, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I am new to this forum and UDF's (which is what I think I need for the following) so I appreciate the advice:

In the below picture, I am doing several things:

1) Tracking my consumption of different materials (rows 3&4) vs. plan (rows 1&2)
2) Calculating my purchased consumption (cell showing 25% to the right)
3) Forecasting Full year spend in the (GREEN). I am entering values and forecasting my total full year spend. ISSUE: When I forecast the rest of year in green, it updated the cell with the 25%

QUESTION:
1) Is there a way to have the cell with the formula for 25% calculated only those cells that are not colored green? (NOTE: The 25% is driven really just by the row titled "fixed volume").
2) This same 5 rows below are repeated many times for other materials. Will the solution for #1 need to be done on each "25%" formula on each tab of the workbook?

Thanks in advance for the communities help!

UDFNewbie

1614636765054.png
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
You should be aware that changing a cell's color does not trigger calculation.
If you have this formula set up to ignore green and the user makes a cell green, the formula will not re-calculate and the value seen will not reflect the change in color. (Making the UDF volatile will not fix this.)
Using color as data in Excel is fraught with problems and is contra-indicated.
Hi Mike. I said that in post 6 as i knew this to be the case. However weirdly when testing and using the format painter to paint the cell is was causing recalculation which i surprised by. Im not sure what the painter does to cause a recalc but it was happening. Using 365.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
What Mike is saying is it will not recalc by using formatting alone. Say you have a value in a cell then paint it green. In theory that would not cause the formula to recalculate. If you lose the cell formatting then type a number in the cell the formula will recalculate. Best way to try is to test and confirm it to yourself.
 

UDFNewbie

New Member
Joined
Mar 1, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Steve, Thanks for your patience with me! I Did the following:

1) updated my formula for the 25% to add the _NG: =IF(O5=0,0,IF(AVERAGE_NG(O5:Z5)>AVERAGE(O3:Z3),IF(AA5>AA3,1,AA5/(AVERAGE_NG(O5:Z5)*12)),AA5/AA3))

2) added the VBA (as seen in pic below) by doing ALT+FN11 and then "insert module". When the window popped up I added the below code with one edit. i chnaged the R from 205 to 204. No other changes.

Result: When I add numbers into Row 5 (see post #3), the % keeps changing (getting higher, as it should). I tried clearing cells and colors and adding color then numbers back in, but same result. The % changes instead of ignoring because it is green. I think I took warning to your post 12 above but obviously I am doing something wrong. Any advice?


1614709502542.png
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
This looks like one of the warnings we suggested about formatting not being the best way to calculate things. However first things first check that the green you are using matches the RGB in the function.
 

UDFNewbie

New Member
Joined
Mar 1, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yea I was worried about that. unfortunately, I did check that before posting the last message. 204,255,204. Something new in that window (at least for me is the Hex# CCFFCC). Is there something there that we have to be careful about? Pic below:


1614715629057.png
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
To show when a cell is being calculated, you could put =RAND() in a spare cell and then change the color of a cell. if the value returns by RAND changes, calculation has been triggered.
The format painter is a Paste operation, which does trigger calculation. (Just as copying a cell and pasting it onto itself does.)

The reason that RAND recalculates but your UDF does not, is that RAND is a volatile function, it recalcualtes any time any cell in the workbook changes. Your UDF, like most functions, recalculates only if one of the precedent cells changes.
To make your UDF volatile put the line
VBA Code:
Application.Volatile
into the function.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Be sure they are all the same even if they look they same they may not be. Now if you understand that formatting doesnt cause recalculation then you should be able to get it to work. So if you want to remove the green colour then remove the formatting first then add the number. It should recalc. If it isnt show the worksheet with the ranges and the formula bar showing the formula in use.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
Seeing as you are seeing the pitfalls of using colours to perform calcs maybe you should use a helper row. Maybe use 1s for calc column and 0s for dont. They you can use an AVERAGEIFS native formula.
 

UDFNewbie

New Member
Joined
Mar 1, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
To show when a cell is being calculated, you could put =RAND() in a spare cell and then change the color of a cell. if the value returns by RAND changes, calculation has been triggered.
The format painter is a Paste operation, which does trigger calculation. (Just as copying a cell and pasting it onto itself does.)

The reason that RAND recalculates but your UDF does not, is that RAND is a volatile function, it recalcualtes any time any cell in the workbook changes. Your UDF, like most functions, recalculates only if one of the precedent cells changes.
To make your UDF volatile put the line
VBA Code:
Application.Volatile
into the function.
Thanks Mike. Is there somewhere in particular in the code that I should place that?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,379
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top