Skipping rows but not skipping reference cells

[Areallusernamestaken]

Hi,


I want to have the formula below in every other row, but I don’t want it to skip reference numbers.


So I want:

In AK2, I have: =IFS(COUNT(AND(AC2,AD2)),AF2,COUNT(AD1),AF1)

In AK4: =IFS(COUNT(AND(AC3,AD3)),AF3,COUNT(AD2),AF2)

In AK6:= IFS(COUNT(AND(AC4,AD4)),AF4,COUNT(AD3),AF3)

In AK8: =IFS(COUNT(AND(AC5,AD5)),AF5,COUNT(AD4),AF4)


Is there a way to do this?


When I drag, it’s skipping reference cells. So in AK8, for example, it’s changing the references to AC8 and AD8.


Thank you!

 

[jasonb75]

The only things that comes to mind are either a data error in column Y, or a time in AA / AB that is in text format. (the formula relies on numbers in those columns to work correctly).

Could you try 2 test formulas and tell me the results that you get from them please.

=COUNT(AA1:AA10000)

and

=COUNT(AB1:AB10000)

 

[Areallusernamestaken]

The only things that comes to mind are either a data error in column Y, or a time in AA / AB that is in text format. (the formula relies on numbers in those columns to work correctly).

Could you try 2 test formulas and tell me the results that you get from them please.

=COUNT(AA1:AA10000)

and

=COUNT(AB1:AB10000)

Both gave me 266.

 

[Areallusernamestaken]

Both gave me 266.

I formatted columns AA and AB as numbers and I'm still encountering the same issue.

 

[Areallusernamestaken]

Interestingly, column AE is working fine through line 327.

 

[jasonb75]

Going over the earlier information, there was something that I missed, which is likely the cause of the incorrect results if you haven't noticed the difference.

The example that I posted with the formulas was compressed slightly, I had missed that columns Z and AA were omitted from your sample data until I was trying to understand why the 2 test formulas returned identical results. With the data in the columns that you specified in posts 6 and 7, the actual formulas would be as in the table at the end of this post.

If you haven't already made the column adjustments as mentioned above, then doing so could possibly fix the problem and make the rest of this reply redundant, for now I'm just trying to think ahead.

You say that some of the formulas are showing 0, this could only happen if there were 0 entries in the source data. The formulas are based on the assumption that there will be one row with hours in either one, or both columns (Reg and OT hours columns) with the other rows for the same name showing #N/A. If there are inconsistencies in the source data that are not shown in the example that you provided then they will likely affect the results. If this is the case, then examples of other possibilities would be needed in order to allow for them in the formula.

If a different method is going to be needed to allow for variances in the data, does the order of the result matter? Could they be listed in ID order (assuming all ID's are numeric) instead of the order of the original data?

Book1 (version 1).xlsb
	AF	AG	AH
1	ID	Code	Hours
2	1234	REG	34
3	5678	REG	40
4	5678	OT	2
5	1357	REG	40
6	1357	OT	15
7	2468	REG	22.25
Sheet12

Cell Formulas
Range	Formula
AF2:AF7	AF2	=IFERROR(INDEX(Y:Y,AGGREGATE(15,6,ROW($Y$2:$Y$17)/ISNUMBER($AC$2:$AD$17),ROWS(AF$2:AF2))),"")
AG2:AG7	AG2	=IF(AF2="","",IF(AF2=AF1,"OT","REG"))
AH2:AH7	AH2	=IF(AG2="","",SUMIFS(IF(AG2="REG",$AC$2:$AC$17,$AD$2:$AD$17),$Y$2:$Y$17,AF2,$AB$2:$AB$17,">0"))

 

[Areallusernamestaken]

Going over the earlier information, there was something that I missed, which is likely the cause of the incorrect results if you haven't noticed the difference.

The example that I posted with the formulas was compressed slightly, I had missed that columns Z and AA were omitted from your sample data until I was trying to understand why the 2 test formulas returned identical results. With the data in the columns that you specified in posts 6 and 7, the actual formulas would be as in the table at the end of this post.

If you haven't already made the column adjustments as mentioned above, then doing so could possibly fix the problem and make the rest of this reply redundant, for now I'm just trying to think ahead.

You say that some of the formulas are showing 0, this could only happen if there were 0 entries in the source data. The formulas are based on the assumption that there will be one row with hours in either one, or both columns (Reg and OT hours columns) with the other rows for the same name showing #N/A. If there are inconsistencies in the source data that are not shown in the example that you provided then they will likely affect the results. If this is the case, then examples of other possibilities would be needed in order to allow for them in the formula.

If a different method is going to be needed to allow for variances in the data, does the order of the result matter? Could they be listed in ID order (assuming all ID's are numeric) instead of the order of the original data?

Book1 (version 1).xlsb
	AF	AG	AH
1	ID	Code	Hours
2	1234	REG	34
3	5678	REG	40
4	5678	OT	2
5	1357	REG	40
6	1357	OT	15
7	2468	REG	22.25
Sheet12

Cell Formulas
Range	Formula
AF2:AF7	AF2	=IFERROR(INDEX(Y:Y,AGGREGATE(15,6,ROW($Y$2:$Y$17)/ISNUMBER($AC$2:$AD$17),ROWS(AF$2:AF2))),"")
AG2:AG7	AG2	=IF(AF2="","",IF(AF2=AF1,"OT","REG"))
AH2:AH7	AH2	=IF(AG2="","",SUMIFS(IF(AG2="REG",$AC$2:$AC$17,$AD$2:$AD$17),$Y$2:$Y$17,AF2,$AB$2:$AB$17,">0"))

Thanks for your response.
I did notice the difference in the columns and already adjusted.
There's definitely data (formatted as numbers) in the hours columns. I'm not sure what the issue is or why it's looking like this (zeroes and then blank)

 

[Areallusernamestaken]

Thanks for your response.
I had noticed the difference in the columns and already adjusted.
There's definitely data (formatted as numbers) in the hours columns. I'm not sure what the issue is or why it's looking like this (zeroes and then blank)
View attachment 37110

 

[Areallusernamestaken]

Also, column AG (REG vs OT) starts giving all OTs at 317.

 

[Areallusernamestaken]

I tried applying the template to another week's payroll and it worked great through line 171 but not after that. I'm not sure why they stop working in the middle.

 

[Areallusernamestaken]

I tried applying the template to another week's payroll and it worked great through line 171 but not after that. I'm not sure why they stop working in the middle.

I'm also getting some strange numbers in my hours column. For example, 200, 177.5... Something is going wonky.