MrExcel Publishing
Your One Stop for Excel Tips & Solutions

What type of formula should be used in an array to.....


Posted by Sahaja on November 29, 2000 1:45 AM

calculate a figure dependant on different conditions. To explain:
So far I have used the following IF formula:=IF(C2:C600="B",E2:E600*G2:G600+(I2:I600+J2:J600+K2:K600),0)
but the problem is that the rows which are not already written in all show up with a 0 value rather than
changing when the B or S appears as I enter data.

Help will be most appreciated


Posted by Celia on November 29, 2000 4:27 PM

Sahaja
I don't understand what you are trying to do. Can you explain further? Are you trying to put a formula result in each row? - that is, for each cell in C2:C600 if the value is "B", do your calculation based on the other columns' data in the same row?
Celia

Posted by Sahaja on November 30, 2000 3:26 AM

Celia
I am trying to return a value in column M depending on the formula according to whether there is a B in column C. The problem is that once I have entered the formula as an array in column M it returns the value of 0 in the subsequent colums that I have not yet entered info.
Does this explain what i am trying to do as I am very new to this, thanks

Posted by Celia on November 30, 2000 4:33 AM

Sahaja
If the problem is that you want to show nothing instead of zero when there is no data in Column C, then change your formula to :-
=IF(C2:C600="B",E2:E600*G2:G600+(I2:I600+J2:J600+K2:K600),"")

Also, check whether the following formula entered into M2 and filled down as far as is required does what you need :-
=IF(C2="B",E2*G2+I2+J2+K2,"")

Celia