May 2021 1 153 Report
MS Excel Array formula problem?

I wrote a formula to test the input in 5 cells within a row. That works great, but when I change the formula to an array formula to test the input of 20 rows at the same time, the array formula doesn't work.

Here are the details:

I have a range with 5 columns and 20 rows. The columns are A thru E...

Within each row of the range, the user can enter >Update:

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

= = = = = = WHERE I ENDED UP = = = = = = = = = = = = = = = = = = = = = = = =

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Thanks for the help. What I was trying to do ended up being bigger than a breadbox. . .

My problem – look thru an R x 5 matrix and see if any row is not "OK."

These are OK (correction made, the above was wrong):

0 0 0 0 0

1 1 1 1 1

0 1 1 1 1

1 1 1 0 1

Any other combo is not OK.

In the end, I had to create a user defined function instead of doing some slick arithmetic:

For all rows in a R x 5 matrix. . .

If sum of all 5 entries = 0 or 5, then OK (check next row)

if first entry is 0, then if sum of last 4 entries is 4, then OK (check next row)

if first entry is 1, then if the sum of the second, third and last entries is 3, then OK (check next row)

else Return "Not OK!" (end)

Please enter comments
Please enter your name.
Please enter the correct email address.
You must agree before submitting.

Answers & Comments


Helpful Social

Copyright © 2024 Q2A.ES - All rights reserved.