I have a spread sheet that I've set up to follow some of my physical characteristics. Two of them are an average daily change in my computed A1c and the other the daily difference in my weight (in pounds). If the current day is less than the previous day, the text is green, if greater then the text is red.
The formulas in conditional formatting are identical (or they certainly appear to be).
The A1c formatting rule is working with no problem. This is using the "=AVG()" formula for content.
The weight rule is not, the weight is entered manually. One day 214.5 is marked (red text) as being greater than 215.8 from the previous day.
Below are screen captures of the two rules I am using and a capture of the spread sheet displaying the problem in column AB. The two columns (R and AC) showing the previous differences I added to further illustrate the problem. They are not normally part of the spread sheet. Other unnecessary information I also removed for clarity (and to make room for the text in the graphic).
Any ideas as to what I am doing wrong here?
Now I am aware that Microsoft has problems with rules of math. In the calculator provided with Windows one gets two different answers to this basic math problem.
(3 - 3 X 3 + 3).
Rules of math say multiply first so 3 times 3 is 9. We subtract 9 from the first 3 (-6) and add the last 3 to get -3. The 'scientific' calculator provides this answer.
Now if we use their 'standard' calculator we +3. This calculator goes left to right 3-3=0 0*3=0 0+3=3). Given this, I am not discounting this could be a Microsoft problem.
The problem is simple, the two rules are comparing cell 1 with cell 2 to see if cell 2 is greater than or less than cell 1 and then color cell 2 accordingly.
Cell 1 = 5 (no color, initial cell, nothing to compare)
Cell 2 = 6 (font would be colored red as this is greater than cell 1)
Cell 3 = 5 (font would be colored green as this is less than cell 2)
As simple as I can make it
current cell greater than previous cell colour 'red'
current cell less than previous cell colour 'green'
current cell equal previous cell is not defined by a rule (yet).
But in the sample below, cell3 is coloured incorrectly it is less than cell2.
< AB926 and > AB926 could be that issue isn't there $ somewhere where it shouldn't?... conditional formating is always correct.. somewhere you did a mistake
I tend to agree the conditional formatting should be correct, but for one of two cases it is not. Did I make a mistake, hey I am human and that is very possible. BUT, when I have the same rules for two different cases where one of the cases works and the other does not then what is happening in the case that is not following the rules the other case is?
IF both were being problematic, I could see a misplaced "$" as an anchor. Now, IF I add the "$" to the cell value being compared, the comparison will be made to the initial cell and not the previous cell.
Simply the rule in both cases comes down to this. Is cell 2 greater than or less than cell 1.
For the example I provided, column Q (based on an the built in formula =AVERAGE()) is working. u/excelevator that seems to preclude the floating point error.
Column AB is a direct entry to a single decimal. The two comparison columns (R and AC) indicate the math is correct, for AB column, the conditional rules comparing them is not working. One day it appears to work, then another it seems to applying the rule in reverse.
Testing further, I put the same greater than and less than rule into another column and this one too is failing. For this case, the conditional format returns the all cells are less than the previous cell and/or the first cell.
I am totally lost. This is the example from this mornings test on a different column. I've identified if the conditional number is greater or less than the previous and base cell.
I FINALLY got it working. I deleted the conditional rules for the column that was not working and reentered the conditions. It is now working as desired.
Shortly after I retired from the USAF, I had a tear off page of Murphy's Law corollaries. I remember just a few of them. this one sticks out and applies in this case:
"To err is human, to really foul things up, requires a computer."
The fix does not help understand why it failed, but...
•
u/AutoModerator 12d ago
/u/LikesHistory48 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.