simplify this formula??

[Dave F]

=IF(ISBLANK(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))),"",IF(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))>=OFFSET(A8,0,MATCH($H$6,$B$7:$G$7,0)-1),"OK","Check"))

The boss says it's "too complicated"

The boss also doesn't like errors returned, so the boss wants errors
trapped. Hence its being complicated. How do you square a circle?

Dave

 

[JLatham]

The response kind of depends on what position/function the 'boss' is in. If
he's end user, he should only be concerned with functionality.

If he is something like head of development, then I'd ask him for
suggestions on how to simplify it - "Please, sir, enlighten this humble n00b".

I can think of other ways to write it, but none of them actually simplify
it. In fact I can think of a couple of ways to write it that would make it
look even nastier.

His basic choices are:
complicated looking but with clean display and functional output
or
not so complicated looking, but with ugly #N/A and other error displayed

Again, I don't know what the 'bosses' role is, but typically the end
user/client simply prepares a statement of need, a functional specification
if you will, and it is/should be left up to the developer to provide the
mechanics of an implementation that will meet the stated need.

A really simple solution would to remove all of the formulas and recommend
an eyeball check using a printed output combined with manual entry of
OK/Check based on they eyeball check.

Wish I had a better solution for you. The only alternative that comes to
mind might be substituting one of the LOOKUP() functions (although without
seeing the total setup of everything I don't even know if that would be
suitable), but in the end, with error checking it is going to be just as
complicated looking as what you have.

Of course, you could Format | Cells | [Protection] and choose both Locked
and Hidden and then protect the sheet and it will look just like (clean,
simple) magic!

 

[Dave F]

Well to be fair, his words were "can you simplify this formula," not
"simplify this."

I agree with you that, though it can be done differently, it can't be made
less complex. His concern is that auditors won't understand the formula.

Dave
--
Brevity is the soul of wit.

The response kind of depends on what position/function the 'boss' is in. If
he's end user, he should only be concerned with functionality.

If he is something like head of development, then I'd ask him for
suggestions on how to simplify it - "Please, sir, enlighten this humble n00b".

I can think of other ways to write it, but none of them actually simplify
it. In fact I can think of a couple of ways to write it that would make it
look even nastier.

His basic choices are:
complicated looking but with clean display and functional output
or
not so complicated looking, but with ugly #N/A and other error displayed

Again, I don't know what the 'bosses' role is, but typically the end
user/client simply prepares a statement of need, a functional specification
if you will, and it is/should be left up to the developer to provide the
mechanics of an implementation that will meet the stated need.

A really simple solution would to remove all of the formulas and recommend
an eyeball check using a printed output combined with manual entry of
OK/Check based on they eyeball check.

Wish I had a better solution for you. The only alternative that comes to
mind might be substituting one of the LOOKUP() functions (although without
seeing the total setup of everything I don't even know if that would be
suitable), but in the end, with error checking it is going to be just as
complicated looking as what you have.

Of course, you could Format | Cells | [Protection] and choose both Locked
and Hidden and then protect the sheet and it will look just like (clean,
simple) magic!

=IF(ISBLANK(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))),"",IF(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))>=OFFSET(A8,0,MATCH($H$6,$B$7:$G$7,0)-1),"OK","Check"))

The boss says it's "too complicated"

The boss also doesn't like errors returned, so the boss wants errors
trapped. Hence its being complicated. How do you square a circle?

Dave

 

[JLatham]

Perhaps if you add a comment to the cell with that formula - or first one in
a range containing similar formulas, explaining the way it works? Even if it
left auditors scratching their heads, you or another knowledgable Excel user
could quickly refresh your memory on what it is doing and explain it to them
in more detail face to face?

Well to be fair, his words were "can you simplify this formula," not
"simplify this."

I agree with you that, though it can be done differently, it can't be made
less complex. His concern is that auditors won't understand the formula.

Dave
--
Brevity is the soul of wit.

The response kind of depends on what position/function the 'boss' is in. If
he's end user, he should only be concerned with functionality.

If he is something like head of development, then I'd ask him for
suggestions on how to simplify it - "Please, sir, enlighten this humble n00b".

I can think of other ways to write it, but none of them actually simplify
it. In fact I can think of a couple of ways to write it that would make it
look even nastier.

