HOW TO CATCH A DIFFERENT/INCONSISTENT FORMULA

F

FARAZ QURESHI

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.
 
D

Dave Peterson

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).
 
J

Jim Cone

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"
<[email protected]>
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.
 
F

FARAZ QURESHI

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top