Help with formula Please

[bloubul]

Will some one please help me.

I'm using this formula "=IF(COUNT(C5)=0,"",INDEX($Q4:$BI4,0,C5))", as long as there are only numbers (1_49) in C5:G5 the formula works great, but if I put a "0" in C5:G5, than it gives me "####" in J5:N5, but the "INDEX($Q4:$BI4,0,C5))" part works fine it see the "0" and count it, it's the front part of the formula that gives me problems.

Thanks

BlouBul

 

[bloubul]

Sorry for the delay to upload the file but we are experiencing major power failures.

https://www.mediafire.com/?33l4jfi0249ywoh

BlouBul

 

[Frank]

Hi Bloubul,
The problem arises because you are using the INDEX function with a parameter of 0. INDEX tries to find a number in an array on row number, column number where you set those 2 parameters. You cannot have a column number of 0. The other thing wrong is that the row (array) you are looking in starts at column R ( corresponding to number 1) when it should start at column Q (to include the data about number 0). You need to amend the formulas(s) to take account of this extra column of information starting with a zero (data) column.

Change the formula in cell J5 to read :- =IF(COUNT(C5)=0,"",INDEX($Q4:$BJ4,0,C5+1))

copy this along to cell N5 , then copy the whole block of 5 cells J5:N5 down the spreadsheet to replace the old formulas.

Try that.

 

[bloubul]

Once again I thank you. I'm trying to learn Excel and formula's, but at 65 yrs it's not that easy any more. Thank you.

BlouBul

 

[Frank]

You are welcome, Bloubul. I'm 70 this year, so its a struggle trying not to forget what I already know!