I have a list of digits in Columns A, B & C and Column D has a formula like
=SUM(A1:C1) or AVERAGE(A1:C1) copied down.

Now is there any procedure to checkout if any formula is not consistent?

I want to develop a Custom Function like:

=Consistent(D20,D1:D100)

which would return True if:

D20=Sum(A1:C1)
AND
Each cell in the range D1:D1000 consists similar formulas, e.g.
D100=Sum(A100:C100); &
D1000=Sum(A1000:C1000); etc

and False if not same, e.g.

D20=A1+B1+C1 instead of Sum(A1:C1).

Any help in formulating a code for such a function shall be highly obliged.

Thanx in advance.

--

Best Regards,
FARAZ A. QURESHI

Before you do that, you may want to try looking at the formulas in R1C1
reference style:
Tools|options|General tab

Then look at the formulas
Tools|Options|view tab|check formulas

You may be able to just scroll up and down to find the one that doesn't belong.

If you wanted a UDF, you could look at the .formular1c1 property and compare it
with the one above (except for the top).


FARAZ QURESHI wrote:

I have a list of digits in Columns A, B & C and Column D has a formula like
=SUM(A1:C1) or AVERAGE(A1:C1) copied down.

Now is there any procedure to checkout if any formula is not consistent?

I want to develop a Custom Function like:

=Consistent(D20,D1:D100)

which would return True if:

D20=Sum(A1:C1)
AND
Each cell in the range D1:D1000 consists similar formulas, e.g.
D100=Sum(A100:C100); &
D1000=Sum(A1000:C1000); etc

and False if not same, e.g.

D20=A1+B1+C1 instead of Sum(A1:C1).

Any help in formulating a code for such a function shall be highly obliged.

Thanx in advance.

--

Best Regards,
FARAZ A. QURESHI

--

Dave Peterson

There is an "Inconsistent formula in region" feature built-in to Excel.
Check out the "Error Checking" tab in Tools | Options.
--
Jim Cone
Portland, Oregon USA



"FARAZ QURESHI"

wrote in message
I have a list of digits in Columns A, B & C and Column D has a formula like
=SUM(A1:C1) or AVERAGE(A1:C1) copied down.

Now is there any procedure to checkout if any formula is not consistent?

I want to develop a Custom Function like:

=Consistent(D20,D1:D100)

which would return True if:

D20=Sum(A1:C1)
AND
Each cell in the range D1:D1000 consists similar formulas, e.g.
D100=Sum(A100:C100); &
D1000=Sum(A1000:C1000); etc

and False if not same, e.g.

D20=A1+B1+C1 instead of Sum(A1:C1).

Any help in formulating a code for such a function shall be highly obliged.

Thanx in advance.

--

Best Regards,
FARAZ A. QURESHI

Thanx Dave,
Just checked out your reply.
Sure found it to be excellent!
Thanx again!
--

Best Regards,
FARAZ A. QURESHI


"Dave Peterson" wrote:

Before you do that, you may want to try looking at the formulas in R1C1
reference style:
Tools|options|General tab

Then look at the formulas
Tools|Options|view tab|check formulas

You may be able to just scroll up and down to find the one that doesn't belong.

If you wanted a UDF, you could look at the .formular1c1 property and compare it
with the one above (except for the top).


FARAZ QURESHI wrote:

I have a list of digits in Columns A, B & C and Column D has a formula like
=SUM(A1:C1) or AVERAGE(A1:C1) copied down.

Now is there any procedure to checkout if any formula is not consistent?

I want to develop a Custom Function like:

=Consistent(D20,D1:D100)

which would return True if:

D20=Sum(A1:C1)
AND
Each cell in the range D1:D1000 consists similar formulas, e.g.
D100=Sum(A100:C100); &
D1000=Sum(A1000:C1000); etc

and False if not same, e.g.

D20=A1+B1+C1 instead of Sum(A1:C1).

Any help in formulating a code for such a function shall be highly obliged.

Thanx in advance.

--

Best Regards,
FARAZ A. QURESHI

--

Dave Peterson