Complicated Index Match Offset function

"Bob" wrote...
....
>How do I change the function to get the closest value either higher or
>lower than the exact number? At the most I am missing exact values by
>.01. Can OFFSET be added? If so how?
....

It requires entering the whole formula as an array formula. The MATCH call
would change to something like

MATCH(MIN(ABS(List-TargetValue)),ABS(List-TargetValue),0)

Thanks Harlan!

How do I tell the function to look in the first or second column?

Bob

"Bob" wrote...
>How do I tell the function to look in the first or second column?

Your original formula was

=IF(ISERROR(INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1)),
"",INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1))

Change it to the array formula

=INDEX($R$9:$S$3094,MATCH(MIN(ABS($S$9:$S$3094-AV44)),
ABS($S$9:$S$3094-AV44),0),1)

Bob
I know you have a table set up to use but this sounds like a simple=20
conversion like =B0C to =B0F or back
with a possible requirement to round either up, down or to the nearest=20
integer.

using the rules from your post
>I have a worksheet that interprets values from a #255-#0 number system
>and need to convert them to a 0%-100% number system and visa versa.
>#255 =3D 0% . #250 =3D 1% and so on.

if the number to convert is in A1
To convert from #255-#0
=3D100-(100/255)*A1
or =3D1-(1/255)*A1 if you are using %formats

To convert from 0%-100%
=3D255-(255/100)*A1
or =3D255-255*A1 if you are using %formats

If you need to change the underlying returned value to an integer use one=20
of the Excel functions
eg =3DROUND(100-(100/255)*A1,0)
other options include
ROUNDUP
ROUNDDOWN
CEILING and FLOOR

have a look at the MS help and post back if you need guidence

hth RES

Thank you very much Robert!
This works perfectly!

Bob
glad it is solved and thanks for the feedback

RES

as a postscript to this it is a good indication of how a vague question
lead to people down different avenues
Harlan actually did what you asked and sorted out your formula. As he has
done for me on a number of occasions.
I ignored your request and took a guess at the underlying problem. This
time I got lucky.

Sorry if this in any way feels like a lecture because in reality it was a
much better post than many out there

The more people who read advice such as the link below the better these
groups will be.
http://cpearson.com/excel/newposte.htm

<no response needed - I have said my piece> <vbg>

Bob
I know you have a table set up to use but this sounds like a simple=20
conversion like =B0C to =B0F or back
with a possible requirement to round either up, down or to the nearest=20
integer.

using the rules from your post
>I have a worksheet that interprets values from a #255-#0 number system
>and need to convert them to a 0%-100% number system and visa versa.
>#255 =3D 0% . #250 =3D 1% and so on.

if the number to convert is in A1
To convert from #255-#0
=3D100-(100/255)*A1
or =3D1-(1/255)*A1 if you are using %formats

To convert from 0%-100%
=3D255-(255/100)*A1
or =3D255-255*A1 if you are using %formats

If you need to change the underlying returned value to an integer use one=20
of the Excel functions
eg =3DROUND(100-(100/255)*A1,0)
other options include
ROUNDUP
ROUNDDOWN
CEILING and FLOOR

have a look at the MS help and post back if you need guidence

hth RES

Thank you very much Robert!
This works perfectly!

Bob
glad it is solved and thanks for the feedback

RES

as a postscript to this it is a good indication of how a vague question
lead to people down different avenues
Harlan actually did what you asked and sorted out your formula. As he has
done for me on a number of occasions.
I ignored your request and took a guess at the underlying problem. This
time I got lucky.

Sorry if this in any way feels like a lecture because in reality it was a
much better post than many out there

The more people who read advice such as the link below the better these
groups will be.
http://cpearson.com/excel/newposte.htm

<no response needed - I have said my piece> <vbg>

Hi All

I have a worksheet that interprets values from a #255-#0 number system
and need to convert them to a 0%-100% number system and visa versa.
#255 = 0% . #250 = 1% and so on.

So I have made 2 columns of numbers, one for each number system so each
value from one number system lines up (closely) with it's corresponding
value from the other. Column R is 0-100% Column S is 255- 0.

I am using this formula now:
=IF(ISERROR(INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1)),"
",INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1))

The problem I am having is that the 255 - 0 column may not have the
exact value I am looking for or visa versa, and the function returns a
blank cell.

How do I change the function to get the closest value either higher or
lower than the exact number? At the most I am missing exact values by
..01. Can OFFSET be added? If so how?

Thanks in advance!

Bob

"Bob" wrote...
....
>How do I change the function to get the closest value either higher or
>lower than the exact number? At the most I am missing exact values by
>.01. Can OFFSET be added? If so how?
....

It requires entering the whole formula as an array formula. The MATCH call
would change to something like

MATCH(MIN(ABS(List-TargetValue)),ABS(List-TargetValue),0)

Thanks Harlan!

How do I tell the function to look in the first or second column?

Bob

"Bob" wrote...
>How do I tell the function to look in the first or second column?

Your original formula was

=IF(ISERROR(INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1)),
"",INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1))

Change it to the array formula

=INDEX($R$9:$S$3094,MATCH(MIN(ABS($S$9:$S$3094-AV44)),
ABS($S$9:$S$3094-AV44),0),1)

Bob
I know you have a table set up to use but this sounds like a simple=20
conversion like =B0C to =B0F or back
with a possible requirement to round either up, down or to the nearest=20
integer.

using the rules from your post
>I have a worksheet that interprets values from a #255-#0 number system
>and need to convert them to a 0%-100% number system and visa versa.
>#255 =3D 0% . #250 =3D 1% and so on.

if the number to convert is in A1
To convert from #255-#0
=3D100-(100/255)*A1
or =3D1-(1/255)*A1 if you are using %formats

To convert from 0%-100%
=3D255-(255/100)*A1
or =3D255-255*A1 if you are using %formats

If you need to change the underlying returned value to an integer use one=20
of the Excel functions
eg =3DROUND(100-(100/255)*A1,0)
other options include
ROUNDUP
ROUNDDOWN
CEILING and FLOOR

have a look at the MS help and post back if you need guidence

hth RES

Thank you very much Robert!
This works perfectly!

Bob
glad it is solved and thanks for the feedback

RES

as a postscript to this it is a good indication of how a vague question
lead to people down different avenues
Harlan actually did what you asked and sorted out your formula. As he has
done for me on a number of occasions.
I ignored your request and took a guess at the underlying problem. This
time I got lucky.

Sorry if this in any way feels like a lecture because in reality it was a
much better post than many out there

The more people who read advice such as the link below the better these
groups will be.
http://cpearson.com/excel/newposte.htm

<no response needed - I have said my piece> <vbg>