Combining If and Lookup Functions calculating some cells but not all

Copper Contributor

Hello,

 

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:

=IF(AND(Q3>"",R3="N"),0,IF(AK3<O3,1,IF(AND($G3=0,ISERROR(LOOKUP(2,1/$P$2:P2,$P$2:P2))),1,IF(AND($G3=0,LOOKUP(2,1/$P$2:P2,$P$2:P2)<INDEX(Table!$M$4:$S$18,MATCH($C$1,Table!$M$4:$M$18,0),MATCH($O3,Table!$M$2:$S$2,0))),(LOOKUP(2,1/$P$2:P2,$P$2:P2)+1),IF(AND($G3=0,LOOKUP(2,1/$P$2:P2,$P$2:P2)>=INDEX(Table!$M$4:$S$18,MATCH($C$1,Table!$M$4:$M$18,0),MATCH($O3,Table!$M$2:$S$2,0))),1,0)))))

 

Here is the second attempt with additional columns:

=IF(AC3="Y",0,IF(AJ3="Y",1,IF(AK3="Y",LOOKUP(2,1/$P$2:$P2,$P$2:$P2)+1,"N/A")))

 

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?

5 Replies

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.

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.

Enter this formula in Column P, starting with P3:
=COUNTIF(Q$3:Q3,Q3)

@Twifoo wrote:
Enter this formula in Column P, starting with P3:
=COUNTIF(Q$3:Q3,Q3)

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.

 

Your statements are contradicting so I can’t understand what you really want to achieve.