His basic choices are:
complicated looking but with clean display and functional output
or
not so complicated looking, but with ugly #N/A and other error displayed

Again, I don't know what the 'bosses' role is, but typically the end
user/client simply prepares a statement of need, a functional specification
if you will, and it is/should be left up to the developer to provide the
mechanics of an implementation that will meet the stated need.

A really simple solution would to remove all of the formulas and recommend
an eyeball check using a printed output combined with manual entry of
OK/Check based on they eyeball check.

Wish I had a better solution for you. The only alternative that comes to
mind might be substituting one of the LOOKUP() functions (although without
seeing the total setup of everything I don't even know if that would be
suitable), but in the end, with error checking it is going to be just as
complicated looking as what you have.

Of course, you could Format | Cells | [Protection] and choose both Locked
and Hidden and then protect the sheet and it will look just like (clean,
simple) magic!

=IF(ISBLANK(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))),"",IF(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))>=OFFSET(A8,0,MATCH($H$6,$B$7:$G$7,0)-1),"OK","Check"))

The boss says it's "too complicated"

The boss also doesn't like errors returned, so the boss wants errors
trapped. Hence its being complicated. How do you square a circle?

Dave

 

[Dave F]

Yeah I think I'm just going to put a button in the spreadsheet called
"details on the lookup formula" which button will open a PDF document
explaining its logic/function. The problem with inserting a comment is
various end users are supposed to insert comments in the range of cells that
this formula, and variations of it, is being used.

Dave
--
Brevity is the soul of wit.

Perhaps if you add a comment to the cell with that formula - or first one in
a range containing similar formulas, explaining the way it works? Even if it
left auditors scratching their heads, you or another knowledgable Excel user
could quickly refresh your memory on what it is doing and explain it to them
in more detail face to face?

Well to be fair, his words were "can you simplify this formula," not
"simplify this."

I agree with you that, though it can be done differently, it can't be made
less complex. His concern is that auditors won't understand the formula.

Dave
--
Brevity is the soul of wit.

The response kind of depends on what position/function the 'boss' is in. If
he's end user, he should only be concerned with functionality.

If he is something like head of development, then I'd ask him for
suggestions on how to simplify it - "Please, sir, enlighten this humble n00b".

I can think of other ways to write it, but none of them actually simplify
it. In fact I can think of a couple of ways to write it that would make it
look even nastier.

His basic choices are:
complicated looking but with clean display and functional output
or
not so complicated looking, but with ugly #N/A and other error displayed

Again, I don't know what the 'bosses' role is, but typically the end
user/client simply prepares a statement of need, a functional specification
if you will, and it is/should be left up to the developer to provide the
mechanics of an implementation that will meet the stated need.

A really simple solution would to remove all of the formulas and recommend
an eyeball check using a printed output combined with manual entry of
OK/Check based on they eyeball check.

Wish I had a better solution for you. The only alternative that comes to
mind might be substituting one of the LOOKUP() functions (although without
seeing the total setup of everything I don't even know if that would be
suitable), but in the end, with error checking it is going to be just as
complicated looking as what you have.

Of course, you could Format | Cells | [Protection] and choose both Locked
and Hidden and then protect the sheet and it will look just like (clean,
simple) magic!

:

=IF(ISBLANK(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))),"",IF(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))>=OFFSET(A8,0,MATCH($H$6,$B$7:$G$7,0)-1),"OK","Check"))

The boss says it's "too complicated"

The boss also doesn't like errors returned, so the boss wants errors
trapped. Hence its being complicated. How do you square a circle?

Dave

 

[RagDyeR]

To replicate what your formula is returning *might not* be the issue.

This is 13 characters shorter, but definitely *not really* more concise:

=IF(ISNA(MATCH($H$6,B$7:$G$7,0)),"",IF(INDEX(B7:G8,2,MATCH($H$6,B$7:$G$7,0))

=INDEX(B7:G8,2,MATCH($H$6,B$7:$G$7,0)-1),"OK","CHECK"))

These formulas are just checking if one cell is smaller then another.

The "WHY" and "HOW" of the *concept* only you can comprehend, knowing the
ultimate "bottom line" of the actual target of the formula.

Are there other ways to achieve the same goal?

This is *not* saying that you didn't do this the *only* way possible.

