approximate value and not the exact value

marreco

Well-known Member
Joined
Jan 1, 2011
Messages
607
Hi.
I need a function that returns me the approximate value and not the exact value.
example:
Numbervalue next soughtresult
33332
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!!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Try

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

though I dont know where your mother comes into it :)
 

marreco

Well-known Member
Joined
Jan 1, 2011
Messages
607
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Nov 2, 2005
Messages
5

ADVERTISEMENT

Maybe this non-array

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

marreco

Well-known Member
Joined
Jan 1, 2011
Messages
607
Hi.
I do not understand your formula.


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

thank you!!
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350

ADVERTISEMENT

Deleted - solution doesnt work
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Well-known Member
Joined
Jan 1, 2011
Messages
607
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top