I have a range A1-A10000 and on every row of column C from C1-C10000 I have a formula using the entire range in A but I want to exclude the single cell that is on the same row as the formula itself.
For example:
In cell C10 the formula must ignore A10 but use all the other cells in the range in A.
Is there an easy way to do this?
EDIT:
I am attempting to use Vlookup or Index|Match to make the C collumn find which row in B collumn has a cable with same number/name as the cable on the same row as the forumala is and then display the text in collumn A on that row.
This is going to be done almost 100 000 times so manualy excluding one cell on each line is not on option.
Quick example
I have managed to get the result using if and iserror to get it to first search below it then if it doesnt find a cable with that name it searches above it. but the formula is very messy and probably very inefficient so would like to get a better solution if possible.
Best Answer
It depends on the formula.
Say column A contains numbers and the formula in the c-cells should calculate the average of column A, but should exclude the value in column A adjacent to the formula.
In C1 enter:
and copy down.
It really depends on the formula in question.