Eliminating Color filled cells from the formulas

[UDFNewbie]

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

 

[steve the fish]

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.

 

[steve the fish]

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]

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?

 

[steve the fish]

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]

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:

 

[mikerickson]

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

Application.Volatile

into the function.

 

[steve the fish]

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]

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]

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

Application.Volatile

into the function.

Thanks Mike. Is there somewhere in particular in the code that I should place that?

 

[mikerickson]

I ususally put it in the function right after the declaration lines.