The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

I don't think that this is cleaner--but maybe your boss will understand it???

=if(abs((G11-H11)/G11)<0.01, .....

(no need to do abs(0.01), though)

Dave F wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

--

Dave Peterson

Change VALID to "These values are within 1% of each other" and INVALID to
"Hey more than 1% difference here, we need to sharpen up Guys" and buy him
Excel for dummies.

Your formula is sound.

Mike

"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

Sorry I copied it incorrectly below. Should be
=IF(ABS(1-(H11/G11))<ABS(0.01)),"VALID","INVALID")

Dave
--
Brevity is the soul of wit.


"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

=IF(ABS(1-(H11/G11)<ABS(0.01)),"VALID","INVALID")

Maybe this:

=IF(ABS(1-(H11/G11))<0.01,"VALID","INVALID")

You might want to make sure you don't get a #DIV/0! error:

=IF(G11=0,"",IF(ABS(1-(H11/G11))<0.01,"VALID","INVALID"))

Biff

"Dave F" wrote in message
...
The formula above tests to make sure that H11 and G11 differ by less than
1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it
does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

Maybe he doesn't understand ABS?
=IF(OR(G11/H11<1.01,G11/H11<1.01),"VALID","INVALID")

"Dave F" wrote:

Sorry I copied it incorrectly below. Should be
=IF(ABS(1-(H11/G11))<ABS(0.01)),"VALID","INVALID")

Dave
--
Brevity is the soul of wit.


"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

Fair point about the abs(0.01)

Thanks.
--
Brevity is the soul of wit.


"Dave Peterson" wrote:

I don't think that this is cleaner--but maybe your boss will understand it???

=if(abs((G11-H11)/G11)<0.01, .....

(no need to do abs(0.01), though)

Dave F wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

--

Dave Peterson

Wow.. never mind what I wrote... What was I thinking?
=if(OR(AND(G11/H11<1.01,H11/G11.99),AND(H11/G11<1.01,G11/H11.99),"VALID","INVALID")

"Sean Timmons" wrote:

Maybe he doesn't understand ABS?
=IF(OR(G11/H11<1.01,G11/H11<1.01),"VALID","INVALID")

"Dave F" wrote:

Sorry I copied it incorrectly below. Should be
=IF(ABS(1-(H11/G11))<ABS(0.01)),"VALID","INVALID")

Dave
--
Brevity is the soul of wit.


"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

I think the real answer here is "there's no way to make it more clearer than
it is"...

Dave
--
Brevity is the soul of wit.


"T. Valko" wrote:

=IF(ABS(1-(H11/G11)<ABS(0.01)),"VALID","INVALID")

Maybe this:

=IF(ABS(1-(H11/G11))<0.01,"VALID","INVALID")

You might want to make sure you don't get a #DIV/0! error:

=IF(G11=0,"",IF(ABS(1-(H11/G11))<0.01,"VALID","INVALID"))

Biff

"Dave F" wrote in message
...
The formula above tests to make sure that H11 and G11 differ by less than
1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it
does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

Pretty much!

Biff

"Dave F" wrote in message
...
I think the real answer here is "there's no way to make it more clearer
than
it is"...

Dave
--
Brevity is the soul of wit.


"T. Valko" wrote:

=IF(ABS(1-(H11/G11)<ABS(0.01)),"VALID","INVALID")

Maybe this:

=IF(ABS(1-(H11/G11))<0.01,"VALID","INVALID")

You might want to make sure you don't get a #DIV/0! error:

=IF(G11=0,"",IF(ABS(1-(H11/G11))<0.01,"VALID","INVALID"))

Biff

"Dave F" wrote in message
...
The formula above tests to make sure that H11 and G11 differ by less
than
1%.

And yet the spreadsheet the formula is in was returned to me by my
boss'
boss...saying "this formula makes no sense!" Despite the fact that it
does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

Hi,

Just checking but does this make sense:

=IF(MAX(H11:G11)*99%<=MIN(H11:G11),"valid","invali d")

Regards!
Jean-Guy

"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

Well, it makes sense but I'm not sure that it's any more concise which seems
to have been the original concern.

But thanks for the suggestion.

Dave
--
Brevity is the soul of wit.


"pinmaster" wrote:

Hi,

Just checking but does this make sense:

=IF(MAX(H11:G11)*99%<=MIN(H11:G11),"valid","invali d")

Regards!
Jean-Guy

"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

I assume 1% of G11
but what about it didn't he understand?
the only technical issue would be that if H11 is less than G11, there could
be a case where it could be greater than 1% of H11 but less than 1% of G11
and pass your test.

you don't need the abs(0.01) just 0.01 is sufficient.

If you would gain anything by standing up to him or her do so cause he or
she is wrong, also you might check with your boss to see what her or his boss
wants.
See if your boss understands it.

other wise try something like
=if(And(H11-G11<0.01*G11,G11-H11<0.01*H11),"valid","invalid")

"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

This is another of those cases where someone asks someone else to do
something, and then wants to direct how it gets done. Seems the task has
been accomplished, and if it works, it works.

Kind of like someone asking you to design an incandescent light, and when
you do, they tell you it's too complicated to use because they don't
understand electricity.

If your boss reads this and doesn't like it, tell him to feel incredibly
free to fire me without notice. But he may need to keep you around the tell
him how electricity works for a while longer yet...

"Dave F" wrote:

Well, it makes sense but I'm not sure that it's any more concise which seems
to have been the original concern.

But thanks for the suggestion.

Dave
--
Brevity is the soul of wit.


"pinmaster" wrote:

Hi,

Just checking but does this make sense:

=IF(MAX(H11:G11)*99%<=MIN(H11:G11),"valid","invali d")

Regards!
Jean-Guy

"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

Ha.
--
Brevity is the soul of wit.


"JLatham" wrote:

This is another of those cases where someone asks someone else to do
something, and then wants to direct how it gets done. Seems the task has
been accomplished, and if it works, it works.

Kind of like someone asking you to design an incandescent light, and when
you do, they tell you it's too complicated to use because they don't
understand electricity.

If your boss reads this and doesn't like it, tell him to feel incredibly
free to fire me without notice. But he may need to keep you around the tell
him how electricity works for a while longer yet...

"Dave F" wrote:

Well, it makes sense but I'm not sure that it's any more concise which seems
to have been the original concern.

But thanks for the suggestion.

Dave
--
Brevity is the soul of wit.


"pinmaster" wrote:

Hi,

Just checking but does this make sense:

=IF(MAX(H11:G11)*99%<=MIN(H11:G11),"valid","invali d")

Regards!
Jean-Guy

"Dave F" wrote:

The formula above tests to make sure that H11 and G11 differ by less than 1%.

And yet the spreadsheet the formula is in was returned to me by my boss'
boss...saying "this formula makes no sense!" Despite the fact that it does.

So....is there a cleaner way of doing the same calculation?

Dave
--
Brevity is the soul of wit.

No, still wrong. The formula you've given here has mismatched parentheses.
Perhaps =IF(ABS(1-(H11/G11))<ABS(0.01),"VALID","INVALID")
or [removing the unnecessary ABS]
=IF(ABS(1-(H11/G11))<0.01,"VALID","INVALID")
--
David Biddulph

"Dave F" wrote in message
...
Sorry I copied it incorrectly below. Should be
=IF(ABS(1-(H11/G11))<ABS(0.01)),"VALID","INVALID")

Dave