02-21-2019 01:43 PM
02-21-2019 01:43 PM
I am having an odd issue with Excel that I cannot seem to find the answer to by searching Google.
I have a function that has several if functions, index/match, and lookup functions. The formula works for about 90% of the cells in the column but for some reason the other 10% only work if I click on the formula bar and hit enter. It then becomes a whack a mole game as previous ones will revert back to the incorrect number. Is there a simple way to get Excel to kick itself in gear and do all of the calculations correctly? The workbook has 15 tabs and each tab averages about 5500 rows so I do not have time to keep going into each incorrect cell to fix them.
I also attempted to split the formula into helper columns and then a simplified if function, but the same thing is happening.
Here is the larger formula:
Here is the second attempt with additional columns:
Any help will be greatly appreciated! I have spent most of the past two days trying to figure this out.
Is it the lookup function not working with the if functions that is causing the problems?
02-21-2019 05:14 PM
For me to even begin to help, I'd need an excel file with that in it. I cannot begin to disect that in text format.
But before you do that, if you put your data in an Excel table and made sure the column(s) all had 100% consistent formulas, that would go a long way to resolving this I suspect.
02-22-2019 06:14 AM
Attached is an example of the sheet. Column P is where I am having the issues. As a you scroll down you will see that numbers repeat where it should be either +1 of the previous number or 1 for a new week.
If you click on one of the cells that doubled up the previous number it will update to the correct number.
02-26-2019 07:25 AM
Enter this formula in Column P, starting with P3:
I am not sure how this formula helps me. If there is a value in Q then P should show 0 because the item has been counted. For the blanks in Q P should have a number between 1 and x depending on how many A, B, or C items there are per week for the location. When it hits x the next number needs to be 1 to start a new week. The formula I have works. Excel just does not want to calculate the cells 100% of the time so it throws off my week counts, and that is what I want to find a solution for.
02-26-2019 06:04 PM