approximate value and not the exact value

[marreco]

Hi.
I need a function that returns me the approximate value and not the exact value.
example:

Number	value next sought	result
3	33	32
2
6
32
33
45

<tbody>
</tbody>

33 if I type in 'B1' and my mother I'm 33 and I have a number near and less than the number entered in 'B1' will be returned 32

thank you!!

 

[Special-K99]

Try

=INDEX(A2:A7,MATCH(B2,A2:A7,1)-1,1)

though I dont know where your mother comes into it

 

[marreco]

Hi.

number
1
3
6
7
10
13
14
21
45
54
65
87
123

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

when I type, 5 in 'B1' the result of its function is '1 'should be '3'


consider the table below.

 

[T. Valko]

Hi.



when I type, 5 in 'B1' the result of its function is '1 'should be '3'


consider the table below.

Maybe this array formula**:

=MAX(IF(A1:A13 < B1,A1:A13))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Cutter]

Maybe this non-array

=INDEX(A2:A14,MATCH(B1,A2:A14,1)-NOT(ISNA(MATCH(B1,A2:A14,0))),1)

 

[marreco]

Hi.
I do not understand your formula.


could explain to me?
it did not work, because partnerships are not complete!

thank you!!

 

[Special-K99]

Deleted - solution doesnt work

 

[T. Valko]

Hi.
I do not understand your formula.


could explain to me?

It returns the closest value that is less than the lookup value.

it did not work

Worked OK for me using your posted data. Did you array enter the formula?

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

because partnerships are not complete!

I don't know what that means.

 

[marreco]

Hello T. Valko, its formula solved!


the first time I open the topic was disfigured, and I understood something else, having left entraddo and again I saw the formula completely.


it worked thank you!

 

[T. Valko]

Hello T. Valko, its formula solved!


the first time I open the topic was disfigured, and I understood something else, having left entraddo and again I saw the formula completely.


it worked thank you!

Good deal. Thanks for the feedback!