Excel ignores formated rows

C

crafty

New Member
Joined
Jul 20, 2012
Messages
11
This sheet drives me nuts.
I am trying to set up a sheet, where I can pull out several information about the order performance.

I made Excel give me the number of total days the orders were proceeded, less than 5, than 10 and then all else greater than 10. There but the total didn't match the overall total of orders and there was a slight difference of 89 pcs.
I now set a filter and found out, that he wouldn't put exactly 89 pcs. in order when I sort the orders by the time they were proceeded. To me it seems, that Excel ignores them. Does anybody have any idea, what the problem could be?

Would really appreciate if anybody would take a look into this big thing. I really can't figure it out!

This is part of the table I'm trying to set up. You'll find the deviating numbers on the top left and right. The formulars are below.
[TABLE="class: grid, width: 1260"]
<tbody>[TR]
[TD][/TD]
[TD]G
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD="colspan: 4"]V | W | X | Y |
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]by quantity
[/TD]
[TD]by %
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Lead time whithin
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]total of orders
[/TD]
[TD]10896
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<5 days
[/TD]
[TD]<10 days
[/TD]
[TD]>10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]overrun delivery date
[/TD]
[TD]2970
[/TD]
[TD]27.26%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7681
[/TD]
[TD]2277
[/TD]
[TD]849
[/TD]
[TD]10807
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]on credit release
[/TD]
[TD]2497
[/TD]
[TD]22.92%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Orders overrun delivery day by
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<5 days
[/TD]
[TD]<10 days
[/TD]
[TD]>10 days
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2352
[/TD]
[TD]385
[/TD]
[TD]233
[/TD]
[TD]2970
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Orders on credit block
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<5 days
[/TD]
[TD]<10 days
[/TD]
[TD]>10 days
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1446
[/TD]
[TD]74
[/TD]
[TD]39
[/TD]
[TD]1559
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Order_create
[/TD]
[TD]Credit_block
[/TD]
[TD]Credit_release
[/TD]
[TD]Req_delivery
[/TD]
[TD]Goods_issue
[/TD]
[TD]Total days proceeded
[/TD]
[TD]On credit block
[/TD]
[TD]Days overrun
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]4/6/11 12:56 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4/7/2011
[/TD]
[TD]4/6/11 7:23 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]5/3/11 3:29 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5/3/2011
[/TD]
[TD]5/3/11 4:04 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]6/22/11 12:19 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/22/2011
[/TD]
[TD]6/22/11 2:14 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]8/12/11 2:34 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]8/12/2011
[/TD]
[TD]8/12/11 3:32 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]2/23/11 2:34 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/24/2011
[/TD]
[TD]2/23/11 4:25 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]5/20/11 2:26 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5/20/2011
[/TD]
[TD]5/20/11 7:04 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]9/6/11 3:27 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9/12/2011
[/TD]
[TD]9/6/11 6:32 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]10/14/11 3:31 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/20/2011
[/TD]
[TD]10/14/11 4:43 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]10/4/11 12:17 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/5/2011
[/TD]
[TD]10/4/11 7:16 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]12/21/11 3:25 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/21/2011
[/TD]
[TD]12/21/11 6:04 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]8/26/11 5:47 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9/2/2011
[/TD]
[TD]8/26/11 6:34 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]9/16/11 2:34 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9/26/2011
[/TD]
[TD]9/16/11 5:39 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]2/22/11 10:37 AM
[/TD]
[TD]2/22/11 10:37 AM
[/TD]
[TD]2/22/11 10:55 AM
[/TD]
[TD]2/28/2011
[/TD]
[TD][/TD]
[TD]371
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]2/3/11 12:11 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/3/2011
[/TD]
[TD]2/3/11 1:19 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]5/12/11 12:42 PM
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5/13/2011
[/TD]
[TD]5/12/11 5:42 PM
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formulas used as follows:
I2: =COUNTA($R14:$R30000)
I3: =COUNT($T14:$T30011)
I4: =COUNT($J14:$J30011)

V3: =COUNTIF($R14:$R30000,"<=5")
W3: =(COUNTIF($R14:$R30000,"<=10"))-V3
X3: =COUNTIF($R14:$R30007,">10")
Y3: =SUM(V3:X3)

V7: =COUNTIF($T14:$T30011,"<=5")
W7: =(COUNTIF($T14:$T30011,"<=10"))-$V$7
X7: =(COUNTIF($T14:$T30011,">10"))
Y7: =SUM(V7:X7)

Same for the credit one.


Then for the matrix itself as follows:

R14: =IF((IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14))))))<=0,"1",(IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14)))))))
S14: =IF((IF(I14<>"",(IF(HOUR(MAX(I14,J14))<12,NETWORKDAYS(MAX(I14),J14),NETWORKDAYS(MAX(I14)+1,J14))),""))<=0,"1",(IF(I14<>"",(IF(HOUR(MAX(I14,J14))<12,NETWORKDAYS(MAX(I14),J14),NETWORKDAYS(MAX(I14)+1,J14))),"")))
T14: =IF(($L14+1)>$M14,"",(NETWORKDAYS($L14,$M14)))


Thanks so much for any advice!
 
Sort by date Sort by votes
I got lost trying connecting your table with your description.
However I wonder if the misaligment in the range used (sometimes rows 14:30000, sometimes 14:30007, sometimes 14:30011) could justify your wrong results.

Bye
 
Upvote 0
I think maybe your formula for I two using CountA is counting cells with formulas in them when their value is actually blank. But that is just a guess.
 
Upvote 0
@Anthony47: I bet it's confusing.
The tabel J1:G4 is really just to get a quick overview about the total amount of orders. V1:Y11 also, but they are supposed to show the total orders more detailed. Those tabels refer to the orders, which are listed below from row 14 to 10909, which equals a total of 10896 rows in use and which is definitely correct. The orders dont't match the analysis tabels above though.
The varying numbers you mentioned shouldn't be the problem, since I'm really only have roughly 11,000 rows in use and the formulars ignore empty cells.

@JLGWhiz: I changed it to a regular >count< formula and now it shows the same result as on the leadtime calculation. This shouldn't be right tough! I am 100% sure, that there are 10896 orders. Excel is definitely, however, ignoring 89 rows/orders and not counting them.

Any other ideas? :eeek:
 
Upvote 0
I don't like that formula in R14 might return "1" (string with character 1, not the value 1); try replacing that "1" with plain 1 (no "quotes").
I mean, NOT
R14: =IF((IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14))))))<=0,"1",(IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14)))))))

BUT
R14: =IF((IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14))))))<=0,1,(IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14)))))))

Indeed this should have caused a large misalignment with the results, but it has to be cleaned.

Bye
 
Upvote 0
All the data's are now as how they are supposed to be and the number are all correct now.

Thank's so much for taking your time!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Separate Day and Time to Another Column if condition meet
2
Replies
10
Views
210
SamKhem
S
R
  • Solved
Holiday without adding Time in and Time Out
Replies
3
Views
69
Peter_SSs
Peter_SSs
V
  • Question Question
time and date fix
Replies
7
Views
216
vmjan02
V
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
812
Peter_SSs
Peter_SSs
I
  • Solved
MAXIFs not working.
Replies
5
Views
340
Joe4
Joe4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top