XL 07: Array reads 0s after replacing table contents

[whitesabbathica9]

I have a matrix that uses array/ formulas (using SUM) that reference a table on a separate sheet. When I replace the contents of the table, using Replace Values, all of the arrays suddenly only read 0.

This is a daily report sheet that I have used every day, and now it suddenly starting doing this. What's interesting is I have some array formulas that do work, but most don't.

=SUM(($A15=LAD)*($B$7=OPLAN)*(G$7=SERVICE)*PAX)

Each named range is a table column. It works until I replace the table that the ranges reference. Even when I replace the named ranges with the alpha-numeric designations it fails to work with the new values.

Any ideas?

 

[Jon von der Heyden]

Hi

If I understand you correctly you are using Find&Replace to change existing values within the ranges that this formula is dependent on?

Can you tell us what the replacements are that you are making (just a handful of examples will do)? And can you tell us what the range names refer to?

BTW, you could also use SUMPRODUCT which does not need to be CSE committed.

=SUMPRODUCT(--(LAD=$A15),--(OPLAN=$B$7),--(SERVICE=G$7),PAX)

Edit: And since you are using xl07 you could also use SUMIFS

=SUMIFS(PAX,LAD,$A15,OPLAN,$B$7,SERVICE,G$7)

 

[whitesabbathica9]

Thanks for the reply, Hayden.

I am actually replacing the entire contents of the table with new information and using Paste Special>Values.

The ranges are:
OPLAN: alpha-numeric TEXT defining one of three categories in table
LAD: DATE column in table
SERVICE: single character defining one of 6 categories in table
PAX: NUMBER of personnel

Table with data:
OPLAN LAD SVC PAX
111AD 6-3-2011 A 5
~ total of 5000 rows of various data in random order
Matrix with arrays:
111AD
A D J
6-2-2011 0 0 0
6-3-2011 5 0 0
~ counts all PAX with same LAD/SVC/OPLAN for the day for a year

Your alternate functions work on the old data, but same effect on the new table; all zeroes.

 

[Jon von der Heyden]

Are you sure that there are combinations that exist and that therefor the result should not be zero?

 

[whitesabbathica9]

Definitely. They are records of daily incoming personnel on an airfield. Always at least 100 per day. What's strange is that I have array formulas to the left of the matrix that use the same tables and ranges but seem to calculate them. It's only the ones that define them per SVC that is causing the problem. I made all the tables and formulas cells to General formatting.

 

[Jon von der Heyden]

It might be that the numbers are seen as text despite the number formatting. So, highligh each column individually and go through the following steps:

Invoke Text to Columns (Data Tab)
Choose Delimited > Next
Uncheck All Delimiters
Hit Finish

 

[whitesabbathica9]

Brilliant!

I didn't go with your recommendation, but it reminded me to reformat the data before replacing. I export the data from another system and forgot to remove the spaces and blank values before replacing. That did the trick. Thanks so much for your help.

One question, though. On your first alternate function...
=SUMPRODUCT(--(LAD=$A15),--(OPLAN=$B$7),--(SERVICE=G$7),PAX)

what is the purpose of the "--"? It works great, but I've never seen that before.

 

[Jon von der Heyden]

Those individual arguments are evaluating an aray and yield an array of TRUE's and FALSE's depending on whether or not they meet the condition asked. The double unary coerces those TRUE's and FALSE's into 1's and 0's so that they can be used as digital values in the SUMPRODUCT calculation.

--TRUE = 1
--FALSE = 0