Without knowing the WHOLE picture, no one can really help with this type of
project.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Yeah I think I'm just going to put a button in the spreadsheet called
"details on the lookup formula" which button will open a PDF document
explaining its logic/function. The problem with inserting a comment is
various end users are supposed to insert comments in the range of cells that
this formula, and variations of it, is being used.

Dave
--
Brevity is the soul of wit.

Perhaps if you add a comment to the cell with that formula - or first one in
a range containing similar formulas, explaining the way it works? Even if it
left auditors scratching their heads, you or another knowledgable Excel user
could quickly refresh your memory on what it is doing and explain it to them
in more detail face to face?

Well to be fair, his words were "can you simplify this formula," not
"simplify this."

I agree with you that, though it can be done differently, it can't be made
less complex. His concern is that auditors won't understand the formula.

Dave
--
Brevity is the soul of wit.

The response kind of depends on what position/function the 'boss' is in. If
he's end user, he should only be concerned with functionality.

If he is something like head of development, then I'd ask him for
suggestions on how to simplify it - "Please, sir, enlighten this humble n00b".

I can think of other ways to write it, but none of them actually simplify
it. In fact I can think of a couple of ways to write it that would make it
look even nastier.

His basic choices are:
complicated looking but with clean display and functional output
or
not so complicated looking, but with ugly #N/A and other error displayed

Again, I don't know what the 'bosses' role is, but typically the end
user/client simply prepares a statement of need, a functional specification
if you will, and it is/should be left up to the developer to provide the
mechanics of an implementation that will meet the stated need.

A really simple solution would to remove all of the formulas and recommend
an eyeball check using a printed output combined with manual entry of
OK/Check based on they eyeball check.

Wish I had a better solution for you. The only alternative that comes to
mind might be substituting one of the LOOKUP() functions (although without
seeing the total setup of everything I don't even know if that would be
suitable), but in the end, with error checking it is going to be just as
complicated looking as what you have.

Of course, you could Format | Cells | [Protection] and choose both Locked
and Hidden and then protect the sheet and it will look just like (clean,
simple) magic!

:

=IF(ISBLANK(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))),"",IF(OFFSET(A8,0,MATCH($H$
6,B$7:$G$7,0))>=OFFSET(A8,0,MATCH($H$6,$B$7:$G$7,0)-1),"OK","Check"))

 

[CLR]

Hi Dave.........
You might consdier "annotating" the formula to refer to an explanation
sheet, or maybe use a CommentBox......

hth
Vaya con Dios,
Chuck, CABGx3

Well to be fair, his words were "can you simplify this formula," not
"simplify this."

I agree with you that, though it can be done differently, it can't be made
less complex. His concern is that auditors won't understand the formula.

Dave
--
Brevity is the soul of wit.

The response kind of depends on what position/function the 'boss' is in. If
he's end user, he should only be concerned with functionality.

If he is something like head of development, then I'd ask him for
suggestions on how to simplify it - "Please, sir, enlighten this humble n00b".

I can think of other ways to write it, but none of them actually simplify
it. In fact I can think of a couple of ways to write it that would make it
look even nastier.

His basic choices are:
complicated looking but with clean display and functional output
or
not so complicated looking, but with ugly #N/A and other error displayed

Again, I don't know what the 'bosses' role is, but typically the end
user/client simply prepares a statement of need, a functional specification
if you will, and it is/should be left up to the developer to provide the
mechanics of an implementation that will meet the stated need.

A really simple solution would to remove all of the formulas and recommend
an eyeball check using a printed output combined with manual entry of
OK/Check based on they eyeball check.

Wish I had a better solution for you. The only alternative that comes to
mind might be substituting one of the LOOKUP() functions (although without
seeing the total setup of everything I don't even know if that would be
suitable), but in the end, with error checking it is going to be just as
complicated looking as what you have.

Of course, you could Format | Cells | [Protection] and choose both Locked
and Hidden and then protect the sheet and it will look just like (clean,
simple) magic!

=IF(ISBLANK(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))),"",IF(OFFSET(A8,0,MATCH($H$6,B$7:$G$7,0))>=OFFSET(A8,0,MATCH($H$6,$B$7:$G$7,0)-1),"OK","Check"))

The boss says it's "too complicated"

The boss also doesn't like errors returned, so the boss wants errors
trapped. Hence its being complicated. How do you square a circle?

Dave