Excel limitations

[Muppet]

I have just started a programming subject for my engineering degree and my first assignment involves evaluating data. 

 

In excel,

I was givin a 20 by 20 grid of numbers,

I had to find the average for each row and column,

then if the number in each cell is higher then the average for the row and column make 1, if lower then the average -1, otherwise 0,

then for each row and column I had to count how many 1,0 and -1,

 

Now this is where im having the problem, The data gets "rejected" if any of the counts is greater then equal to 65% (13). When i do the evaluation for each row it works, =IF(OR(V26:V45>=13,W26:W45>=13,X26:X45>=13),"Rejected","Accepted")

 

   But when i try to do, =IF(OR(B46:U46>=13,B47:U47>=13,B48:U48>=13),"Rejected","Accepted")

 

I cant get it to work, and if i try to do columns i would have to state 20 different areas within the OR function. And since its being graded its got to be the most efficient way. 

 

Solution please?

 

I hope that made sense, been doing so much googling of excel functions ect! 

 

 

 

 

 

[crazy_104]

I'm quite confused with the 1, 0, -1 part.

 

Are all numbers in your array 1, 0 or -1?

Could you give a small example as to what the expected output is?

[WanderingFool]

Bit confused at what you are doing, given that =IF(OR(V26:V45>=13,W26:W45>=13,X26:X45>=13),"Rejected","Accepted")

only looks at V26 >= 13, W26 >= 13 and X26 >= 13....if V27 were to be 14 it would still say "Accepted"....unless you are referring to each row being rejected....at which point the above if statement is needlessly long.

e.g. if each row gets rejected =if(or(v26>13,w26>=13,x26>=13..... works just as well, with less confusion.

 

Anyways that was assuming you meant rows get rejected if any of them exceed 13, the rest of this post will discuss if the entire 20x20 grid gets "rejected" if one column/row is invalid

 

Since you have said you were googling for excel functions, might I recommend using the MAX function....from what I am assuming you could simplify it.

 

e.g.

=IF(MAX(V26:X45) >= 13, "Rejected", "Accepted")

It should be noted in this example it does not perform the same way as your =IF(OR(V26:V45>=13,W26:W45>=13,X26:X45>=13),"Rejected","Accepted")

 

 

Now I put the =IF(MAX(V26:X45) >= 13, "Rejected", "Accepted") in the cell Y26, now here is the trick that will help you in the future....I copied it, right click on B49 and paste special this opens a dialogue, now look for the check mark that says transpose, click ok....you should now get =IF(MAX(B46:U48) >= 13, "Rejected", "Accepted")

 

Transpose basically turns a vertical calculation into a horizontal calculation.

 

You now have two formulas, one that checks columns and one that checks rows...if you want you could reduce it by one by copying the text in one and or'ing the results from max

 

[Muppet]

Ill try MAX, i didn't think of that one, and i didn't know about transpose, thanks.